Passing multi valued parameters in Data Driven Subscription in SSRS

Question

I need to schedule a report to be delivered by email in SSRS. I opted for data driven subscription since the report needs to be delivered to a distribution list.

I have 2 parameters, Store and DateTime. Store is a multi valued parameter and user has to select Datetime. But when I try to run data driven subscription, its prompts error!

I went through a few posts stating DDS does not accept multi valued parameters!

Is there a work around?? Can anyone provide a detailed description of how this can be done?

Thanks, Iswarya

Answer 1

May be you could try sending the parameters in with a delimitation and split the multi parameters out once passed into the sql.

I've done this a few times and the difficult bit, may be, to split these out. Admittedly I was using Oracle.

Answer 2

I had the same problem and solved it by using the listagg() function to create a comma separated list of the values in the DDS query. For example,

SELECT email_address , listagg(transaction_id, ', ') WITHIN GROUP (ORDER BY transaction_id) AS trans_ids FROM my_table

The result will be a single-value string that can be passed to the report (i.e., '001, 002, 003'). In the report query, I used instr() to parse the string:

SELECT ..... WHERE instr(:Transaction_ID, transaction_id) > 0

Answer 3

Values passed to a SSRS multi string parameter need to be in the list of available values. An error may also occur if report parameter values are retrieved at run time but no data (null) is returned by the query used to retrieve available values.

Hope this helps and Good luck!