Report running too slow

[JaneKOS]

I'm trying to write a report to show the available capacity of a component over the next 26 weeks.

There are 26 columns and in each one I determine the PO demand (Po) in the detail of the report then sum in the footer. There is no grouping in the report and only 2 linked tables. The following are then also calculated in the footer:

I determine the available weekly capacity (WklyCap)from a parameter.
The requirement each week (Req)is then Sum ({@Po25})+{@Bal26}. ie the sum of the PO demand plus any residual balances from the previous weeks.
The amount actually made that week is the capacity available or the amount required (Cap):
if {@Req25}>0 then
(if {@Req25}>{@WklyCap} then
{@WklyCap} else
{@Req25})
else 0

The balance remaining (Bal) is then the required qty minus capacity used
{@Req25}-{@Cap25}

I started with week 26 then worked backwards to current week. I tested after 6 weeks and it was working perfectly so I continued with the formulas for all remaining weeks. When I went to run I ended task after 1 hr!

I appear to have something fundamentally wrong in my report design. Any help much appreciated!

 

[lbass]

What is your record selection formula? If you go to database->show SQL query, do you see all selection criteria being used in the query?

Please show the content of your detail level formulas (and of any nested formulas).

-LB

 

[JaneKOS]

The only record selection I have is:
{PMA_TBL.PMA_PART_ONLY}startswith"19HAN007"

and this is the SQL query that shows:

SELECT "PMA_TBL"."PMA_PART_ONLY", "API_STK_TBL"."ASTK_REFERENCE_1", "API_STK_TBL"."ASTK_QTY_1", "API_STK_TBL"."ASTK_WHEN_1", "API_STK_TBL"."ASTK_WHEN_2"
FROM "KOS_LIVE"."dbo"."PMA_TBL" "PMA_TBL" LEFT OUTER JOIN "KOS_LIVE"."dbo"."API_STK_TBL" "API_STK_TBL" ON "PMA_TBL"."PMA_PART_ONLY"="API_STK_TBL"."ASTK_PART_ONLY"
WHERE "PMA_TBL"."PMA_PART_ONLY" LIKE '19HAN007%'

Formulas are as follows, they are all repeated for 26 weeks in columns and appear on the report in this order:

To calculate the Order qty due each week in the detail section( @Po2):
(if {API_STK_TBL.ASTK_REFERENCE_1}IN ["WO", "Sug MO"] and
{@AckDate}in (({@Wk1}+1)to{@Wk2}) then
{API_STK_TBL.ASTK_QTY_1}) else 0

This is then summarised in the report footer (Sum (Po2))

The Demand required for each week is calculated (Req2):
Sum ({@Po2})+{@Bal3}

The Capacity that will be used that week (Cap2):
if {@Req2}>0 then
(if {@Req2}>{@WklyCap} then
{@WklyCap} else
{@Req2})
else 0

The balance remaining that week (Bal2):
{@Req2}-{@Cap2}

I think it's to do with the fact that my Req formula looks to the next week so Crystal is constantly having to run through all the formulas again & again?

 

[Madawc]

I think you might do better with a different design. Group by week. Use a running total for the whole report to get the residual balances from the previous weeks. Use a variable to collect this value in the group header. Have a group total for each week.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

Can't tell what's going on because you didn't show the content of your nested formulas.

-LB

 

[JaneKOS]

Sorry, what do you mean by nested formulas? Apart from some title formulas that work out the week ending Friday date, these are all the formulas in the report?

Thanks,
JG

 

[lbass]

Formulas within formulas, e.g.:

{@Req2}-{@Cap2}

We don't know the content of the formulas. It might be that if we understood more specifically what you are doing that we could suggest alternative approaches. For example, why are you not using an inserted crosstab instead of creating columns manually?

You also might find the following thread useful: thread149-1276307.


-LB