none
Cascading parameters default values SSRS 2008

    Question

  • Hi everyone,

    I am facing the following problem: If I have two cascading report parameters:
    @Param1 with available values 1,2,3,4 and @Param2 with default values switch(@Param1.Value = 1, 1, @Param1.Value=2, 2.....)
    When the report is loading for the first time - the second parameter gets the default value correctly. However if I change the value of the first parameter @Param1, the value of the second does not change. How can I make the second parameter default value to change according to the value of the first everytime the value of the @Param1. I have also tried setting Advanced feature 'Always refresh', however this does not help.

    Any help will be very much appreciated.

    Maria

    Monday, April 27, 2009 11:41 AM

Answers

  • Hello Maria,

    After setting the default value of @param2 based on @param1, If you want the user again to select any value then you can follow the below process

    1. Set the default value of the @param2 to the following dataset

    SELECT CASE @Param1
    WHEN 1 then 1
    WHEN 2 then 2
    WHEN 3 then 3
    WHEN 4 then 4
    END

    2. have one more dataset with all the required values for showing the available Values for @param2

    Example:

    select 1 a
    union all
    select 2
    union all
    select 3
    union all
    select 4


    Now @param2 will have default value based @param1 and also it contains other value to select


    Chandra, http://www.ggktech.com
    • Edited by Chandra _M Monday, April 27, 2009 12:45 PM
    • Marked as answer by LadySQLBI Tuesday, April 28, 2009 9:41 AM
    Monday, April 27, 2009 12:23 PM

All replies

  • Hi,

    Default valu can be calculated only at the time of report generation for the first time.

    So to get the @param2 value based on @param1 you can create dataset for the second parameter with the following query.

    SELECT CASE @Param1  
    WHEN 1 then 1
    WHEN 2 then 2
    WHEN 3 then 3
    WHEN 4 then 4
    END

    Chandra, http://www.ggktech.com
    Monday, April 27, 2009 11:56 AM
  • Thanks a lot Chandra,

    this works ok but besides the default value for the second parameter I need to allow the user to select other values as well. So If I put the query in the available values for the second paramter then the default values is preselected and the user has no other values available.

    Have you got an idea how to solve this?

    Thank you

    Maria
    Monday, April 27, 2009 12:03 PM
  • Hello Maria,

    After setting the default value of @param2 based on @param1, If you want the user again to select any value then you can follow the below process

    1. Set the default value of the @param2 to the following dataset

    SELECT CASE @Param1
    WHEN 1 then 1
    WHEN 2 then 2
    WHEN 3 then 3
    WHEN 4 then 4
    END

    2. have one more dataset with all the required values for showing the available Values for @param2

    Example:

    select 1 a
    union all
    select 2
    union all
    select 3
    union all
    select 4


    Now @param2 will have default value based @param1 and also it contains other value to select


    Chandra, http://www.ggktech.com
    • Edited by Chandra _M Monday, April 27, 2009 12:45 PM
    • Marked as answer by LadySQLBI Tuesday, April 28, 2009 9:41 AM
    Monday, April 27, 2009 12:23 PM
  • Hi & Thanks again Chandra,

    I did as suggested - 2 datasets one for the default value and second for the available values (the second excludes the value from the first so that they do not get double times). However the second parameter does not get its values preselected when the first parameter is changed. I noticed that when I click the drop-down list of @Param2 the values are in correct order (for example if @Param1 = 1 then 1 appears first in the list. However the value 1 should be preselected and it is not).

    Am I missing something here?

    Thank you.

    Maria
    Monday, April 27, 2009 1:43 PM
  • Hi again,

    I managed to implement this. Chandra thanks a lot for the tips -  they were really helpful.

    Maria
    Tuesday, April 28, 2009 8:45 AM
  • Hi Maria,

    Its nice to here that you have managed to solve your problem.

    Can you post your implementaion process, So that it will be helpful for others who are having similar kind need
    Chandra, http://www.ggktech.com
    Tuesday, April 28, 2009 8:52 AM