locked
How to Change 2nd Parameter Value Based on 1st Parameter Selection RRS feed

  • Question

  • Hello everyone :)

    I have 2 parameters (non queried):

    Parameter 1 has 2 options: "A" and "B"

    I want the default value in Parameter 2 to change based on the selection in Parameter 1. I've tried setting the default value as the following:

    =IIF(Parameters.Parameter1.value="A",20,40)

    This sort of works in that when the user first chooses A or B from Parameter 1, Parameter 2 correctly changes to 20 or 40 respectively. But it only works the first time, when the user changes their selection say from A to B then Parameter 2 does not update.

    I basically need Parameter 2 to change if the user has changed Parameter 1, but they can over type with a new value, just when they make a selection in Parameter 1 I want a default suggestion to populate in Parameter 2.

    Many thanks to any help in advance!

    Tuesday, February 25, 2014 11:55 AM

Answers

  • Hi JJW14,

    As per my understanding, if the default selection of Parameter2 can be always automatically changed when changing the selection of the Parameter1, then the Parameter2 cannot be changed to another value when the Parameter1 value has select. Similarly, if we enable the user to amend the value of Parameter2, then the default selection of Parameter2 cannot be always automatically changed when changing the selection of the Parameter1. So, maybe you should create two report to separate achieve the requirements.

    Thanks,
    Katherine xiong


    Katherine Xiong
    TechNet Community Support

    Thursday, February 27, 2014 2:35 AM

All replies

  • Hi,

    Why do you need to get Parameter2 as a parameter ? you can use a dataset with a query Like :

    Select 
    
    Case When @parameter1 = 'A' then 20 
    
    Else 40 end 
    
    END 
    AS Param2

    Sts


    Dj's | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    • Proposed as answer by Katherine Xiong Wednesday, February 26, 2014 6:54 AM
    Tuesday, February 25, 2014 5:32 PM
  • Hi JJW14,

    From your description, the default selection of dependent parameter is not always automatically changed when changing the selection of the “parent” parameter. Based on my research, this is by design and we can see it on the link below:
    https://connect.microsoft.com/SQLServer/feedback/details/268032/

    From Microsoft: As described, this is not a bug. We do not re-evaluate the default value for a subsequent parameter *unless* the selected value is no longer in the valid values list. We do not know whether the current value was specifically requested by the user or it is there because of the default. You could make a case to have control over this behavior through some sort of property but it is currently working as designed.

    Besides, the suggestion posted by Sts is a good workaround, you can refer to it. Please let me know if it doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Wednesday, February 26, 2014 6:56 AM
  • Thanks for your replies

    I basically have a report that shows Sales, and have a filter that works two ways.

    Parameter 1 Available Values: "Less Than" and "Greater Than"

    Parameter 2 will be a user entered Quantity

    Then it will filter on Sales that are either Less than the Quantity or Greater than the Quantity. I have the filter working fine, the issue is with the default value on Parameter 2.

    I wanted it so that Parameter 2 populates with a Default Quantity  of 20 if "Less Than" is selected or 40 if "Greater Than" is selected, but still allowing the user to amend this quantity if they so wish - this is why I need it as a parameter because I want the quantity to be amendable.

    Problem is that usually the user will select "Less Than" and then Parameter 2 correctly populates with 20 and runs the report. They then change the selection  to "Greater Than" but Parameter 2 does not change to 40.

    I may have to resort to creating 2 separate reports

    Wednesday, February 26, 2014 1:18 PM
  • Hi JJW14,

    As per my understanding, if the default selection of Parameter2 can be always automatically changed when changing the selection of the Parameter1, then the Parameter2 cannot be changed to another value when the Parameter1 value has select. Similarly, if we enable the user to amend the value of Parameter2, then the default selection of Parameter2 cannot be always automatically changed when changing the selection of the Parameter1. So, maybe you should create two report to separate achieve the requirements.

    Thanks,
    Katherine xiong


    Katherine Xiong
    TechNet Community Support

    Thursday, February 27, 2014 2:35 AM