Too heavy report

[kruel830]

Starting point: I have estimated consumption of the components for the next 12 months and I know current component balances on hand. Component suppliers tend have have a order size for the each component (but sometimes order size is 0). I need to report estimated order sizes for the next 12 months.

The problem: I have thousands of different components. I have solved the task by creating virtual on hand balances for the each month. It takes more than a minute to open the report (as well editing, sorting etc) as order sizes and balances for each month are calculated dynamically. How I can speed up report generation? I tried to make calculation with data load editor, but I faced "Nested aggregations not allowed" error.

My calculation goes like this:

Virtual on hand balance for the first month (v2):

If ((OnHand - estimated consumption for the month) > OrderPoint,
(OnHand - estimated consumption for the month),
ceil((OrderPoint - (OnHand - sum(estimated consumption for the month)/OrderSize,1)*OrderSize+OnHand - estimated consumption for the month))

Estimated order amount for the first month (OA1):

if(OnHand - estimated consumption for the month) > OrderPoint,
(OnHand - estimated consumption for the month),
Ceil((OrderPoint - (OnHand - estimated consumption for the month)/OrderSize,1)*OrderSize))

Virtual on hand balance for the month+1 (v3):

If (($(v2) - estimated consumption for the month+1) > OrderPoint,
($(v2)- estimated consumption for the month+1),
ceil((OrderPoint - (OnHand - (estimated consumption for the month+1)/OrderSize,1)*OrderSize+$(v2) - estimated consumption for the month))

Estimated order amount for the month+1 (OA2):

if($(v2)- estimated consumption for the month) > OrderPoint,
($(v2) - estimated consumption for the month),
Ceil((OrderPoint - ($(v2)- estimated consumption for the month)/OrderSize,1)*OrderSize))

v4, v5, v6....OA3, OA4, OA5, OA6 etc.