[b]Record Selection Performance Issues [/b]

[Nassy]

Hi,

I am on a client site and have been asked to help speed up some reports. On one particular report the user is asked to enter two string values: Start Period and End Period . These 2 string input values then get converted into numbers: 200401 represents May 2003, 200402 represents 200402. This conversion is done using formulas with lots of IF THEN ELSE blocks and STRING MANIPULATION functions.

The issue

The R selection formula references the formulas used to do the string - number conversion and appear to really slow the report down. If I change the paramaters in the report to directly accept numbers this speeds everything up = but then this isn't user friendly to make the user enter codes representing periods.

Unfortunately I don't really have the luxury to play around much with SQL so need a Crystal reports solution..

The things is, I am not really sure why converting the parameter strings to numbers would slow things down so much as surely the formula would only need to be evaluated once once (?) when the report is run with the input parameter values.

Can anyone think of any ideas to speed this up? I am wondering if a correct useage of 'before readingrecords' would do the trick?

Cheers

Nassy

 

[lbass]

Please show samples of your string field, and also explain what your fiscal year is.

-LB

 

[Madawc]

I'd split the strings into year and month. Start-Year would be Left(@Start_Period, 4) and Start-Month would be Right(@Start_Period, 2)

Then check these against years and months derived from the date. Year({your.date}) and Month({your.date}).

Selections are much quicker if the command is in a form that Crystal can send to the server, rather than your machine sorting a mass of data that the server has sent. Check Database > Show SQL Query for Crystal 10: it helps to say which Crystal.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[vidru]

I think you could use a SQL Expression (called FiscalPeriod) to convert the date to a string that represents it's fiscal period (YYYYMM):

CONVERT(VARCHAR(6), 
  CASE WHEN MONTH("Table"."DateField") BETWEEN 5 AND 12 THEN 
            (YEAR("Table"."DateField") + 1) * 100 + (MONTH("Table"."DateField") - 4)
       ELSE
         YEAR("Table"."DateField") * 100 + 8
  END
)

Your record selection formula would then look like:
{%FiscalPeriod} = {?Parameter}

If I'm wrong in assuming that there's an eight month differential between calendar periods and fiscal periods, please elaborate.

-dave

 

[Nassy]

Thanks for the replies... I found a FAQ on this forum that showed some tips for getting record selections passed back to SQL.. I didn't realise of its existence but there were all sorts of suggestions in there.

I will take the information from this FAQ and also from what you have suggested in your replies on board for next time I encounter performance issues.

Nassy