SSRS - Pass Multi Value Parameter with Available Values Option Enabled Through Querystring

Question

I have one Parameter on my report, that has multi value on.

And it's values came from a Dataset.

On my application, when I request a report, this parameter goes as an Querystring value. But when I use the multi value option, the parameter is not a single value anymore, but an "array" with multiple values (Of Couse).

REPORT?codArea=&codDiretoria=17&ultimaAlteracao=on&formato=PDF&codListaPrioridade=8&codListaPrioridade=13

Important Part:

(codListaPrioridade=8&codListaPrioridade = 13)

The issue, is the "Available Values" option. The Parameter having an Array as a value, don't have any of the "Available Values" and SSRS throw a error.

Important Part:

System.ServiceModel.FaultException: This report requires a default or user-defined value for the report parameter 'codListaPrioridade'. To run or subscribe to this report, you must provide a parameter value.

If i disable the "Available Values" option, i can send this "array" without any issue:

codArea=&codDiretoria=17&ultimaAlteracao=on&codListaPrioridade=9&codListaPrioridade=13&formato=PDF

Important Part:

(codListaPrioridade=9&codListaPrioridade=13)

="----> "& Join(Parameters!codListaPrioridade.Value, ",")

The trick is that I need create in this report, this parameter with "Available Values" enabled, but this parameter need to accept "Any" values (Because the Array that is coming on querystring).

The "Available Values" must be used to show which values are available only (Inside my application).

Inside SSRS, the parameter works fine, the issue only occurs when using this report in my application, sending the parameters values in a querystring.

Answer 1

The key part of the error is "This report requires a default or user defined value for the report parameter."

You don't need a default parameter. It sounds to me like your application is passing an invalid parameter value to the report. The reason it works when you disable the available values is because then it will accept ANY value at all.

Run the query that populates the available values and keep it open.

Set a breakpoint in your ASP.NET code just before it passes the values to the report server. Examine what those values are and make sure it isn't trying to pass anything that ISN'T in the list of available values you got from running the query.

If there is something different then that is the cause of your error. I can't remember off the top of my head if casing matters so you'll want to check that too. And if you check them and everything is fine... check them again. I don't know how many times I've thought there wasn't any data out of place only to suddenly see it later.

---

An alternate theory would be that you just aren't passing the multi-value parameter to the report server correctly. I've never tried to do it directly in the URL before but on the MSDN page Pass a Report Parameter Within a URL in the "other examples" section it shows passing a multi-value parameter. In their example they seem to append a number to the end of the parameter name for each value. But I'm not sure if that is right either.

https://myserver/Reportserver?/SQL+Server+User+Education+Team/_ContentTeams/folder123/team+project+report&teamgrouping2=xgroup&teamgrouping1=ygroup&OrderID=747&OrderID=787&OrderID=12

---

But since you're already writing .NET code let me make a suggestion. Use the webservice as a webservice. It makes things like this so much easier. You just add the web service as a reference to the project and then basically call it like any other .NET class. The reference reads the WSDL file and shows you all of the available API calls and their parameters and what you'll get back. It really is quite a nice feature. Check out this MSDN article on "How to: Add a Reference to a Web Service" and then this TechNet dev guide: https://technet.microsoft.com/en-us/library/bb522713.aspx