Chapter-19-Creating Multi Value Parameter Report in SSRS

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

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

Multi Value Parameter Report in SSRS

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.

Multi Value Parameter Report in SSRS

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.

Creating Multi Value Parameter Report

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

Report wizard

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

Query windows

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

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

Choose tabular Report Type

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

select fields

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

table style

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

completing wizard

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”

query

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

choose DataType filed to “Allow Multiple Values”



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.

get value from query

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

select multiple values

Based on the selected parameter the report will be displayed

image015

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

14 + seven =