none
Enabling SSRS parameter based on the value of another parameter

    Question

  • Hi All,

    We have a report in SSRS 2008. It has about 10 parameters in it - Start Date, End Date, OrgID, LocID etc.. We need to add one more parameter to it AccID. It should be added in such a way that if OrgID and LocID are selected, AccID should be disabled and vice versa. The user should be given an option to decide which one should be selected.

    We googled a lot and found that it is not possible to change the visibility of a paramete dynamically.  Just asking again - If we add a new parameter - Show OrgID and LocId OR AccID - a boolean value - is there any work around to dynamically change the visibility of the three parameter?

    If that is not possible, is there any way to handle this in the stored procedure side? We need to display this report in the report manager so changing the visibility of the parameter through asp.net is not an option for us.

    Any help will be greatly appreciated. We are in a fix right now due to this.

    Regards,
    L

     

     

    Monday, November 21, 2011 5:32 PM

Answers

  • Hi Kannadelavu,

    Thanks for the question and _JW’s suggestion. As had mentioned, by design, we couldn’t control the parameter’s visibility by using expression. Please consider the feasibility of this work-around: using the parameter @AccID to display the other parameter’s names, although we couldn’t use @AccID to control other parameters’ visibility, we can use @AccID to control other parameters’ filter effect.

    For instance, I selected OrgID in the @AccID, then adding one filter on the dataset, choose the filed OrgID in the Expression Drop-down list, choose = in the Operator drop-down list, click the fx button of Value, type in it with:     

    =Iif(instr(Join(Parameters!AccID.Value,","),"0"),Fields!OrgID.Value,Parameters!AccID.Value)

    Based on the expression, if user didn’t select OrigID in the @AccID, the @OrgID will have no effect to filter the data.

    Best Regards,
    Sharp Wang


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    Tuesday, November 29, 2011 2:57 AM
    Moderator

All replies

  • I don't believe there is a way to toggle a parameters visiblity. But one option you might have is to set a default value for AcctID based on weather or not the values selected for OrigID & LocID. Writing your queries as expressions will give you some flexibility on what and the way parameter values are passed into your datasets.

     

    -JW

    Tuesday, November 22, 2011 6:36 PM
  • Hi,

    Thanks for the reply.

    In our case, AccID is not related to OrgID and LocID so I cannot do as you suggested.

    Wednesday, November 23, 2011 9:32 AM
  • Hi Kannadelavu,

    Thanks for the question and _JW’s suggestion. As had mentioned, by design, we couldn’t control the parameter’s visibility by using expression. Please consider the feasibility of this work-around: using the parameter @AccID to display the other parameter’s names, although we couldn’t use @AccID to control other parameters’ visibility, we can use @AccID to control other parameters’ filter effect.

    For instance, I selected OrgID in the @AccID, then adding one filter on the dataset, choose the filed OrgID in the Expression Drop-down list, choose = in the Operator drop-down list, click the fx button of Value, type in it with:     

    =Iif(instr(Join(Parameters!AccID.Value,","),"0"),Fields!OrgID.Value,Parameters!AccID.Value)

    Based on the expression, if user didn’t select OrigID in the @AccID, the @OrgID will have no effect to filter the data.

    Best Regards,
    Sharp Wang


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    Tuesday, November 29, 2011 2:57 AM
    Moderator