SSRS 2008 R2 - Default Multi Value Parameters are Not Selected

Question

Hello,

I've been running into this issue quite a few times, and have been unable to solve it through reading various posts/forums.  Here is the issue

Software Details:

Datawarehouse Database - SQL Server 2008 R2

Reporting Services Version - SSRS 2008 R2

Development Environment - Visual Studio 2008

Problem Details:

I have been writing reports based of a Relational Datamart with dimensions and facts.  The report in question uses dimensions for parameters and facts for content.  Multi valued parameters are enabled in the report query by using the 'IN(@Paramenter)' statement within the report query.  It has a total of 6 multi-value parameters.  I assign the same available values from dimension datasets for each parameter to it's default parameter. Theoretically all the values in the option lists should be selected after first render.  This is not always the case.

Example:

SELECT *

FROM dbo.MyTable

WHERE

TableColumn0 IN (@Parameter0)

AND

TableColumn1 IN(@Parameter1)

AND ....N (continue syntax to 6 multi valued parameters)

 

Some of the multi-value drop down lists are defaulting to have every value selected, and some are not .  The problem is I need to know why some parameters are not selecting all by default when specified.

 

Thank you in advance for your help.

 

Russ D

 

Answer 1

Hi RussD,

I test your scenario in local environment, all the parameters hava a normal behavior of having all the value selected, so I would recommend you have a check to see whether the parameters' default value is same as the available value you set, please make sure a parameter's default value and availabe value are from the same dataset and same datafield.

Thanks,
Challen Fu

---

Challen Fu [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

hi russ d,

please check  report->report parameters->default values->select none.

mark as asnwer if this post solves your issue.

 

 

Answer 3

In Report Parameters properties, go to Default value section. Check if you have provided a default value. If not, then provide a value directly or through a query.

Thanks.

---

-Nitin Pawar

Answer 4

Russ I have the exact same problem. I also assumed it was something related to what Challen mentioned but I ran that to the ground. If you find the answer I would also love to know the reason/solution.

Thanks

Chris

Answer 5

I was experiencing the same thing as well and tried to find the answer here unsuccessfully.  Default values for some multi-value dropdowns are selected and some are not.  It was just really weird and annoying as there are no error/warning messages from the compiler and everything seems ok except for the fact that the default values will not be selected for some of the dropdown parameters.

After some fiddling around, i found out the reason for this behavior.  If the default values return a NULL as part of its dataset, then it will not work (the multi-value dropdowns do not allow NULLs to be selected anyway).  You can include a WHERE clause to remove the NULLs from the query and the default values will be selected when the report is run.

Hope that helps anyone else who stumbles on this page looking for an answer to the same problem.

 

Answer 6

I had the same problem, but the parameter values were static, not based on query. 

The solution was to delete the report from the server and upload again. Overwriting is not enough.

 

RL

Answer 7

Thanks, RL!

I had added default values on a couple of my parameters, after initial deployment. The defaults were working great in IDE, but not on report server. Deleting the report from the server, and then redeploying it solved my issue.

 

Answer 8

I realize your post was a year ago and no real answer ever became of this.  I just had the same problem where one of my three parameters would not show the values selected if I chose "Select All" from each one of them.  The "offending" parameter had a long list of two fields from the database concatenated to form the label values.  Ex. Building / Building Description.    If I had a list of about 500 and Select All of them the values selected did not appear/show in the parameter.  If I changed my underlying query and added "Select top(5)" instead, the functionality now works as one would have expected.

Looks like there is a limitation here that creates an undesired effect or a bug in SSRS.  You decide.

Answer 9

thanks CK,

it really helped me. :)

-RJ