Arithmetic in selection processing suppresses Where clause

[CrystalMartinC]

HI,

We use Crystal Info ver 6 connection via ODBC.
Background
We use a numeric field which represents our reporting month e.g. YYYYMM. When selecting figures for a month,quarter,year we have to select the figures in the data set one month prior to the start date and the and subtract them from the current month. Therefore 2qtr becomes 200103 to 200106 etc.

Problem
Asking users in a parmeter prompt to enter a date one month prior to the required start date and allow for year change causes confusion, therefore we currently select all records <= to the end date and a formula selects the month entries immediately prior to the required start date(storing required values work fields). Due to the increase in database size this solution is causing run time problems. Is there any way we can carryout arithmatic on the start date parameter within the select records section
e.g. If totext({date1},'######')[5 to 6] = '01'
then {date1} - 89 else {date1}-1
without the system suppressing the 'Where' clause generation and attempting to bring all the database back for local processing.Using a foumula with beforerecordread and using its result within the record selection fails. Some users have resorted to editing the scheduled job's selection parameter inserting monthEnd in [date1,{date2}] etc to improve runtimes, however this leads to problems if the start date entered in date1 and and real start parameter differ causing confusion between report content and headings!

Limitations.
We cannot change the database field types.
Ingress 2 database in use and we cannot get stored procedures to work.
Using a sub-report failed due to size of database and number of repeated calls leading to extend run times.

Thanks for any help

 

[DjangMan]

What about adding a table that contains something like:

[tt]
QTR DATEVALUE
2000 Q1 199912
2000 Q2 200003
2000 Q3 200006
2000 Q4 200009
2001 Q1 200012
[/tt]

and have your parameter browse that table for the valid values? (I don't have Crystal 6 installed on this machine so I can't test to see if C6 allows parameters to browse other tables.)

DjangMan

 

[rhinok]

Instead of trying to create a stored procedure, can't you just create a view that has the field values you want? If so, you could use the view as a table in the report and base the parameters off those fields.