2 Replies Latest reply on May 31, 2016 11:33 AM by kfure

    Creating a summary report with Fiscal Period Columns


      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,

        • 1. Re: Creating a summary report with Fiscal Period Columns

          If your cross-tab report has a known horizontal limit, repeating fields are quite handy.


          You could try to


          • create a calculation field with 9 repetitions, unstored

          • use ExecuteSQL to calculate the respective value per product/quarter, where the product ID comes from the current record, and the quarter is calculated by using Get ( CalculationRepetitionNumber ) + a start date/quarter


          Not sure how you would do the import, but for display this is a very compact solution.

          • 2. Re: Creating a summary report with Fiscal Period Columns

            I created a sample solution that uses a list layout. I have some global calculation fields that determine the fiscal year end date based upon the current date. This first version report needs to display summarized data into 12 or 4 columns for either the previous closed 12 periods or previous closed 4 quarters, summarized by a part number or customer, etc...     In my current design layout, the user makes their selection criteria for the report and presses a button which calls a script. That script summarizes the data into the proper fiscal year/period categories using ExecuteSQL and assigning the values to fields in the table that the layout is based on. In my sample environment the report takes about 30 seconds to run. I'm worried that once we are running it remotely on our peer-to-peer setup, it may take a really long time. Should I be using a different approach than Execute SQL for performance? Would cross-tab reporting help for performace? These reports will only be run monthly so it's ok if they are not lighting fast, but I don't want them to take too long! Any advice would be greatly appreciated. (I did put all Execute SQL into scripts (that are called at report run-time). So there are no calculation fields calling Execute SQL, as I have heard that is a performance drain.) Thanks!!!!