Improving Crystal Report Performance by Using Parameters in Queries

[Former Member]

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:

1. Parameter that affect Report's printing.
2. 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 1^st stage is to run the query on all records, and only then use the parameters to filter
the results.

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:

ParameterName@Query

for example:

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

shachar