ActiveReports allows you to use parameters to filter or add the data to display in reports at run time. You can either prompt users for parameters so that they control the output, or supply the parameters behind the scenes.
Adding parameter for different data sources
A query parameter can get its value from the Report Parameters collection (entered by the user or from a value you supply), a field in another dataset, or an expression. Syntax for adding a parameter in your query might differ depending upon the data source that you are using. Use the syntax specific to your data source type to create a parameter.
Parameterized query for different data sources are as follows:
| Data Source | Parameter Syntax | Example |
|---|---|---|
| OleDB | (?) | SELECT * FROM Customer WHERE (CustomerID = ? AND AccountNumber = ?) |
| ODBC | @ParameterName | SELECT * FROM Customer WHERE (CustomerID = @CustomerID AND AccountNumber = @AccountNumber) |
| SQL Client | @ParameterName | SELECT * FROM Customer WHERE (CustomerID = @CustomerID AND AccountNumber = @AccountNumber) |
| OracleDB | :ParameterName | SELECT * FROM Customer WHERE CustomerID = :CustomerID AND AccountNumber = :AccountNumber |
Page Report/RDL Report
In a page report or a RDL report, the easiest way to build queries with parameters is to use the Visual Query Designer, as it automatically sets up each parameter.
In the DataSet dialog, click on 
to access Visual Query Designer for creating SQL queries. See Query Building With Visual Query Designer for further information on how to create a parameterized query using the interactive query designer.
However, if you would like to do it manually, you must enter each parameter in three locations: the Report Parameters dialog (for filtering data at run time), the Parameters page of the DataSet dialog, and the Query page of the DataSet dialog.
Report - Parameters dialog
The Report - Parameters dialog allows you to control how and whether a user interface is presented to your users for each parameter. You have to set the following properties in the dialog to create a parameter:
- Enter a report parameter name. Each report parameter in the collection must have a unique name, and the name must match the name you call in the Parameters page of the DataSet dialog. In the example above, the name is MPAA.
- Set the data type, the text used to prompt the user, whether to allow null, blank, multiple values or multiline text, and whether to hide the user interface.
- Select the default value or populate a list of available values from which users can choose.
Parameter values are collected in the order they appear in the Report Parameters collection. You can change the order using the arrows in the Report - Parameters dialog.
General Tab
- Name: Set the name for the parameter in this field. The value you supply here appears in the parameters list and must match the corresponding query parameter.
-
Data type: Set the data type for your parameter which must match the data type of the field that it filters. The interface presented might also differ depending on the data type.
- Boolean: Presents the user with two options True or False
- Date: Presents the user with a calendar picker to select a date if you do not supply a default value or a drop-down selection of available values
- DateTime: Presents the user with a calendar picker to select a date and a time picker to select the time in cases where you do not supply a default value or a drop-down selection of available values
- Integer: Presents the user with a text box or a drop-down selection of available values
- Float: Presents the user with a text box or a drop-down selection of available values
- String: Presents the user with a text box or a drop-down selection of available values
- Text for prompting users for a value: Enter the text you want to see on the user interface to request information from the user in this field. By default this is the same as the Name property.
- Allow null value: Select this check box if you want to allow null values to be passed for the parameter. It is not selected by default.
- Allow blank value: Select this check box if you want to allow blank values to be passed for the parameter. It is not selected by default.
- Multivalue: Select this check box to allow the user to select multiple items in the available values list.
For a multi-value parameter, you can also allow user to specify special value for 'Select all' option in the Value for 'Select All' property. - Multiline: Select this check box to allow multiline values in the parameter. The control will automatically adjust to accommodate multiple lines.
- Hidden: Select this check box to hide the parameter interface from the user and instead provide a default value or pass in values from a subreport or drill-through link. Please note that if you hide the user interface and do not provide a default value, the report will not run.
Available Values
These values are used to fill a drop-down list from which the end user can choose.
- Non-queried: You can supply Labels and Values by typing in static values or using expressions.
- From query: You can select a Dataset from which to select a Value field and Label field.
Default Values
- Non-queried: You can supply a default Value by entering a static value or using an expression.
- From query: You can select a Dataset from which to select a Value field.
- None: You can have your users provide a value for the parameter.
To access the Report - Parameters Dialog
You can access the Report - Parameters dialog through any one of the following:
- In the Report Explorer, click the Add (+) icon and select the Parameter option.
- In the Report Explorer, right-click the Parameters node and select Add Parameter.
- In the Report Explorer, right-click the Report node and select Report Parameters.
- From the Report Menu, select Report Parameters.
The Report Parameters dialog contains a parameters page with a list of parameters and three tabs to set parameter properties. To add a parameter to the list, click the Add (+) icon and set the parameter properties in the three tabs described below.
Parameters page of the DataSet dialog
On the Parameters page of the DataSet Dialog, pass a Report Parameter into the parameter in your query. You can click the Add (+) icon at the top of the parameters list, enter parameter name, and supply a value like:
=Parameters!MPAA.Value
Query page of the DataSet dialog
SELECT * FROM Movie WHERE (MPAA = ? AND YearReleased = ?)
If you want to run a report without prompting the user for a value at run time, you need to set a default value for each parameter and the Hidden check box should be selected in the Report - Parameters dialog, General tab.
Subreport parameters are also considered as hidden parameters as a user can easily synchronize a subreport's data with that of the parent report . See Subreports in RDL Reports for further details.
Drill-Through parameters are also hidden parameters as drill-through links are used to navigate from one report to another. When you select Jump to report for the action, the parameters list is enabled.
Section Report
In section report, you can use the Parameters collection to pass values directly into a control at run time, or you can also use it to display a subset of data in a particular instance of a report.
There are several ways for setting up parameters in a report:
- You can enter syntax like the following in your SQL query to filter the data displayed in a report at run time:
<%Name | PromptString | DefaultValue | DataType | PromptUser%> - You can add parameters through the Report Explorer and place them on the report as TextBox controls to pass values in them at run time.
- You can also add parameters through the code behind the report, inside the ReportStart event. See Add Parameters for more information.
Prompting for Parameter Values
In order to prompt the user for parameter values, all of the following must be in place:
- At least one parameter should exist in the Parameters collection of the report.
- The PromptUser property for at least one parameter must be set to True.
- On the report object, the ShowParameterUI property must be set to True.
When there are parameters in the collection and the ShowParameterUI property is set to True, the user prompt automatically displays when the report is run. When the user enters the requested values and clicks the OK button, the report gets displayed using the specified values.
Values of a parameter added through the Report Explorer can be applied to a parameter in the SQL query by specifying the param: prefix for a parameter in the SQL query. This prefix relates the current parameter to the one in the Report Explorer.
For e.g., select * from CUSTOMERS where CustomerName = '<%param:Parameter1%>'. In this case, the parameter with the param: prefix in the SQL query is updated with values of the corresponding parameter in the Report Explorer.
Note: Within the same report, you can prompt users for some parameters and not for others by setting the PromptUser property to True on some and False on others. However, if the report object's ShowParameterUI property is set to False, the user prompt does not display for any parameters regardless of its PromptUser setting.
Adding Parameters to the Parameters Collection via the SQL Query
When you add a single parameter to a report's Parameters collection via the SQL query, the query looks like this:
| SQL Query. |
Copy Code
|
|---|---|
| SELECT * FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID WHERE Products.SupplierID = <%SupplierID|Enter a Supplier ID|1|S|True%> |
|
You can also create a parameterized query from the Visual Query Designer. See Query Building With Visual Query Designer for further information on how to create a parameterized query using the interactive query designer.
There are five values in the parameter syntax, separated by the pipe character: |
Only the first value (Name) is required, but if you do not specify the third value (DefaultValue), the field list is not populated at design time. You can provide only the Name value and no pipes, or if you wish to provide some, but not all of the values, simply provide pipes with no space between them for the missing values. For example, <%ProductID||||False%>
Name: This is the unique name of the parameter, and corresponds to the Key property in parameters entered via code.
PromptString: This string is displayed in the user prompt to let the user know what sort of value to enter.
DefaultValue: Providing a default value to use for the parameter allows ActiveReports to populate the bound fields list while you are designing your report, enabling you to drag fields onto the report. It also populates the user prompt so that the user can simply click the OK button to accept the default value.
DataType: This value, which defaults to S for string, tells ActiveReports what type of data the parameter represents. It also dictates the type of control used in the user prompt. The type can be one of three values.
- S (string) provides a textbox into which the user can enter the string.
Depending on your data source, you may need to put apostrophes (single quotes) or quotation marks around the parameter syntax for string values.
For example,'<%MyStringParameter%>'
Also, if you provide a default value for a string parameter that is enclosed in apostrophes or quotation marks, ActiveReports sends the apostrophes or quotation marks along with the string to SQL.
For example,<%MyStringParameter||"DefaultValue"|S|False%> - D (date) provides a drop-down calendar control from which the user can select a date.
Depending on your data source, you may need to put number signs around the parameter syntax.
For example,#<%MyDateParameter%># -
B (Boolean) provides a checkbox which the user can select or clear.
If you provide a default value of True or False, or 0 or 1 for a Boolean parameter, ActiveReports sends it to SQL in that format.
Note: In case of Microsoft Access Database, the default value for boolean parameter is specified as -1(true) or 0(false).