none
Make MultiValue Parameter Dynamically Enable/ Disable

    General discussion

  • Hi,

    I have two MultiValue  parameters in SSRS report. Both the parameters are independant of each other.  If I choose value from first parameter, Second parameter should be disabled dynamically. If I choose  value from second parameter, First parameter should be disabled dynamically.

    Can this be achieved in SSRS ?

    Is there a workaround to achieve the above scenario ?

    Regards

    G. R. Venkatesh


    • Edited by Venkatesh G R Wednesday, April 23, 2014 6:30 PM I am new to SSRS. How do I make Multivalued parameter as NULL. SSRS doesn't allow to do so.
    Wednesday, April 23, 2014 10:05 AM

All replies

  • Hi Venkatesh,

    From users experience perspective this is not a good functionality you want to implement. Though this is not possible, this  can be achieved in different manner. This is not required in SSRS. With this functionality, once user selects 1 parameter, the other will be disabled. And if user wants to select 2nd parameter then he has to reload the report as it is disabled now.

    I hope, with this functionality, you want to disallow the users from selecting the other parameter. If this is true then you can make both parameters as Allow Nulls. When user selects parameter 1 then other one he can set to null and the other way.

    Thanks
    Shenoy

    Wednesday, April 23, 2014 11:41 AM
  • Using nulls is one way. Another way is to set a default value say NotApplicable. Just set below expression for default value

    IIF(Len(Parameters!SecondParameter.Value) > 0,"NotApplicable",Nothing)

    Similarly for the other parameter

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, April 23, 2014 11:51 AM
  • Using nulls is one way. Another way is to set a default value say NotApplicable. Just set below expression for default value

    IIF(Len(Parameters!SecondParameter.Value) > 0,"NotApplicable",Nothing)

    Similarly for the other parameter

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Hi Visakh,

    I am new to SSRS.

    Could you please share a workaround sample for my scenario

    Wednesday, April 23, 2014 6:36 PM
  • Hi Venkatesh,

    From users experience perspective this is not a good functionality you want to implement. Though this is not possible, this  can be achieved in different manner. This is not required in SSRS. With this functionality, once user selects 1 parameter, the other will be disabled. And if user wants to select 2nd parameter then he has to reload the report as it is disabled now.

    I hope, with this functionality, you want to disallow the users from selecting the other parameter. If this is true then you can make both parameters as Allow Nulls. When user selects parameter 1 then other one he can set to null and the other way.

    Thanks
    Shenoy

     

    Hi Shenoy,

    I am new to SSRS. How do I make Multivalued parameter as NULL. SSRS doesn't allow to do so.

    Wednesday, April 23, 2014 6:37 PM
  • Multivalue parameters cannot be set to allow nulls. They can allow blank values though. Note that neither suggestion disables the other parameter when a value is selected in another. Instead they allow the report to be run without an explicit value for the other parameter. This is because there is no way to actually disable the parameter.

    With Shenoy's suggested (modified to use empty value) or Visakh's suggestion, you will need to be certain to handle the "blank" result appropriately. For instance, if used in a SQL query:

    WHERE (Column1 IN (@MultiValueParam1) OR @MultiValueParam1='')

    Or for Visakh suggestion:

    WHERE (Column1 IN (@MultiValueParam1) OR @MultiValueParam1='NotApplicable')

    My preference would be for Visakh's solution because your original ask to disable the other parameter implies the need to provide visual feedback to the report user that they should not also set the other parameter.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Wednesday, April 23, 2014 7:45 PM
  • Using nulls is one way. Another way is to set a default value say NotApplicable. Just set below expression for default value

    IIF(Len(Parameters!SecondParameter.Value) > 0,"NotApplicable",Nothing)

    Similarly for the other parameter

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Hi Visakh,

    I am new to SSRS.

    Could you please share a workaround sample for my scenario

    I tried to implement the expression for Multivalue  parameter1 default value, The parameter1 has below expression

    =IIF(Len(Parameters!SecondParameter.Value) > 0,"NotApplicable",Nothing)

    and Parameter2 has below expression

    =IIF(Len(Parameters!FirstParameter.Value) > 0,"NotApplicable",Nothing)

    I get a error saying "The expression references the parameter 'parameter2' which doesn not exist in the parameters collection. Letters in the names of parameters must use correct case"

    Thursday, April 24, 2014 8:28 AM
  • Reporting sees the parameters in the order listed so Parameter1 doesn't know about Parameter2. Parameter2 does know about Parameter1 though. You could set the default values to a short message that instructs the user:

    "<Set Only 1 Parameter>" or "<Optional>"

    not ideal but...

    So, what exactly are the rules. Can the report user set both? Would that matter? 

     

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Thursday, April 24, 2014 6:35 PM
  • Reporting sees the parameters in the order listed so Parameter1 doesn't know about Parameter2. Parameter2 does know about Parameter1 though. You could set the default values to a short message that instructs the user:

    "<Set Only 1 Parameter>" or "<Optional>"

    not ideal but...

    So, what exactly are the rules. Can the report user set both? Would that matter? 

     

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Problem: Both parameters in the report are mandatory. This is because default value is not set.

    Report user requirement:The report user should be able to set both the parameters. However, report user should have option to set only one parameter at a time. When report user set parameter1, parameter2 should be disabled and all the values of parameter2 should be passed internally in the SQL query to get desired report output. When report user set parameter 2, parameter1 should be disabled and all the values of parameter1 should be passed internally in the SQL query
    to get desired report output.

    I have set default values to both the parameters to make parameter1 and parameter2 optional. However, I would like to provide better visual feedback to report user.

    Friday, April 25, 2014 1:20 AM
  • Hi Venkatesh,

    For better visual experience for users, you can follow below points.
    Set default values for both the parameters as one of the following "Disable" / "All"

    Change the query accordingly as
    WHERE (Column1 IN (@MultiValueParam1) OR @MultiValueParam1='Disable')

    WHERE (Column2 IN (@MultiValueParam2) OR @MultiValueParam2='Disable')

    Yes, for multivalue parameter, we can not allow NULL instead blank.

    Friday, April 25, 2014 6:07 AM
  • Shenoy's solution may work for you. There may be some aspects of it that vary from your intended. For instance, "OR @MultiValueParam1='Disable'" essentially eliminates the criteria on column1. If your parameter's allowed values are not all possible values of column 1 then you do not want to eliminate the criteria on that column, you simply want to expand it to include all allowed values. The same applies to param2 and column2.

    If the latter is the case then you need to get more creative, You might add an internal parameter configured like param1 (and another for param2) except that both the allowed and default values are set the same, with all allowed values (probably have a dataset for this). Your SQL might look like:

    WHERE Column1 IN (CASE WHEN @MultiValueParam1='Disable' THEN @MultiValueParam1i ELSE @MultiValueParam1 END)
    Another simple approach would be to set both params as I described for the internal parameter, so all allowed values are selected. Users then would deselect the ones they don't want. Not the best user experience but it would meet your requirements.



    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Friday, April 25, 2014 3:27 PM