Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals
Recreating Department P&L Report in Excel via ODBC
-
- User
- Posts: 10
- Joined: 09 May 2022, 15:41
- Sage Version: v29 UK/Europe/Africa
Recreating Department P&L Report in Excel via ODBC
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
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
- brucedenney
- Site Admin
- Posts: 4632
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Recreating Department P&L Report in Excel via ODBC
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
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.
-
- User
- Posts: 10
- Joined: 09 May 2022, 15:41
- Sage Version: v29 UK/Europe/Africa
Re: Recreating Department P&L Report in Excel via ODBC
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.
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.
-
- User
- Posts: 10
- Joined: 09 May 2022, 15:41
- Sage Version: v29 UK/Europe/Africa
Re: Recreating Department P&L Report in Excel via ODBC
Good morning
I have got a bit further with this, but my sumifs formula is falling down with the nominal codes
this just displays £-
if i remove the nominal part:
then it works correctly and shows the total of those in Department 0 (column Q) within the dates I need.
If I have 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 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
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)
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))
If I have
Code: Select all
Sheet1!$E:$E,"="&4000
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.
Any help appreciated!
Thanks
- brucedenney
- Site Admin
- Posts: 4632
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Recreating Department P&L Report in Excel via ODBC
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.
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.
Login to access 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.
Re: Recreating Department P&L Report in Excel via ODBC
Hi Steve,
Could it be a text v number thing? As I understand it the Sage nominal code field is of type VARCHAR.
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
Hi Steve,
Checked and I think this should work.
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.
Checked and I think this should work.
Code: Select all
Sheet1!$E:$E,"=40??"
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.
-
- User
- Posts: 10
- Joined: 09 May 2022, 15:41
- Sage Version: v29 UK/Europe/Africa
Re: Recreating Department P&L Report in Excel via ODBC
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.
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.
-
- User
- Posts: 10
- Joined: 09 May 2022, 15:41
- Sage Version: v29 UK/Europe/Africa
Re: Recreating Department P&L Report in Excel via ODBC
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.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 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
You're welcome.
Who is online
Users browsing this forum: No registered users and 1 guest