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?

Views: 98

Reply to This

Replies to This Discussion

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

Attachments:

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

Attachments:

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.

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2018   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service