SSRS 2008 R2 - Default Multi Value Parameters are Not Selected
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
February 21st, 2011 4:52pm

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 FuChallen 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.
Free Windows Admin Tool Kit Click here and download it now
February 23rd, 2011 3:17am

hi russ d, please check report->report parameters->default values->select none. mark as asnwer if this post solves your issue.
February 23rd, 2011 5:42am

hi russ d, please check report->report parameters->default values->select none. mark as asnwer if this post solves your issue.
Free Windows Admin Tool Kit Click here and download it now
February 23rd, 2011 5:42am

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
February 23rd, 2011 7:42am

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
Free Windows Admin Tool Kit Click here and download it now
February 23rd, 2011 7:42am

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
April 27th, 2011 1:12pm

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
Free Windows Admin Tool Kit Click here and download it now
April 27th, 2011 1:12pm

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.
May 12th, 2011 4:55pm

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
Free Windows Admin Tool Kit Click here and download it now
August 15th, 2011 11:27am

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
August 15th, 2011 11:27am

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.
Free Windows Admin Tool Kit Click here and download it now
November 4th, 2011 11:54am

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.
November 4th, 2011 11:54am

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.
Free Windows Admin Tool Kit Click here and download it now
February 10th, 2012 12:29pm

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.
February 10th, 2012 12:29pm

thanks CK, it really helped me. :) -RJ
Free Windows Admin Tool Kit Click here and download it now
May 9th, 2012 1:44pm

thanks CK, it really helped me. :) -RJ
May 9th, 2012 1:44pm

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. Thanx a lot! That was the main cause of the issue! And the worst thing it was not obvious! Thank you, once again. Your investigation really helped to solve the problem!!
Free Windows Admin Tool Kit Click here and download it now
June 8th, 2012 10:55am

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. Thanx a lot! That was the main cause of the issue! And the worst thing it was not obvious! Thank you, once again. Your investigation really helped to solve the problem!!
June 8th, 2012 10:55am

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. Thanx a lot! That was the main cause of the issue! And the worst thing it was not obvious! Thank you, once again. Your investigation really helped to solve the problem!!
Free Windows Admin Tool Kit Click here and download it now
June 8th, 2012 11:11am

i too had the same problem.. it comes when you change the default value later on after deploying.. i tried ovewritting or reseting but delete the report and redeploying solved. this is an annoying bug.Tamilselvan
July 24th, 2012 2:42am

Thank you CK. This was a huge help.Zak Hanano
Free Windows Admin Tool Kit Click here and download it now
July 25th, 2012 11:55am

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. Thanx a lot! That was the main cause of the issue! And the worst thing it was not obvious! Thank you, once again. Your investigation really helped to solve the problem!! This solution solved my problem
August 8th, 2012 5:28am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics