I have an SSRS report that uses a multiple value parameter. The available values is single column data set. e.g.
AccountNumber
AccountName
Address
Sales
It's just a list of column heading names.(Each column in the report has a visibility toggle whether the column name was selected as part of the multi value parameter)
The report works fine as a regular report running on demand, and a regular subscription (just selecting which columns I want for various subscriptions).
I need to do a data driven subscription for the same report. As part of the DDS, I pass in the columns I want for this particular subscription:
SELECT 'AccountNumber,AccountName,Sales' as columns
I get the value from the database for the 1 parameter. The subscription errors. (I don't have access to get the specific errors at this point). If I specify multiple columns as a static parameter it doesn't work as well. If i specify 1 column the report works.
Thanks in advance for help !
Update
I was able to get semi workarounds 2 ways.
1. Instead of getting my report's parameter Available Values from a database query, I specified values, which wasn't a problem in this example. Then the typical drop down multi value select box works in a data driven. You would think a query could do the same thing since it talks to the database in the DDS query & in a regular subscription, why not in a data driven subscription?
2. Before i did the above, I added another row in my parameter data set query called "Default". I then made a hidden dummy parameter saying iif the input was default then use a hard coded column list, otherwise use whatever was entered. Then I could enter 1 value in my DDS of "Default". I then had to adjust the column visibility formulas to work for my dummy parameter.
