AnsweredAssumed Answered

Creating a summary report with Fiscal Period Columns

Question asked by kfure on May 17, 2016
Latest reply on May 31, 2016 by kfure

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 NumberFY13Q4FY14Q1FY14Q2FY14Q3FY14Q4FY15Q1FY15Q2FY15Q3FY15Q4FY16Q1FY16Q2FY16Q3Grand 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.

 

Thanks again,

Outcomes