SSRS includes a multi-value parameter option that enables users to select one or more options from an embedded query. This feature provides users and developers with an advanced filtering option

Download Tutorial for Chapter-19-Creating Multi Value Parameter Report in SSRS

Download Source Code for Chapter-19-Creating Multi Value Parameter Report in SSRS

Prerequsite

1. Create Datasource connected with SQL Server 2012 sample Database Northwind “NorthwindDataSource”.

(as expianed in “Chapter-1-Creating a basic SSRS Report”)

2. Create a Report Server Project “BasicSSRSChapter19” using Visual Studio 2010 or higher Version (as expianed in “Chapter-1-Creating a basic SSRS Report”)

Step-1 Open  SQL Server Enterprise Manager, click the ‘‘New Query’’ button to execute the

following code using the database “Northwind”.


CREATE FUNCTION [dbo].[UDF_SplitString](@textstring varchar(max),@delimiterused varchar(2) = ' ')
RETURNS @Strings TABLE(stringpos int IDENTITY PRIMARY KEY,Itemname varchar(max))
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@textstring) > 0)
BEGIN
SET @index = CHARINDEX(@delimiterused , @textstring)
IF (@index = 0) AND (LEN(@textstring) > 0)
BEGIN
INSERT INTO @Strings VALUES (@textstring)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@textstring, @index - 1))
SET @textstring = RIGHT(@textstring, (LEN(@textstring) - @index))
END
ELSE
SET @textstring = RIGHT(@textstring, (LEN(@textstring) - @index))
END
RETURN
END


This code will create a new user-defined function (UDF) that will allow you to pass in a comma-separated list and split the string into individual values.

Step2- Create a new procedure using “Northwind” database with folloiwng code which return the dataset for the reports

-- =============================================
-- Author:
-- Create date: <07/08/2016>
-- Description:
-- =============================================
CREATE PROCEDURE SSRS_Procedure_CategoryWiseProduct
@cateid varchar(200)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
SELECT ProductID,ProductName,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock
FROM Northwind.dbo.Products
where CategoryID in(select stringpos from dbo.UDF_SplitString(@cateid,','))
END
GO


Step-3: Right Click on the Project  “BasicSSRSChapter19” and select context menu Add>New Items as shown in the image.

Right Click on the Project “BasicSSRSChapter19” add New Item window will appear choose “Report Wizard” and provide the name of the report as “Chapter-19-Creating Multi Value Parameter Report in SSRS” as shown in the image and click oK button, it will bring the Report Wizard window.

In the Report wizard window just click next as we had already created DataSource as “NorthwindDataSource”

Step4: Click on next a Query windows will appear as shown below and now click on “Query Builder” Button

A query designer window will appear as shown below. Type the name of the stored procedure created in step-2 and choose command type as “StoredProcedure” as shown in the image and click on OK Button. 

Choose tabular Report Type and click on next button and click on next button

Now from available column value  all the columns to detail section of the report  as shown and click on next button.

Now chose style of table and click on next button a next screen with finish button will appear.

Click on finish button this will complete the wizard and report will be built up.

In  the design view adjust the width of each column accordingly.

Now go to view menu>Report Data a report Data Data window will appear on the left side of screen as shown now create a new dataset “DSParameter”

Now right click on parameter “cateid”  and choose parameter properties change the Prmopt to “Category ID” and choose DataType filed to “Allow Multiple Values” as shown

Now click on Available value tab  chose Dataset “DSParameter” and value field “CategoryID” and Lable Field “CategoryName” as shown in the image. And click on finish button.

Now click on preview button a parameter with mutiple select value will appear check the parameter which is desired in the report and click on “view report” button

Based on the selected parameter the report will be displayed

Related Posts:
 
Chapter-1: Creating a basic Tabular SSRS Report using Report Wizard
Chapter-2 : Report Deployment on SQL Server 2012 Reporting Server
Chapter-3:Opening/Calling the deployed SQL Server Reporting Server report from Asp.net
Chapter-4 Creating SSRS Matrix Report / Cross Tab Report
Chapter-5 Creating a Sub Report in SSRS
Chapter-6: Creating a Drill Down Report in SSRS
Chapter-7: Working with expressions and custom code in SSRS
Chapter-8-SSRS (Reporting Services) Working with Calculated fields
Chapter-9-Sorting of Column and Custom Paging in SSRS
Chapter-10 : Creating and Deploying Web Services for SSRS
Chapter-11 : Creating SSRS Report by consuming Web Service Data Source
Chapter-12 : Working with Data Bars in SSRS
Chapter-13-Displaying Color in Data Bar Based on Condition
Chapter-14- Displaying Data Labels along with Data Bars in SSRS
Chapter-15-Displaying Indicators in SSRS
Chapter-16-Creating SSRS Linked Report
Chapter-17 Adding or Creating Drop Down Parameter List in SSRS
Chapter-18 Creating Group above or stepped report in SSRS
Chapter-19-Creating Multi Value Parameter Report in SSRS
Chapter-20-Using CSS Stylesheet in SSRS