Report Datadriven Subscription with multiple values

The issue I ran into here was that I had created a report running from a cube. I then setup the data driven subscription to read a parameter from a table, generate the reports, and store them in Excel format. My problem was that I had another parameter which had to have multiple values.

This is all good if you can hardcode the multiple values when you create the subscription, but what if the values had to change? As was the case.

Setting parameterized defaults on the report did not work... Don't know why though. When running the report manually it seemed fine, but running with a subscription caused the report to process indefinitely.

The Solution:
You will need to setup a scheduled storedproc or SSIS for this one... Probably at the same time you want your data driven reports to run.
Calculate the proper values for the multi value parameter, then store them in the "Subscriptions" table of the "ReportServer" database. This table holds the definitions for all the subscriptions created.
The values for the parameters will go in the "Parameters" column. It is in xml format so it will look like this:


<ParameterValues>
<ParameterValue>
<Name>Param1</Name>
<Field>Param1Field<Field>
</ParameterValue>
<ParameterValue>
<Name>Param2</Name>
<Value>Param2MultiValue1</Value>
</ParameterValue>
<ParameterValue>
<Name>Param2</Name>
<Value>Param2MultiValue2</Value>
</ParameterValue>
</ParameterValues>

Now just update the table in SSIS, and run the scheduled task (which can be found in SQL Agent) using the "Execute SQL Server Agent Job" task

‘Njoy
Tiaan Otto