How to improve report performance

[jj22171]

I have a set of reports with running time ranging from half a minute to 2 minutes. Another set of reports takes only 15 to 20 seconds and both sets are using the same query (tried query of linked tables and pass-thru query, there's not much difference). The reports have sorting and grouping in 3 different fields. The report values are populated by assigning values to control sources such as below:

prd1Act.ControlSource = "=Sum(IIf([TIME] ='" & gstrPrd & " ',[AMT],0))"
prd1Bgt.ControlSource = "=Sum(IIf([TIME] ='" & gstrPrd & " ',[AMT],0))"
prd1Var.ControlSource = "=[p1Bgt]-[p1Act]"
prd1VarP.ControlSource = "=iif([p1Bgt]<>0,([p1Bgt]-[p1Act])/[p1Bgt],0)"

toDate1Act.ControlSource = "=Sum(IIf([PERIOD]<=" & gintPrd & ",[AMT],0))"
toDate1Bgt.ControlSource = "=Sum(IIf([PERIOD]<=" & gintPrd & ",[AMT],0))"
toDate1Var.ControlSource = "=[b1Bgt]-[b1Act]"
toDate1VarP.ControlSource = "=iif([b1Bgt]<>0,([b1Bgt]-[b1Act])/[b1Bgt],0)"

The 1 in prd1/todate1 indicates the group. Since budget figures are not available at the moment, Bgt picks up the Actual for illustration purposes only.

Question: Is there a more efficient way of coding the report?

Greatly appreciate your thoughts.

 

[dhookom]

Are you using Page of Pages on your report? Are sections set to allow growing or keep together?

Why are you setting control sources at run time?

Duane MS Access MVP

 

[jj22171]

Hi dhookom,
Are you using Page of Pages on your report?
-Yes, but tried deleting it with not much of improvement.

Are sections set to allow growing or keep together?
- Can Grow/Shrink set to NO
- Keep together set to Yes

Why are you setting control sources at run time?
- users have the option to select periods and reports have to display the Current Period amount & To Date Balances.

I tried sum/group query and dumped the result into a local table and use it as recordsource for reports. The report runtime itself improved considerably. But the performance issue shifted to the creation of local table though it takes only 30 to 40 seconds which is already an improvement. As long as the user don't change the query selection criteria often (or for each report), it should be okay.

Is there a better alternative? Thanks.

 

[dhookom]

Why does

users have the option to select periods and reports have to display the Current Period amount & To Date Balances.

result in changing control sources? Changing date ranges should change the records displayed and not the control sources (unless I am missing something).

Duane MS Access MVP