ActiveReports 18 .NET Edition
Report Authors / Design Reports / Design Page/RDLX Reports / Interactivity / Parameters
In This Topic
    Parameters
    In This Topic

    ActiveReports allows you to use parameters to filter or add the data to display in reports. You can either prompt users for parameters so that they control the output, or supply the parameters behind the scenes.

    In a Page or an RDLX 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 Button to access Visual Query Designer to create SQL queries. See Query Builder in Microsoft SQL Client and OLEDB Providers for further information on creating a parameterized query using the interactive query designer.

    However, if you would like to create parameterized query manually, you must enter each parameter in three locations:

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

    The Report - Parameters dialog contains a parameters page with a list of parameters and three tabs to set parameter properties. You need to set the following properties in the dialog to create a parameter:

    1. In the General tab, enter a report parameter name. Each report parameter in the collection must have a unique name, and the name must match the name you enter or call in the Parameters page of the DataSet dialog.
    2. 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. For parameters with Date or DateTime data types, you have the choice to select the display format.
    3. Go to the Available tab to populate a list of available values from which users can choose, or the Default tab to set the default value.

    The tabs in the Report - Parameters Dialog are explained below.

    • 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.
    • 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.
    • Display format: Choose a format for a Date or DateTime data type. The selected format is displayed in the preview in the Parameters pane. The Display format applies to parameters with available values specified by a query and the default parameter value.

    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.
    This is the value that you give for the parameter if the user does not supply one, or if you hide the parameter user interface. You can choose from 'Non-queried' and 'From query' options.
    • 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.
    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:

    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

    On the Query page of the DataSet Dialog, enter the parameter in the SQL query. Use the syntax specific to your data source type to create a parameter. For example, with an OleDB data source, add a query like the following for a multi-value Movie Rating parameter:

    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 hidden parameters, as a user can easily synchronize a subreport's data with that of the parent report. See Subreport 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.

     

    Parameter in SQL query for 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 (?) 
    SELECT * FROM Customer WHERE (CustomerID = ? AND AccountNumber = ?)
    SQL Client @ParameterName 
    SELECT * FROM Customer WHERE (CustomerID = @CustomerID AND AccountNumber = @AccountNumber)
    OracleDB :ParameterName 
    SELECT * FROM Customer WHERE CustomerID = :CustomerID AND AccountNumber = :AccountNumber