When designing an SSRS report with a multiple value parameter that isn’t a static dimension, you’ll need this trick to pass a valid parameter value to a data driven subscription.
I was designing a report where I needed to be able to select a customer, and then based on the customer that I picked, a second parameter would populate with the available locations for that customer. I also needed to be able to select multiple locations for that customer for the report.
The Location parameter’s Available Values are populated with a SQL query that pulls the available locations using the Customer parameter. For this to work, one needs to have the Customer parameter above the Location parameter in the Parameters list:
One can reorder the parameters by selecting a parameter and using the up and down arrows in the parameter strip menu.
The first problem that many people run into is that a multiple valued parameter is passed as an array. Instead of Parameters!Location.Value containing a single value, one has to iterate through the array, or in my case, I put the values into a SQL IN() clause with dynamic SQL like this:
“SELECT … WHERE location IN (” & Join(Parameters!Location.Value, “,”) & “) …”
This worked fine for me until I needed to make a data driven subscription where I send a copy of the report to the customer with all of their locations selected.
I was pulling the customer from the database along with his or her email address, but I couldn’t populate the list of the customer’s locations in the subscription.
First off, one will notice that the list of locations is blank instead of showing the list of available values based on the Customer. Which makes sense, because no customer is selected, but one can’t type in a list of values for the Location parameter because the list will be different for each customer.
Second, one will notice that if one tries to pass multiple location values, that one needs to pass them as an array, but an array isn’t supported. Even if one populates a VARCHAR with multiple values separated by carriage return, like one can manually enter into the Location static values in the above step, one will encounter one of these errors:
Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: The value provided for the report parameter ‘Location’ is not valid for its type.;
Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter ‘Location’ is not a valid value.;
Worse, if one selects a static value for the Location value using the radio button, but leaves the value blank, one will think that the report has processed successfully when checking the status, because it won’t show any errors, like this:
Done: 119 processed of 119 total; 0 errors.
The way I worked around this was by adding a hidden parameter to the report that I could pass to indicate that it was being run for all locations.
I set the default value for this boolean parameter to false, so that when the report was being run directly, it wouldn’t automatically run for all locations. However, I could set the hidden parameter to true in my data driven subscription in order to have the report run for all locations.
Now I just needed to update my SQL to support this logic, by changing my dynamic SQL to this:
“SELECT … WHERE ( ” & Parameters!all.Value & ” OR location IN (” & Join(Parameters!Location.Value, “,”) & “)) …”
This would now run the report for all of the locations if I set the all parameter to true in my subscription.
However, I still needed to send at least one valid value to the Location parameter, which is fine, because I found that I could send one value without using an array, just not multiple values. I handled this by changing the query in the data driven subscription to send the MAX() location from the database for that customer.
SELECT customerid , email , MAX(location) AS location FROM my_table GROUP BY customerid , email;
I could then pass the “all” parameter as true to return all of the locations even though I was only passing one value to the Location parameter.
I could now send my customers automated email with a data driven subscription, with all of their locations selected, without having to design a second version of the report without the multiple value parameter.