Sage 100

Am working on a request for a Rolling 12 Month Sales History report (MAS 90 4.4). However, it must group by customer # and Ship To Code. However, the periods are fiscal, so I need it to refer back to GL_FiscalYearDetail to get the proper date ranges for the rolling periods. Anyone done one of these and willing to either sell it or share how you joined the GL file to establish the period dates? I've done these in the past, but always based upon calendar months.

Is this an option...If you had the sales by shipto turned on in Sales Order, you could just use the standard Inventory Sales History Report. You would require that they pick the current fiscal year and the prior fiscal year in the selection criteria, so you would have all the data you need.

I just wrote a Rolling 12 month report for a client. I don't think you need to link to GL Fiscal as a true rolling 12 is always from the selected report date back. I used the following formula to test for the month ...here is the current month formula ..if datediff(""m"",{?Current Month},{AR_InvoiceHistoryHeader.InvoiceDate})=0 then {@Sales Amt} else 0. I then wrote 11 formulas to test for the next 11 months.

@BethBowers Good thought, but that report only reports Inventory items, not misc items/charges. Basically, I'm looking for the Customer Sales History by Period report, with Ship To Code breakdown.

I attached my report for you to see the formulas for the 12 rolling months. My report is on Invoice Totals

@TinaRenner Thanks for the sample! Will give it a try. I noticed that discounts are not deducted from Sales Amt (perhaps not applicable for your client?).

For this report, they wanted gross sales.

I've done something similar with the boolean statements. I've found that the report will run faster if you create an initial formula to designate the period: if transaction date in [ ] then ""1"" and so forth. Then you build the totals off of that formula and it seems faster. Also if they ever change how they want it organized, you only have to change one field instead of 12. Just another option.

It appears i need the actual fiscal date ranges (as defined in Gl) for this report. Any help is appreciated.