I have a report based on a single table with 40,000+ records using the following complicated filter in the report. It takes 63 seconds to preview.
I'm thinking Lightning Quick Optimization (LQO) may help. But I can't get it working. And I don't really understand LQO since it does not filter records. Help for LQO requires the following:
"Use at least one of the same fields used in the query.
If you use multiple fields with different data types, the first field used must be type character,
The data type of the data in the index key must match the data type of the field you are searching.
Use an order expression that contains either a single field, or two fields concatenated using the plus operator (+), such as in the expression Lname+Fname.
Not use the Unique Only option
Not use a filter expression"
Is LQO for searching records ONLY, or will it help in filtering records?
I tried using an index of: Location+shift+cdate(Bgn_date) with no luck. I then tried adding a calculated field to the table and using it in the index expression without success, improved speed of report.
I'm printing the report from a form based on the same table as the report. I've tried off-loading part of the report filter to the form, but without success as it delays opening the form and the purpose of the form is to capture the variables.
What would you suggest?
Thanks,
~ Tom
Code:
Location = Var->V_Location .and. (*word(var->v_shift,shift)).and. (Bgn_Date<=Var->V_BeginDate.and.(End_Date={}.or.End_Date>Var->V_Begindate .or. SERVICE_DATE=var->v_BeginDate)) .and. (*word(Dow,left(cdow(Var->V_Begindate),2)) .or. Service_Date=Var->V_Begindate .OR. (DateTest(MONTH(Var->V_Begindate),Dayofwk,YEAR( Var->V_Begindate),Wkofmo)=Var->V_Begindate .or. RECURDATE (Bgn_Date,Recur,var->v_begindate)- Recur = var->V_Begindate .or. RECURDATE (Bgn_Date,Recur,var->v_begindate) = var->V_Begindate))
"Use at least one of the same fields used in the query.
If you use multiple fields with different data types, the first field used must be type character,
The data type of the data in the index key must match the data type of the field you are searching.
Use an order expression that contains either a single field, or two fields concatenated using the plus operator (+), such as in the expression Lname+Fname.
Not use the Unique Only option
Not use a filter expression"
Is LQO for searching records ONLY, or will it help in filtering records?
I tried using an index of: Location+shift+cdate(Bgn_date) with no luck. I then tried adding a calculated field to the table and using it in the index expression without success, improved speed of report.
I'm printing the report from a form based on the same table as the report. I've tried off-loading part of the report filter to the form, but without success as it delays opening the form and the purpose of the form is to capture the variables.
What would you suggest?
Thanks,
~ Tom
Comment