Report parameters is a powerful feature, here is a list of samples you can do with parameters
You can define the parameters, and also preview how the parameters will show to the user.
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:
|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.|
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.
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.|
There are special parameters that can be used to interact with the engine itself, just define and assign values to them:
|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|
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|
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:
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:
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.
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.
SELECT .... ORDER BY 1,2,3
You can create a string substitution parameter assigned to one or more datasets with properties:
|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:
From Delphi/Kylix/Builder you can evaluate this expression:
Using parameters from Delphi/Builder/Kylix/ActiveX
You can call parameter window from your application with the method ShowParams:
if CLXReport1.ShowParams then
You can modify report parameters in runtime in your application.