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