You can 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.
Go to Parameters in the right pane of the designer and click Add. The new parameter will be displayed under PARAMETERS.
Click the added parameter to open the Edit Parameter pane.
Set the following values for parameter settings in the Edit Parameter pane.
• Name field: Specify the parameter name.
• Prompt field: 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.
• Data Type field: Set the data type for your parameter which must match the data type of the field that it filters.
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.
• Multivalue field: Select this check box to allow the user to select multiple items in the list of the available values. For a multi-value parameter, you can also allow the user to specify a special value for the 'Select all' option in the Value for 'Select All' property.
• Hidden field: 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.
• Allow Null Value field: Select this check box if you want to allow null values to be passed for the parameter.
• Multiline field: Select this check box to allow multiline values in the parameter. The control will automatically adjust to accommodate multiple lines. It is only available for String data type.
• Allow Blank Value field: Select this check box if you want to allow blank values to be passed for the parameter. It is only available for String data type.
Add Available Values that are used to fill a drop-down list from which the end-user can choose.
• From query: You can select a Dataset from which to select a Value field and Label field.
• Non-queried: You can supply Labels and Values by typing in static values or using expressions. To specify an expression, click Expression to open the Expression Editor.
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.
Add default values, which is the value that you give for the parameter if the user does not supply one, or if you hide the parameter user interface.
• Non-queried: You can supply a default value by entering a static value or using an expression. To specify an expression, click Expression to open the Expression Editor.
• From query: You can select a Dataset from which to select a Value field.
To exit the Edit Parameter pane, click the Back icon.
Hover the cursor over the parameter you want to remove, and click the Remove icon.
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 a large number of options to choose from, choosing the '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 the Select All Value field.
In the following steps, we will add a multi-value parameter and filter our report according to the value selected from the drop-down list in the parameter input. Let us assume that we already have a report that displays Product details – Product ID, Name, Supplier ID, Quantity Per Unit, and Unit Price fields, and we want to filter the report with 'Name' as a parameter.
Create a dataset to populate the parameter values. To do so, select the Products Data Set under the Data tab and click the Edit Data Set option.
In the Edit Data Set dialog, enter a query like the following into the Query text box.
Click Validate and then OK to close the dialog.
Add a report parameter - go to Parameters in the right pane of the designer and click Add. Click the added report parameter to display the Edit Parameter panel.
In the Name field, enter a name for the parameter – for example, Parameter1. Ensure that the Data type matches that of the field (String type for Name).
In the Prompt field, enter some text for prompting users for a value field.
Set Multivalue property to allow users to select more than one item from the list.
To provide a list of values for the Report Parameter, go to Available Values > From query and in the Data Set Name field, select the dataset created at previous steps 1-3 (DataSet1).
In the Value Field and Label Field, select the field from the drop-down list (name).
To filter the Product dataset using report parameters, select the dataset, and click Edit Data Set.
In the Edit Data Set dialog, enter a query like the following in the Query text box:
="$.[?(@.name=='" & Join(Parameters!Parameter1.value, "'|@.name=='") & "')]"
Click OK to close the Data Set dialog.
Click Preview. Select the items to pass multiple values for the parameter. You can select the items from the drop-down or type the names of the items, and then click Preview.