How to Pass Multi-Value Parameters to Data Driven Subscription in SSRS - Solved - SSRS Tips

How to pass multi-value parameters to DDS (data driven subscription). SSRS – Passing multiple value parameters in data driven subscriptions video show you how you can pass the multiple values to parameter in data Drive subscription in SQL server reporting Services. There is no straight forward way to handle that so I have used dynamic SQL and then use concatenated values for dataset of Data Drive Subscription.

Sample Stored Procedure for Data Driven Subscription example:

USE [TechBrothersIT] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[sp_TotalSale] @Region VARCHAR(500), @Country Varchar(500) AS BEGIN Declare @var_Country VARCHAR(500) SET @var_Country = Replace(@Country, ',', ''',''') --print @var_Country Declare @SQL NVARCHAR(MAX) SET @SQL = ' Select [SalePersonFName], [SalePersonLName], [ProductName], [ItemsSold], [SoldPrice], [SoldDate], [City], [State], [Country], [Region] from dbo.TotalSale Where Region = '''+@Region+''' And Country in ( '''+@var_Country+''' ) ' --Print @SQL Execute( @SQL ) END

Query for Data Driven Subscription DataSet:

SELECT Region, Region as Filename, stuff( ( SELECT distinct ',' + [Country] FROM dbo.TotalSale WHERE Region = t.Region FOR XML PATH('') ), 1, 1, '') as Country FROM ( SELECT DISTINCT Region FROM dbo.TotalSale ) t

SSRS Data Driven Subscription Multi Value Parameters