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
Do you need Personal Finance?
ReplyDeleteBusiness 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
This comment has been removed by the author.
ReplyDelete