Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

Recreating Department P&L Report in Excel via ODBC

Sage 50 general help forum - Free help and support for all general issues
Post Reply
arsenalrule
User
User
Posts: 5
Joined: 09 May 2022, 15:41
Sage Version: v2018 24 UK/Europe/Africa

Recreating Department P&L Report in Excel via ODBC

Post by arsenalrule » 12 May 2022, 13:50

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

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4466
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

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

Post by brucedenney » 12 May 2022, 15:29

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
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

arsenalrule
User
User
Posts: 5
Joined: 09 May 2022, 15:41
Sage Version: v2018 24 UK/Europe/Africa

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

Post by arsenalrule » 12 May 2022, 16:03

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.

arsenalrule
User
User
Posts: 5
Joined: 09 May 2022, 15:41
Sage Version: v2018 24 UK/Europe/Africa

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

Post by arsenalrule » 13 May 2022, 08:38

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

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4466
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

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

Post by brucedenney » 13 May 2022, 10:33

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
You do not have the required permissions to view the files attached to this post.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

Onion RS
User
User
Posts: 8
Joined: 13 Feb 2020, 10:49
Sage Version: v2018 24 UK/Europe/Africa

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

Post by Onion RS » 13 May 2022, 10:53

Hi Steve,

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

Onion RS
User
User
Posts: 8
Joined: 13 Feb 2020, 10:49
Sage Version: v2018 24 UK/Europe/Africa

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

Post by Onion RS » 13 May 2022, 11:15

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.

arsenalrule
User
User
Posts: 5
Joined: 09 May 2022, 15:41
Sage Version: v2018 24 UK/Europe/Africa

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

Post by arsenalrule » 13 May 2022, 11:26

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.

arsenalrule
User
User
Posts: 5
Joined: 09 May 2022, 15:41
Sage Version: v2018 24 UK/Europe/Africa

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

Post by arsenalrule » 13 May 2022, 11:35

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!

Onion RS
User
User
Posts: 8
Joined: 13 Feb 2020, 10:49
Sage Version: v2018 24 UK/Europe/Africa

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

Post by Onion RS » 13 May 2022, 11:42

You're welcome.

Post Reply

Who is online

Users browsing this forum: No registered users and 3 guests