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!