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


2 comments:

  1. Do you need Personal Finance?
    Business Cash Finance?
    Unsecured Finance
    Fast and Simple Finance?
    Quick Application Process?
    Finance. Services Rendered include,
    *Debt Consolidation Finance
    *Business Finance Services
    *Personal Finance services Help
    contact us today and get the best lending service
    personal cash business cash just email us below
    Contact Us: financialserviceoffer876@gmail.com
    call or add us on what's app +918929509036

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete