Show All

Hide All

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

Note: If control characters like NULL, SOH, etc., are present in parameter values and report is previewed on Server (in HTML5 viewer or Thin viewer):

Control characters are changed to their hex representation.
Converted hex value appears in parameter dropdown instead of whatever the 'label field' is chosen in parameter.
'Valid value expected' error appears on previewing report.

For example if Parameter value contains 'Chr(&H000)' code for Null value, then the parameter dropdown displays '0x0'.

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.

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.

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.

Note: In the Available Values tab , the Value is what is passed to the query parameter, and the Label is what is shown to the user. For example, if the Value is an Employee Number, you might want to supply a more user-friendly Label showing Employee Names.

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.

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

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 Page/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.

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).

PromptUser: This Boolean allows you to tell ActiveReports whether to prompt the user for a value. This can be set to True for some parameters and False for others. If you set the report's ShowParameterUI property to False, users are not prompted for any parameters, regardless of the PromptUser value set for any parameter in the report.