Trying to design a Customer Sales Report that contains Month/Current Year and same Month/Prior Year.
Platform: Sage 100
Table: AR_InvoiceHistoryHeader
Parameter field: InvoiceDate
Trying to present a report that shows invoices for June 2018 and June 2017.
Any suggestions on how to set the parameters to pull month - year and month - prior year?
Tags:
Hi Dan,
This can be achieved by use of a "pass through variable" in your container. You'll can use this variable to filter data in the report to a specified date range. You can find more information on how to set this up at the below link:
https://www.youtube.com/watch?v=03AdLdHRV8Y&t=936s
You can also find some helpful videos on our Sage 100 Intelligence Reporting YouTube channel: https://www.youtube.com/channel/UCLRKG4vc3IGc9zRHtI5zsUg
Hope this helps!
Kind regards,
Keeveshin
Thank you for the response... Greatly appreciated!
I will check out the YouTube links.
Are you looking to get Summarized Sales Totals or Detail (i.e. Invoice) Totals
Detail -- Invoice Totals
I have attached a snapshot
Dan - Attached is an example on how to get the Current Pd/Year and the Prior Pd/Year.
It's a Union report that has 2 sub reports. One for Current Pd/Year and one for the Prior Pd/Year.
There are 2 Pass-Thru Variables setup in each for Invoice Start Date & Invoice End Date.
The Pass Thru Variables are used in each container to filter the appropriate dates.
In the "Current" container the filter is set as follows:
WHERE AR_InvoiceHistoryHeader.InvoiceDate >= @StartDate@ AND AR_InvoiceHistoryHeader.InvoiceDate <= @EndDate@
In the Prior Container the filter is set as follows:
WHERE AR_InvoiceHistoryHeader.InvoiceDate >= (@StartDate@ -365) AND AR_InvoiceHistoryHeader.InvoiceDate <= (@EndDate@ -365)
The Current Data is outputted to Shee1 in the template and the Prior Data is outputted to Sheet3.
You can setup the Pivot Tables to link to the separate sheets.
Let me know if you have any questions
Forgot to point out the report is filtered by Customer.
Thanks, Doug!
I really appreciate the response.
Long time no talk. Hope all is well with you and yours.
© 2019 Created by Sage Alchemex.
Powered by