EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Software Development Software Development Tutorials SSRS Tutorial SSRS multi-value parameter
 

SSRS multi-value parameter

Updated April 3, 2023

SSRS multi-value parameter

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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. 

1-1

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. 

2-2

Next, ensure that the “Allow multiple values” check option is selected in the parameter attributes.

3-3

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)

SSRS multi-value parameter

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

We hope that this EDUCBA information on “SSRS multi-value parameter” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SSRS Versions
  2. What is SSRS?
  3. SSRS Interview Questions
  4. SOA Interview Questions

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW