Improving Crystal Report Performance by Using Parameters in Queries
This document will present you with a methodology that can help you improve Crystal Report’s performance. If you have encountered scenarios in which the
report‘s query takes a long time to run, the methodology in this document might help you reduce the run time.
In Crystal Report there are two types of parameters:
- Parameter that affect Report’s printing.
- Parameters that affect Report’s query.
Parameter that affect Report’s printing:
The first type of parameters is the most commonly used parameter, which we define inside the report. These parameters affect only the report’s
printing. While running the report, the query will return all records and the parameter used to filter the records to display during printing.
These types of parameters have an inherited inefficient, since the 1st stage is to run the query on all records, and only then use the parameters to filter
Parameters that affect Report’s query:
In order to improve report’s Performance, it is preferable to filter the records of the queries outcome within the query. We can do so by using a query that
contains parameters (the second parameter type).
In order to create parameter that affect a report’s query, Please follow these steps:
Open the ”Add Command to Report” window.
Write your query in the box on the left
To add parameters, use the “create” button. a “Command Parameter” screen will open, in which you will setup the parameter.
Parameter Name – Enter the name of the parameter
Propting Text – the text that will be prompt to the user on the input screen
Value Type – select from the drop down list the type of values for this parameter.
Default Value – enter the default value that will appear in the input box
Allow Multiple Values – checking this check box, will convert the input box to a drop down list, enabling the user to select a value from a predefined list.
in order to populate the values in the drop down list in case of multiple values, enter in the “Parameter Name” field the following:
ToDate@ SELECT T0.[DocDueDate] FROM OINV T0
To use the parameter in the query, as the benchmark in the query condition, place the coarser where the parameter needs to be added
double click on the parameter’s name
The Crystal report will add & link the parameter with your query
The last step is to press “OK” and start designing your report.
While running the report, the system will prompt the user with a parameter screen, the user will enter or select the value and the query will return filtered results.
Hope It was helpful