CrystalMartinC
MIS
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.
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.