locked
SSRS 2008 R2 - Default Multi Value Parameters are Not Selected RRS feed

  • 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

     

    • Edited by Russ D Monday, February 21, 2011 10:20 PM Incorrect symptom given in subject
    Monday, February 21, 2011 9:52 PM

Answers

  • 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.

     

    • Marked as answer by Challen Fu Friday, May 13, 2011 12:53 AM
    Thursday, May 12, 2011 8:55 PM
  • 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

    • Proposed as answer by matty13 Tuesday, June 25, 2013 10:28 AM
    • Marked as answer by Sri Harsha Kalavala Wednesday, July 16, 2014 11:30 AM
    Monday, August 15, 2011 3:27 PM
  • 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.

    • Marked as answer by Challen Fu Tuesday, March 1, 2011 1:36 PM
    Wednesday, February 23, 2011 8:17 AM

All replies

  • 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.

    • Marked as answer by Challen Fu Tuesday, March 1, 2011 1:36 PM
    Wednesday, February 23, 2011 8:17 AM
  • hi russ d,

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

    mark as asnwer if this post solves your issue.

     

     

    Wednesday, February 23, 2011 10:42 AM
  • 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
    Wednesday, February 23, 2011 12:42 PM
  • 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

    Wednesday, April 27, 2011 5:12 PM
  • 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.

     

    • Marked as answer by Challen Fu Friday, May 13, 2011 12:53 AM
    Thursday, May 12, 2011 8:55 PM
  • 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

    • Proposed as answer by matty13 Tuesday, June 25, 2013 10:28 AM
    • Marked as answer by Sri Harsha Kalavala Wednesday, July 16, 2014 11:30 AM
    Monday, August 15, 2011 3:27 PM
  • 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.

     

    Friday, November 4, 2011 3:54 PM
  • 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.

    Friday, February 10, 2012 5:29 PM
  • thanks CK,

    it really helped me. :)

    -RJ

    Wednesday, May 9, 2012 5:44 PM
  • 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!!

    • Proposed as answer by trapijgn01 Wednesday, August 8, 2012 9:14 AM
    Friday, June 8, 2012 2:55 PM
  • 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

    Tuesday, July 24, 2012 6:34 AM
  • Thank you CK. This was a huge help.

    Zak Hanano

    Wednesday, July 25, 2012 3:41 PM
  • 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

    Wednesday, August 8, 2012 9:15 AM
  • Also looks like the list of values needs to be distinct.  Dupes seemed to be ignored when default selections are pulled from a query.
    Friday, January 11, 2013 6:00 PM
  • Thanks rlaci - looks like a bug - I had to delete and re-deploy for the static/hard-coded default values to pre-select.
    Friday, January 18, 2013 5:34 AM
  • This is not a bug, it is an as designed behavior in SSRS. After the initial deployment of a report, many parameter aspects are cached externally to the report definition on the server and can only be managed via the SSRS management portal. This is why reuploading the report doesn't work, but deleting and reuploading does.

    To update the default parameters for the report after uploading an update:

    1. Go to the SSRS reports portal and make sure select the "Detail View" for the reports list grid
    2. Click next to the report name to view the dropdown list of available actions and select "Manage"
    3. Select "Parameters" from tab list on the left
    4. Modify your report parameter default behaviors
    5. Click "Apply"

    Just remember, once uploaded, many aspects of your report parameters are cached and any subsequent re-uploads of that report will ignore updated RDL parameters and use those cached server values. It also explains why sometimes things appear to work and sometimes not.


    • Proposed as answer by deaconb Friday, January 18, 2013 8:57 PM
    • Edited by deaconb Friday, January 18, 2013 8:59 PM Spelling Correction
    Friday, January 18, 2013 8:57 PM
  • @ckphua, THANK YOU, this was the answer I was looking for!

    Derek Gusoff


    Wednesday, June 5, 2013 6:19 PM
  • This was my case - even in Preview it still not display the "Default" value, but when deleted the report and then re-deployed worked OK. FYI: my SSRS is in Integrated Mode.
    Tuesday, June 25, 2013 10:29 AM
  • empty strings will also cause this along with NULLs.

    Derek Gusoff

    Thursday, June 27, 2013 3:20 PM
  • Does anyone know whether this can be done dynamically? When a report I have is pulled, there are several predefined characteristics that are to be filtered upon, but that set is defined in the database, i.e., a query pulls the full set and the user gets the pre-checked list that they can override if necessary. So checking default values in the rdl on the server or workstation isn't an option. Thanks, in hopes...
    • Edited by SAinCA Tuesday, September 3, 2013 6:11 PM
    Tuesday, September 3, 2013 6:10 PM
  • Thanks a lot ... This helped me solve the issue.
    Friday, July 3, 2015 9:34 AM
  • This worked for me as well.
    Tuesday, December 12, 2017 1:51 PM