I have researched this question and so far have found very different opinions as to how or if it is possible.

I have created 2 Data-driven subscriptions in SSRS. 1 for our sales persons and another for our managers reports. For the salespersons report, the solution works brilliantly as expected and I can appreciated the increased simplicitly for having 1 subscription to drive reports to a sales staff of over 100 folks.

One the other hand, I can't seem to grasp the significance of this type of solution for the Sales managers when they have multiple salespersons that report to them. Apparently, SSRS 2008 has provided no practical solution for this scenario that would be easily implemented in a Data driven subscription.

Since I can't find a way to pass in muliple integer values that represent each of the salesreps for a single manager, I am stuck with potentially sending multiple reports for each of the Sales Reps residing under a single manager. Not very elegant or useful as I'd hoped for using Data driven subscriptions. I have even changed the parameter datatype to string and used something like:

paramSalesPeople = substring((SELECT ( ', ' + CAST(territoryid AS VARCHAR(2000)))

FROM Reports..SalesForce t2

WHERE t1.SalesManagerID = t2.SalesManagerID

ORDER BY SalesManagerID

FOR XML PATH( '' )), 3, 1000 )

to create a comma-delimited list of values for the parameters in effort of generating 1 (ONE) record per Manager. But the Data Driven Subscription fails miserably.

So the question, Is it or is it not possible to have multiple values passed as a single parameter to a data driven subscription to consolidate the number of required reports into one. If not, it would seem that it should be possible since it can be done from the reports parameters drop-down menu.

I have read someones recommendation to script the multi-valued parameter which seems to defeat the intent of the term "Data Driven' Subscription if I have to hard-code this logic into a script.

Can someone please provide me with a cut and dried statement as to what the options are from a Data driven perspective. If it's not possible then it's simply is what it is and I can put this controversial topic (for me) to rest.

Thanks

Hi,

I think it can be achieved with web services and not trhough Report Manager or BIDS.


Gouri Sohoni

Hi mindscape777,

From your descriptions, you want to pass a multi-values parameter to a data-driven subscription. If I have misunderstood, please do not hesitate to let me know.

By default, the parameter should be string data type. There is not array data type in current SQL Server version. As you mentioned, we can pass a single-value parameter to the report, and then split the single-value parameter into multi-values parameter in the Report.

Here are the detailed steps to solve the issue:

1. Create a string parameter and move this parameter to top.

The parameter will pass the sale persons Id with comma-delimited such as 1,2,3

2. Change the default values of the paramSalesPeople to be

=Split(Parameters!parametername.Value, ,)

The parametername should be the name of the parameter we created in step1.

3. Now, to pass multi-values parameter to data-driven subscription, we should pass a single-value such as 1,2,3 for the parameter that is created in step1. And set the paramSalePeople to be default(Check the box Use default).

If there is anything unclear, please feel free to ask.

Thanks,

Jin Chen

Excellent!

Thanks so much Jin, I knew there had to be a workaround to the solution.

 

I have a problem with this method.

When i do that and during creation of my data driven subscription, the value for my parameter (the original) is empty, so i can't finalise creation of my subscription :(

The difference is my parameter depends from an other wich have no default value.

Did you find a solution to this?  This is the exact problem I'm having...

 

Thanks,

-Alex

Has this BUG been fixed in SSRS 2012?

Will it ever be fixed?

Jin,

I am not sure I understand.  I have a parameter LOCATION that is a multi value query based parameter.  I need to use this in my data driven subscription in order to send 1 report to each manager containing data for only those loactions that they support.  Could you please explain exactly how I would accomplish this ? Am I setting up 1 additional parameter in the report? Which parm am I referencing in the data driven subscription??

Thanks,

Barbara

I can the follow error ,"This report requires a default or user-defined value for the report parameter XXXX" 

So this set-up doesn't work. Do you have an updated answer? Help would be very much appreciated.