ActiveReports 12
Add a Multi-Value Parameter
ActiveReports 12 > ActiveReports User Guide > How To > Page Report/RDL Report How To > Add Parameters > Add a Multi-Value Parameter

In a page report or an RDL report, you can create a multi-value parameter by selecting the Multivalue option. In a multi-value parameter, you can choose a few options from the list or simply choose 'Select all' to select all options. 
If there are large number of options to choose from, choosing 'Select all' option creates an SQL query too long for an SQL Command to run. In such a case, you can specify a value to the multi-value parameter in Value for 'Select All' option.

The following procedures take you through a step by step process of how to create a multi-value parameter and specify a value for selecting all options from the list. These steps assume that you have added a Page Report/RDL Report template to your report and have a data connection in place. See Adding an ActiveReport to a Project and Connect to a Data Source for further information.

Note: This topic uses the Products table from the NorthWind database. By default, in ActiveReports, the Nwind.mdb file is located at [User Documents folder]\GrapeCity Samples\ActiveReports 12\Data\NWind.mdb.

To create a dataset to populate the parameter values

  1. In the Report Explorer, right-click the Data Source (DataSource1 by default) node and select Add Data Set.

     
  2. In the DataSet dialog that appears, select the Query page.
  3. Enter an SQL query like the following into the Query text box

    select distinct productName from Products 
  4. Click the OK to close the dialog. You see the data set, DataSet1, and the field, productName, in the Report Explorer.

To add a Report Parameter

  1. In the Report Explorer, right-click the Parameters node and select Add Parameter.
  2. In the Report - Parameters dialog that appears, add a name for the parameter, ReportParameter1.
  3. Ensure that the Data type matches that of the field (String for ProductName).
  4. Enter some text in the Text for prompting users for a value field.
  5. Select the check box next to Multivalue to allow users to select more than one item from the list.
  6. In the Value for 'Select All' option, enter '1'.

To provide a list of values for the Report Parameter

  1. In the Report - Parameters dialog, go to the Available Values tab and select the From query radio button.
  2. Under the Dataset field, select the dataset created previous steps (DataSet1).
  3. Under the Value and Label fields, select the field productName.
  4. Click the OK to close the dialog and add the parameter to the collection.

To add a dataset with a parameter

  1. In the Report Explorer, right-click the Data Source (DataSource1) node and select Add Data Set.
  2. In the DataSet dialog that appears, on the Parameters page, click the Add (+) icon above the parameters list and add the following to the dataset to provide values for the parameters we add to the query in step 3 below.
    Name: ReportParameter1; Value: =Parameters!ReportParameter1.Value
    Name: Parameter1; Value: =Parameters!ReportParameter1.Value
  3. On the Query page, enter a SQL query like the following in the Query text box:

    SELECT * FROM products where ProductName in (?) OR '1' in (?)

    At run time, this query matches the selected product name and fetches data accordingly. If the user chooses 'Select all' (for which we have specified value '1'), then query after 'OR' is evaluated and data is fetched for all products.
  4. Click the Validate DataSet icon to validate the query and to populate the Fields list.
  5. Click the OK to close the dialog. You see the data set, DataSet2, and the fields in the Report Explorer.

To view the report

Place a control like a Table onto the design surface and add fields to it. View the report in the preview tab and see the Parameters in the sidebar with Select all option at the top.

 

Note: If the Available Values (queried or non-queried) for a parameter contain only some values from database and Select all value is specified for the parameter, then on previewing report, selecting 'Select all’ checkbox shows all records from the database instead of only those present in the parameter. For example, if in database there are four records and in Available values there are only two records with Select all value specified, then on previewing report and selecting 'Select all' checkbox, all four records are shown instead of only two.