EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials SSRS Tutorial SSRS multi-value parameter
Secondary Sidebar
SSRS Tutorial
  • SSRS Basic and Advanced
    • What is SSRS?
    • SSRS Versions
    • SSRS Cascading Parameters
    • SSRS Parameter
    • SSRS Group
    • SSRS Alternate Row Color
    • SSRS SQL
    • SSRS Subreport
    • SSRS Lookup
    • SSRS LookUpSet
    • SSRS Date Format
    • SSRS Report Builder
    • SSRS ISNULL
    • SSRS Opinion Panel
    • SSRS linked report
    • SSRS DateDiff
    • SSRS Dashboard
    • SSRS IIF
    • SSRS Reports
    • SSRS Number Format
    • SSRS Page Break
    • SSRS multi-value parameter

SSRS multi-value parameter

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.

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.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,629 ratings)

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

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 –

  1. SSRS Versions
  2. What is SSRS?
  3. SSRS Interview Questions
  4. SOA Interview Questions
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more