Page 1 of 1

Recreating Department P&L Report in Excel via ODBC

Posted: 12 May 2022, 13:50
by arsenalrule
Hi all

Sage Version 28.0.226.0, report designer version 5.0.251.0
Windows 10 Pro 64 bit

I have connected excel to Sage via the ODBC and Micrsoft query but I am having trouble finding the right fields to get the data I am looking for.
The figure I am looking for is the "Product Sales" from the DEPPNL report.

I would then like to filter this by the department and date so I can get weekly figures for each deparment.
We do not use any of the other fields from this report.

Does anyone know which tables I will need to query?

Thanks
Steve

Re: Recreating Department P&L Report in Excel via ODBC

Posted: 12 May 2022, 15:29
by brucedenney
The department data only exists at the split level in the audit trail, so you need to take the whole of rhe audit split i to excel.

I would then make a hash of the department and nominal code then you can use a sumif to addup the values for a nominal code for the department nominal code.

Product Sales may be several nomial codes added together

Re: Recreating Department P&L Report in Excel via ODBC

Posted: 12 May 2022, 16:03
by arsenalrule
Thank you Bruce

What do you mean by "make a hash of the department and nominal code"? I have had a google, do you basically mean get a unique list of the department numbers and nominal codes?

I've pulled the audit split and it is looking promising. I've made a pivot table by department and nominal, and i think I know which nominals need to be included, so it is looking good! thank you.

Re: Recreating Department P&L Report in Excel via ODBC

Posted: 13 May 2022, 08:38
by arsenalrule
Good morning

I have got a bit further with this, but my sumifs formula is falling down with the nominal codes

Code: Select all

=SUMIFS(Sheet1!$U:$U,Sheet1!$Q:$Q,0,Sheet1!$C:$C,">="&DATE(2022,1,1),Sheet1!$C:$C,"<="&DATE(2022,1,7),Sheet1!$E:$E,">"&4000,Sheet1!$E:$E,"<"&4099)
this just displays £-

if i remove the nominal part:

Code: Select all

=SUMIFS(Sheet1!$U:$U,Sheet1!$Q:$Q,0,Sheet1!$C:$C,">="&DATE(2022,1,1),Sheet1!$C:$C,"<="&DATE(2022,1,7))
then it works correctly and shows the total of those in Department 0 (column Q) within the dates I need.

If I have

Code: Select all

Sheet1!$E:$E,"="&4000
then it works, but adding more than 1 nominal code makes the formula fail.

The nonimal codes are currently formatted as "general". I have changed to "number", but this makes no difference.

I have tried adding multiple

Code: Select all

Sheet1!$E:$E,"="&4000,Sheet1!$E:$E,"="&4001,Sheet1!$E:$E,"="&4002,etc.
but this does not work. I assume it is becasue sumifs is an "and" function, so there is no data that is in both 4000 and 4001 and 4002 etc?

Any help appreciated!

Thanks

Re: Recreating Department P&L Report in Excel via ODBC

Posted: 13 May 2022, 10:33
by brucedenney
It is hard to see what you are doing from the formula alone.

Attached is a sample I have made for you.

There are 3 main formulas on 3 sheets, I have colour coded them yellow, orange and blue.

The yellow formula is the hash, this allows us to add up the audit trail based on department and nominal code.

The orange formula makes the corresponding hash and adds up all the values in that raw data for the hash giving us a table of values for each nominal for each department.

The Blue formulas adds up the the orange formulas to give Sales,COS etc These are slightly different for each heading

I hope this helps.
Sample Dept calcs.xlsx

Re: Recreating Department P&L Report in Excel via ODBC

Posted: 13 May 2022, 10:53
by Onion RS
Hi Steve,

Could it be a text v number thing? As I understand it the Sage nominal code field is of type VARCHAR.

Re: Recreating Department P&L Report in Excel via ODBC

Posted: 13 May 2022, 11:15
by Onion RS
Hi Steve,

Checked and I think this should work.

Code: Select all

Sheet1!$E:$E,"=40??"
Regards,

Ian

PS Whilst this happens to work for your specific circumstance I'd be inclined to convert the code to a number, either in your query or in your worksheet, and then base the SUMIFS on the numeric version of the code. Your previous > and < criteria would probably be OK then.

Re: Recreating Department P&L Report in Excel via ODBC

Posted: 13 May 2022, 11:26
by arsenalrule
Amazing, thank you Onion, that has worked perfectly!

Bruce, thank you for the example sheet, that is not something i have ever done before and will definitely come in useful in the future, thank you.

Re: Recreating Department P&L Report in Excel via ODBC

Posted: 13 May 2022, 11:35
by arsenalrule
Onion RS wrote: 13 May 2022, 11:15 PS Whilst this happens to work for your specific circumstance I'd be inclined to convert the code to a number, either in your query or in your worksheet, and then base the SUMIFS on the numeric version of the code. Your previous > and < criteria would probably be OK then.
I've just seen this, and you are correct, it is the formatting. Changing the column to number format does not seem to chaneg them, and they remain left aligned.
I have used a helper column of =Value() and this has converted them to numbers.
My original formula is now working.

Thank you!

Re: Recreating Department P&L Report in Excel via ODBC

Posted: 13 May 2022, 11:42
by Onion RS
You're welcome.