Using report parameters

Advanced report design

Using report parameters

Parameter features

Report parameters is a powerful feature, here is a list of samples you can do with parameters

Allow the user to change words or sentences of the report.
Set initial values of group sums, etc, place the parameter name in any expression
Assign them to querys to alter the result set (parametrized or linked querys)
Replace some text in the query with a provided text (text substitution)
Print them, or alter them while executing the report, you can use the parameter in any expression
Assign parameters using report client application
Assign parameters using the web report server and any html client
The parameters can contain expressions evaluated before or after opening report datasets.
A search dataset can be defined so the user can search for parameter values inside a dataset.
Set a validation rule for the parameter
Set an error message for the validation rule fail
Set diferent description, hints and error messages for each report language

You can define the parameters, and also preview how the parameters will show to the user.

Parameter definition

Parameter definition window allows to add, modify and delete parameters, you can assign parameters to datasets (parametrized querys). The parameters can be visible or not visible to the user. You can assign a single parameter to multiple datasets.

This is a sample of report parameters:

The data definition properties for this sample, see that parameters are preceded by the : symbol.

The user parameters window is called in preview window when user click the parameters button:

Parameter types

Type	Description
String, Integer, Float, Date, Time, DateTime	Basic types, they contain fixed values
Boolean	False or True values
Expres.Bef.Open	Contains a expression evaluated before any dataset is open, so the result can be assigned to a parametrized query for example
Expres.Aft.Open	Contains a expression evaluated after opening all datasets, so it can contain dataset fields
String Substi.	You use this parameter type assigning it to at least one dataset, you must provide a search string, the engine will search in the sql sentence for that string and will replace it with the value of the parameter. This can be used to pass the entire sql sentence as a parameter or only the WHERE clause for example.
Value list	You provide two list, one for value captions, and other for real values, the engine will show to the user a combo box to select from the caption list, and will assign the corresponding value from list of values. A dataset can be specified to fill the value list, when the parameter window is shown the list is filled with the dataset, first field of the dataset will be descriptions, and second field on the dataset will be real values.
Multi select	It's a merge of String Substi and Value List, the value to be replaced will be a combination of selected values separated by commas. Very useful to use with querys like: WHERE ORDERTYPE IN (:ORDERTYPES) The user will be able to select more than one value, a checklist box will be provided on parameters window. The value list can also be loaded from a dataset, like Value List parameter type. You can obtain information about a parameter, and multiple selection using Expression evaluator function: ParamInfo
String sustitution extended	Same as string sustitition but using multiple selection.
Initial value	The datatype is detected by evaluating an expression, the value of the parameter must be a valid expression, for example, to create a paremeter with initial value of today, you select the initial value and set the value to TODAY.

Special parameters

There are special parameters that can be used to interact with the engine itself, just define and assign values to them:

Name	Purpose
ADOCONNECTIONSTRING	Replaces the connection string with the provided in this parameter
ALIAS_ADOCONNECTIONSTRING	Replaces the connection string in "ALIAS" connection with the provided in this parameter
DBXCONNECTIONS	Path to a user defined dbxconnections file, to alter password, database path at runtime
DBXDRIVERS	Path to a user defined DBXDRIVERS file.
MAIL_DESTINATION	Default destination, on preview window, send mail option
MAIL_SUBJECT	Default subject, on preview window, send mail option
MAIL_BODY	Default body, on preview window, send mail option
MAIL_FILE	Optional, force a filename for the attatched file, you should include file extension
DBPARAM_PARAMNAME	Replaces database connection parameter PARAMNAME with the value in the parameter. Useful to provide user name, password, database path, before executing the report For DBExpress also DriverName, VendorLib,LibraryName,GetDriverFunc For InterbaseExpress also DatabaseName is allowed For Mybase, Database param indicates the directory path
ALIAS_DBPARAM_PARAMNAME	Same as DBPARAM_PARAMNAME but applied only to Connection with alias: "ALIAS"
DATASETNAME_FILTER	Filter for Mybase driver in memory datasets, if a paramter named DATASETNAME_FILTER is found, it will be used to filter the dataset

Validation

You can set a validation expression for a parameter, the validation of parameters is done in the moment of showing the parameters to the user, if the validation is not valid, the error message is shown to the user, this is true also for integrated web server. Note the validation is not done before running the report, to do validations before running the report you can use the checkexpression evaluator function.

Lookup help for parameters

Sometines a parameter is specified as a code number (customer code, family code). You can specify a lookup search so the user can browse a dataset to find the code number.

The simple case is to select a code number from a small dataset. Imagine you have a parameter FAMILY_CODE, and the user must enter the family code as a number. The final result will be a button beside the parameter input, when the user click the button all the available families are shown, the user select it by name and the family code is assigned to the parameter.

Steps to create a lookup dataset to assist the user on parameters window:

Create the parameter FAMILY_CODE as integer. Description family code.
Create a new dataset FAMILY_LOOKUP with the sql sentence to show help information to the user, the first field must be the family code, that is the field that will be assigned to the parameter: SELECT CODE,DESCRIPTION FROM FAMILIES ORDER BY DESCRIPTION. Also uncheck the option Open at start, so the dataset will no be open on report start, but only when needed.
Go to parameter definition and select the new dataset, as the Search Dataset for the parameter FAMILY_CODE.
Test the feature going to user parameters window.

