SageForum for UK versions of Sage 50- Help and support.

Hello All!

I have spent all day trying to extract some data from Sage - Current and historical back to July 2017 by month.

Long story short all I am after is historical data by Stock Category T/O and Profit excluding certain customers. I managed to do this using invoices. So spent the afternoon running the reports by months. Took ages as Sage is slow working each one out.

I got to July 2016 and a little alarm rang, it should have rang earlier but all the margins were really similar once I got to over 3 months and past a price update. I then discovered that the invoicing is not Frozen in time? Sales price is but cost is todays last cost price even looking at an invoice from 2016. It really surprised me? I really thought that once updated all the data from that should be literally frozen. Even running a report in a month could get corrupted if you have had a lump of popular stock arrive and it has gone up or down dramatically.

Anyway I feel sure product profit run at the end of the month would be more accurate, or at least closer as it looks like it uses average cost price. I cannot see away to exclude certain Account Ref?

Is anyone else surprised invoiced profit is not frozen at point of clicking update? You would think it was relatively simple exercise.

Hi Andrew

The issue is that the cost prices are stored in the Stock transaction tables, it sounds like the report or whatever that you used is picking up the last cost price from the stock table.

What you need to do is to work out margins on the products using the info from the stock transaction table.

That is not much use if you want to know by cusotmer so you need to either

Link the invoice to the stock transaction so you can find the cost price for that particular transaction
or
Link the stock transaction to the invoice table to find the customer.

Hope this helps.

Not sure how to that to be honest!!

I have tried editing "Product Period Profit" (By Transaction)as this report is using the cost at the time of the transaction. SAge even go to the trouble of informing you in the description that the historical data will be accurate.

In the editor I go to Report --> Filter and that opens "Expression Editor" By trying to isolate my main customers Nominal Account of 4100 The customers I want to exclude each have their own Nom Sales Number (For P&L Purpose) but because I work on small margins for these they skew just about every other report in Sage! I have searched the Sage website and found it really hard to find any instructions on using the Expressions. So this is what I pieced together and it still didn't work. The only bit I added was in bold. If its a report you could write happy to pay for it!

STOCK_TRAN.TYPE LIKE "GO" OR STOCK_TRAN.TYPE LIKE "GR" OR NOMINAL_LEDGER.ACCOUNT_REF LIKE "4100"

I think you need AND not OR and some brackets

Thanks Bruce - I gave up trying to find instruction online from Sage and called them so in case anyone searches and find this thread. Here is the Sage expression operators To exclude certain things from a report. Guess now I know this can use in other reports. The thing added to the end of the standard Filter is in Bold. So right click on the reort you want to change -- Edit --- Report at the Top --- Filter --- ADD to Expression --- Report --- Properties - Change Name and save as.

STOCK_TRAN.TYPE LIKE "GO" OR STOCK_TRAN.TYPE LIKE "GR" AND NOMINAL_LEDGER.ACCOUNT_REF not in ("4130", "4110","4160")

I was close but no cigar!

If you are reading this Bruce, do you do or could you write anything to import a POP or SOP via Excel just really Product code and quantity? I upgraded to 365 just to get the email about the automatic back up confirming it went through and no data errors and was hoping integration meant a bit more than some basic auto reports in Excel.

STOCK_TRAN.TYPE LIKE "GO" OR STOCK_TRAN.TYPE LIKE "GR" AND NOMINAL_LEDGER.ACCOUNT_REF not in ("4130", "4110","4160")

Looks slightly ambiguous to me. I would add brackets to make it clearer.

(STOCK_TRAN.TYPE LIKE "GO" OR STOCK_TRAN.TYPE LIKE "GR") AND NOMINAL_LEDGER.ACCOUNT_REF not in ("4130", "4110","4160")

For your second question, yes, we can import most sorts of transactions from all sorts of sources. Give me a ring on 03333 445950 and we can chat.

Hi,
Been out all day came back and ran the report it didn't work! HA Good old Sage Support. Will try yours and give you call.

Have you used pivot tables in excel?

If so, I would pull the data from invoicing and stock trans into one big table in excel then do the selections and exclusions using a pivot table to get the bits you want.