Reporting Services Data Driven Subscriptions with Multi Value Parameters


To send a parameterized report to a list of subscribers we can use the Data Driven Subscription.

 

If the WHERE condition in the Report Dataset just contains single parameter values (something like: Select * From Sales where Department = 123)  the setup is fairly simple. (There is already a documentation on how to achieve that: http://msdn.microsoft.com/en-us/library/89197b9b-7502-4fe2-bea3-ed7943eebf3b )

However what if we would like to pass a list of parameters? (like: Select * From Sales where ProductDesc IN ('P1','P3','P9'))

In this case we have to apply some changes in the report as we can just supply one column per ReportParameter in the Subscription Wizard:

Here is an example if my report has a report Parameter Company:

 

 

I can create a table with subscribers and a Company Parameter Column specifying a commaseparated list of Companies (Subscriber_Stocksymbol):

In the Subscription Wizard I have to specify this column as the company parameter:

 

Unfortunately we cannot push this list to the report Dataset directly. Instead a workaround is required:

The trick is to write a User Defined Function to split the input String and return a table->

Then you need to change the Query in the Dataset a little bit:

 Moreover the Report Parameter has to be configured correctly:

(Do not check Allow Multiple Values!)

Don't forget the Mapping between the Dataset Parameter and the Report Parameter:

you can specify default values:

 When you schedule the subscription you should get the correctly parameterized report!

 



Comments (7)

  1. Jan says:

    Hi

    Thx for your post! How do you fill your parameter @p? Can you give a reply on mezelf.forum@gmail.com? Thx!

  2. LukasSteindl says:

    @p is a single comma separated string e.g 'MSFT, GOOG, YAHOO'. you have two options: either you store this string as a varchar in a column in your configuration table like in the example above or create it dynamically like this:  

    Create Table Table1 (Name varchar(10))

    GO

    Insert Into Table1 Values ('ABC'),('XYZ'),('DEF')

    GO

    SELECT STUFF(

       (SELECT ',' + NAME

          FROM TABLE1

           FOR XML PATH(''),type).value('.','nvarchar(max)'),1,1,'') AS [P]    

    And don`t forget that you can also use a view as your configuration data source for the automatic subscription. Regards Lukas

  3. philamander says:

    This is close to what I need. However, I'm working with an @Countries parameter where the SSRS report allows a user to select multiple countries from a list of 320 countries. I need the subscription to 'select all' and run. That would mean I'd have to list all 300+ countries in a comma separated value, correct?

  4. Matt says:

    Can you show the UDF you create to split the Stock Symbols? You did this in Visual Studio under the code section?

  5. Lukas Steindl says:

    Hi, here is a split function I took from : http://www.codeproject.com/…/Split-string-into-Rows-Columns-using-Delimiters

    CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))    

    returns @temptable TABLE (items varchar(8000))    

    as    

    begin    

       declare @idx int    

       declare @slice varchar(8000)    

       select @idx = 1    

           if len(@String)<1 or @String is null  return    

       while @idx!= 0    

       begin    

           set @idx = charindex(@Delimiter,@String)    

           if @idx!=0    

               set @slice = left(@String,@idx – 1)    

           else    

               set @slice = @String    

           if(len(@slice)>0)

               insert into @temptable(Items) values(@slice)    

           set @String = right(@String,len(@String) – @idx)    

           if len(@String) = 0 break    

       end

    return    

    end

    Select * from dbo.Split('1,2,3',',')

  6. Lukas Steindl says:

    here is a good split function:

    (i could not remember my own one)

    took this one from here:

    http://www.codeproject.com/…/Split-string-into-Rows-Columns-using-Delimiters

    CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))    

    returns @temptable TABLE (items varchar(8000))    

    as    

    begin    

       declare @idx int    

       declare @slice varchar(8000)    

       select @idx = 1    

           if len(@String)<1 or @String is null  return    

       while @idx!= 0    

       begin    

           set @idx = charindex(@Delimiter,@String)    

           if @idx!=0    

               set @slice = left(@String,@idx – 1)    

           else    

               set @slice = @String    

           if(len(@slice)>0)

               insert into @temptable(Items) values(@slice)    

           set @String = right(@String,len(@String) – @idx)    

           if len(@String) = 0 break    

       end

    return    

    end

    1. Marc says:

      Hi,

      My query type is storeprocedure which has 4 paremeters.
      And one of them ,I have to use this solution but cant figure it out

Skip to main content