Another case is to select a code from a large dataset where the user should search for, like a customers table.

Steps to create a lookup dataset to assist the user on parameters window, with search feature, usually to loockup on large datasets:

Create the parameter CUSTOMER_CODE as integer. Description family code.
Create a new dataset CUSTOMER_LOOKUP with the sql sentence to show help information to the user, the first field must be the customercode, that is the field that will be assigned to the parameter, but it will also contain one parameter to search for only a subset of the dataset: SELECT CODE,DESCRIPTION FROM CUSTOMERS WHERE UPPER(DESCRIPTION) CONTAINING :CUST_DESCRIPTION ORDER BY DESCRIPTION. Also uncheck the option Open at start, so the dataset will no be open on report start, but only when needed.
You must create a paramerter called CUST_DESCRIPTION as string and assigned to CUSTOMER_LOOKUP dataset, you should set the visible property to false for this parameter.
Go to parameter definition and select the new dataset, as the Search Dataset for the parameter FAMILY_CODE, and assign the CUST_DESCRIPTION parameter as search parameter.
Test the feature going to user parameters window, now the user must write some text and the after a few seconds, the engine will execute the sql sentence assigning the parameter CUST_DESCRIPTION.

About parameters and database drivers

When you place a parameter inside a query:

SELECT MYDATA FROM MYTABLE WHERE MYCODE=:CODE

This is called a parametrized query, the query is send throught the database driver, and translated by the driver to a similar syntax, the parameters must be reconized by the database driver and the database engine (usually a sql server).Depending on the SQL server the query will translate by the database driver natively with different syntax:

SELECT MYDATA FROM MYTABLE WHERE MYCODE=?
SELECT MYDATA FROM MYTABLE WHERE MYCODE=@CODE

This query is "compiled" by the database engine, usually this process parse the query, translating to a native (and fast) format, selects a optimization method (query plan) including indexes used by the engine, the query can be compiled once (also called prepare procedure), and execute multiple times, the successive executions will be lot faster because the database engine can bypass the parse of the query and analizing index statistics to select the best path to execute the query.

So passing parameters to a database involves the database driver and database server, some database drivers, does not support large parameters or does not implement some parameter types (date,time), others are a bit tricky about detecting them, for example:

.. WHERE :CODE=MYCODE can generate a syntax error, but WHERE MYCODE=:CODE works ok, this is because database driver parse the query to translate it to database server intermediate format, and the database driver parser can be not accurately programmed.

Also because the query is compiled by the database server and a optimization plan must be selected, the parameters can not be placed in any part of the SELECT sentence.

SELECT .... ORDER BY :MYORDER1,:MYORDER2

The above sentence is not valid because a parameter inside the order by clause is usually not valid, think that changing the order of a query can change the way the query is optimized by the database server, for example if the where clause and the order by clause references same field, only one index is needed, if they differ, a diferent query plan (index path) is needed to execute the query, so a recompilation of the query is needed.

Some database drivers and servers allow parameters inside the select fields part but others not:

SELECT :MYPARAM,MYFIELD FROM ....

This is also applicable to linked querys, but linked querys takes the parameters from the master query fields by name.

Note: When you missplace a parameter, the database driver can ignore not returning you any error.

Workarounds

Using query parameters is efficient and the prefered way, but when database drivers have some problem, or it's not possible to do it you must find alternative methods. For example, if the database driver does not accept Date parameter type, you can define the parameter as string, the syntax of the date will be defined, then by dabase server, for example '31-JAN-2004' or '01/31/2004'.

So a query can not be compiled/prepared if the order by clause change between executions, or the where clause syntax changes, to allow the use of parameters in this querys, there is the String Substitution parameter type, it's not a real parametrized query, it's a query that will be changed before sending it to the database driver. The use of the :PARAM syntax is not needed.

For example:

SELECT .... ORDER BY 1,2,3

You can create a string substitution parameter assigned to one or more datasets with properties:

Property	Value
Name	ORDERBYCLAUSE
Value	ORDER BY 1,2,3
Search For	ORDER BY 1,2,3
Description	Order by clause

Changing the value of the parameter to:

ORDER BY 2,1,3

Will search for the string ORDER BY 1,2,3 inside the sql sentence, if found, it will be replaced by ORDER BY 2,1,3 then , the new generated sql sentence is executed.

This is also usefull to change some part of the where clause (or the entire clause) or also to change the entire sql sentence at runtime.

How to modify parameter values at runtime

Report parameters are added to the evaluator as variables when the report begin, so if you want to modify the values while the report is executing you must alter the variable value using a expression like:

paramname:='NewStringValue'

From Delphi/Kylix/Builder you can evaluate this expression:

CLXReport1.Report.Evaluator.EvaluateText('paramname:=''NewStringValue''');.

Using parameters from Delphi/Builder/Kylix/ActiveX

You can call parameter window from your application with the method ShowParams:

VCL/CLX Components:

if CLXReport1.ShowParams then

CLXReport1.Execute

You can modify report parameters in runtime in your application.

VCL/CLX Components:

CLXReport1.Report.Params.ParamByName('CUSTOMERINI').Value:=2;

ActiveX:

ReportManX.ParamByName('CUSTOMERINI').Value=2

ReportManX.Report.Params.Items[0].Value=2;

ReportManX.Report.Params.Items.Count;