The cube has hundreds of measures going across.
So returning the entire set in one query causes an error.
So the full time developer broke out the measures into chunks.
Basically a single report with 10 SubReports.
And it runs slow, too slow for comfort.
So that's why they brought me in.
To streamline the reports so the customer will approve before going live with the big project.
So I went through my checklist of things to look for:
So I verified the report and sub-reports and it was still running slow.
I then applied the following techniques:
- Right Align Subreports so they fit on the main report page
- In each of the Subreports as well as the main, I condenses white space wherever possible.
- Inserted New Section for each row.
- Then Fit Sections for every section on the report.
- Removed every single "Keep Together" checkbox in report and all subreports.
- Added PageNofM in order to render through all the subreports upfront.
- Changed the report from Portrait to Landscape 11x8.5.
- Deleted all Unused Sections of the report.
- Unchecked "Suppress Blank Sections" for every section.
- Unchecked Select Distinct.
- Unchecked Verify on 1st Refresh.
- Unchecked Verify Stored Procedure on 1st Refresh.
- Unchecked Verify Database Driver upon change.
- Checked the Perform Grouping on Server checkbox.
- Checked the Select Distinct Data for Browsing.
- Checked the Perform QueryAsynchronously checkbox.
- Checked the Use Indexes on Server for Speed.
- Removed Unused Formula fields from sub-reports.
So far report run time has dropped from 8-10 minutes down to 4:27 minutes. Shaved off 53% off the baseline runtime.
Not too shabby, however, there's a ways to go!