I need to create various forecasting reports that will summarize dollar or quantity ordered by Part Number or Customer/Part Number, displaying these summarized values into columns that define a fiscal quarter or period. (These reports will ultimately be downloaded to Excel by users so they can further manipulate and share.)
To start, I want to create a simple report that can summarize quantity quoted by part number for the previous 4 quarters. I can create a sub summary report to show the data (minus the 4 quarters). Now, I'm trying to figure out the best way to further break down that quantity ordered by date ranges into columns. For example, these would be sample column headings:
|Part Number||FY13Q4||FY14Q1||FY14Q2||FY14Q3||FY14Q4||FY15Q1||FY15Q2||FY15Q3||FY15Q4||FY16Q1||FY16Q2||FY16Q3||Grand Total|
I created global calculation fields to calculate all the fiscal start/end dates according to their calendar, based upon the current date. Can someone help me figure out the best way to approach this problem? I thought about having several scripts that find the data based upon dates and export/import into temp excel files/tables. And then when the report runs, it pulls the values from these temp tables. But, that seems pretty complex and may be a performance drain. Any ideas you have I would greatly appreciate! I read about cross-tab reports but not sure if that is relevant here, and I have never done one.