Definition of SSRS multi-value parameter
The multi-value parameter allows us to pass one or more values to the analysis in addition to the input parameter. It also has a “Select All” option for selecting all parameter values. The SSRS Multi-Variable Parameter can interactively filter reports using more than one value. Except for Boolean, we can make any parameter category multi-value. Depending on their importance, multi-value parameters are primarily used to provide query restriction clauses.
Overviews SSRS multi-value parameter
Reporting Services generally keeps the selected values as an array. At the same time, we set up a parameter to accept multiple values, which impacts how we use it in an expression or query. For example, suppose we want to filter a dataset using a multi-value parameter as just a query parameter. In that case, we could do so by including a WHERE clause and employing the variable as a list to the right of IN, as shown below:
WHERE TableColumnValue IN (@ParameterName)
When working with multi-value parameters, we must choose an appropriate delimiter and add additional values such as “ALL” or “BLANKS,” among many other things. For example, in SSRS, we may add a multi-value parameter having additional values (all) to disable the parameter and (blank) to pick blanks and NULLs.
While working on an SSRS report in Visual Studio 2008 and wanting to have a parameter to show multiple values, even NULL values, we would get a warning message saying, “A multi-value parameter cannot include null values.”
There is a two-step solution to this problem.
1) Add an entry to generate a (NULL) element in the dataset, which supplies entries for the parameter when creating the query.
2)The second step is to change the primary dataset so that all real NULL values in the desired field in the query results were altered with the artificial (NULL).
Using SSRS multi-value parameter
Click the Parameters nodes, right-click on the report parameter that has been instantly produced for the dataset’s parameters, and afterward click Parameter Parameters to pick a multi-value parameter directly to that same Report Data pane. Finally, select Allow multiple values on the Navigation pane to enable a customer to choose or more one option for the parameter.
Step-1 Using Delimited String in multi-value
Here is a simple way to show this. First, the primary reports query parameter must be configured to expect a string of values.
| SELECT Prod_ID,I_No ,I_Des
FROM DIM_Prod WHERE I_No IN (@ITEM) |
Step-2: Parameter Setting
Assign a name and the data type and set up a parameter to text.
Step-3: Applying values for the parameter
Setting up a second data set to provide the parameter with these default values.
SELECT '11000B,2200,17000' AS ITEMNO
Here on -the Parameters pane of the Dataset Specifications for the preliminary report dataset, update the expression for the Parameter Value.
The following should be written in the parameter expression.
=split(Parameters!Item.Value,",")
This takes a comma-separated string as input, divides it into particular values, and uses it in the IN clause. And the final report eliminates commas, and the result is given as
Create a report SSRS multi-value parameter
Show the entries of the multi-value parameter that we want to see:
- To the report, add a textbox.
- Then, select the Expression menu item with just a right-click on the textbox.
- “Parameter Values: “should be written in the value field. &
- JOIN (Parameters! saleParam. value, “, “)
Multi-value SSRS parameter
Within the parameter list, one can select several options. Both queried and non-queried reports can have multi-valued elements. The multi-value parameter allows us to pass one or more values to the report in addition to the input data. It also has a “Select All” feature for selecting all model parameters. In SSRS, we’ll now develop an example of a multi-value parameter. For just an SSRS report, a multi-valued comma-delimited source parameter.
How to Use SSRS to Create a Multi-Valued User Input Report Parameter?
Step-1: Add a data source.
Step 2: Using just a Stored Procedure, add the dataset.
Step 3: Modify the value expression for such Dataset parameter.
Step 4: Add Tablix to a mix.
Step 5: Look over the report.
Let’s look at how to implement a multi-value parameter step by step.
First, we need to create a Data Source for the report. To do so, right-click on the Data Sources folder and select Properties from the menu. As seen below, it offers details for the Data Source connection string.
We should first facilitate multiple values for a parameter to allow randomized, multiple-value selections in its drop-down menu.
Right-click on the parameter inside the Parameters folder in the Report Data window of the report and choose Parameter Properties.
- Select Allow multiple values from the drop-down menu.
- Click the OK button.
Next, ensure that the “Allow multiple values” check option is selected in the parameter attributes.
We’ll edit the Prompt field after clicking the Allow multiple values option in the General tab to make the parameter a multi-value parameter. In addition, the parameter’s name inside the report will alter due to changes. After applying these modifications, we’ll proceed on to the Available Values tab:
Click preview to list the country location list. For example, Albania, Algeria, and Andorra would be the parameters. Then, within the report’s dataset, we have to tell the query to choose rows in which the nation is one of them. This is done with an IN operator, and the parameter must be delimited in parenthesis:
Right-click on the report’s dataset in the Report Data window and select Dataset Properties.
To the query, add a WHERE clause like this: WHERE COUNTRY IN (@Country)
Several of the reports we produce in the future will also include more. Either the dataset will contain numerous predicates in the WHERE clause, or variables will be used to customize how the report appears.
Conclusion
Coming to an end, we have discussed deeply multi-value and seen the creation of a report. Multiple value parameters are a fantastic feature of SSRS. It’s also simple to use a multiple value parameter with only an object filter if we know how to construct the parameter values in the filter.
Recommended Articles
This is a guide to SSRS multi-value parameters. Here we discuss the definition, overview, Create a report SSRS multi-value parameter, and Examples with code implementation. You may also have a look at the following articles to learn more –
