SLOW report @Design Time Maybe to many formulas ? 1

[Relloc]

I though I have written the ultimate two-dimensional report only to drive into a brick wall with Crystal 8.0

My report contains more than 300 formulas
I do not use whileprintingrecords
64 of formulas are switch formulas (Easy way of incorporating fiscal periods)



The whole Idea is to vertically group on Month, Quarter, Biyearly or Yearly, calculated etc

Problem the report is to slow at design time takes 3 minutes + just to enter formula explorer and takes 5 minutes to execute (of the execution time 4.5 minutes are taken up by the evaluation of the formulas)

The question

Will it speed up my report if I use an alternative to Switch ex. CASE, IFF or Simple IF ELSE

 

[kenhamady]

I was surprised to find this note in the Seagate help, which might indicate that you are correct:

"Every argument of the Switch function is evaluated before the result is returned. Thus, you should watch out for undesirable side effects when using Switch. For example, if one of the values results in division by zero, an error will occur, even if that value is not the value that is returned by the Switch function."

With If-Then it will only process till it finds a match. That might make things faster, especially if you put the most frequently used cases toward the front of the if-Then.

I am also surprised that it takes 300 formulas to give (primarily) date range grouping options.
Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[Relloc]

Thanks the 300 Formulas = Analysis Reports

 

[Relloc]

OK

I have converted all Switch formulas in my report to Select Case but still get a report that takes 5 - 10 minutes just to evaluate one formula (Extremely long at creation of new formulas if I edit an existing formula it is OK). When the crystal evaluation of formulas and settings is done the actual data flow is normal

Will it speed up things if I make use of UFL’s for the formulas in the background?

If I could have made use of variables in Crystal query on ACCESS ODBC I could have perform most of these functions via SQL is it possiple ?

Attached an example of a detail line and one of the formulas that is used in it


DETAIL LINE
if {@nPeriod}>= 5 and {@PYDATA} = false
then
{@p1}+{@p2}+{@p3}+{@p4}+{@p5}
else
if {@nPeriod}>= 6 and {@YEAR}- {@InitFlag6} = {oehist50.Year} and {@PYDATA} = false
and {@CALCULATION} <> TRUE then
{@L1Start6M}
else
if {@nPeriod}>= 3 and {@YEAR}- {@InitFlag3} = {oehist50.Year} and {@NYears} = 1 and
{@PYDATA} = TRue
and {@CALCULATION} <> TRUE then
{@L1Start3M}
else
if {@nPeriod}>= 2 AND {@YEAR}- {@InitFlag2} = {oehist50.Year} and {@NYears} = 2
and {@PYDATA} = TRue and {@CALCULATION} <> TRUE then
{@L1Start2M}
ELSE
IF {@nPeriod}>= 2 and {@NYears} = 1 AND {@CALCULATION} = TRUE THEN
{@CALC2}

Formula to determine witch column or columns to use in database

Switch ({@InitStart6} = 1,{@LINE11},{@InitStart6} = 2,{@LINE12},
{@InitStart6} = 3,{@LINE13},{@InitStart6} = 4,{@LINE14},{@InitStart6} = 5,{@LINE15},{@InitStart6} = 6,
{@LINE16} ,{@InitStart6} = 7,{@LINE17},{@InitStart6} = 8,{@LINE18},
{@InitStart6} = 9,{@LINE19},{@InitStart6} = 10,{@LINE110},{@InitStart6} = 11,
{@LINE111},{@InitStart6} = 12,{@LINE112})

 

[kenhamady]

I am not sure of the answer to either question. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.