none
Filter Dataset based on a boolean value

    Question

  • Hi,

    I have implemented a multivalued filter in SSRS,which works fine . Now I am trying to set up a second filter such that based on a boolean condition i would like to filter the dataset based on  a series of values.

    The boolean parameter is accomplished as :


    If the user selects "True" the dataset field "Location" has to be filtered based on 3 values "NY,NJ,DC" . If False is selected no filter will be applied on the dataset . The Parameter name is 'Param_Loc' and of datatype 'Integer' .

    Can you help me with the Filter expression i have to use here : 

     

    Looking forward to hearing from you .

    Thanks,

    Grigory

    Friday, March 07, 2014 7:27 AM

Answers

  • I think you need the below expressions for filter

    Expression
    -------------
    Fields!Location.Value
    
    Operator
    ------------
    In
    
    Value
    -------------
    =IIF(parameters!Param_Loc.Value = 1,Parameters!Location.Value,Fields!Location.Value)



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

    Friday, March 07, 2014 9:04 AM
  • Hi Mendel,

    Try this once

    Go to Filters in dataset properties-> then in the filter expression do the following stuff

    Expression =Select the value from the dataset,In ur  case it is "Location"

    Select Type = "Text"

    Operator = "in"

    Value = IIF(Parameters!Param_Loc.Value=True,"NY,NJ,DC",Fields!Location.Value)

    In the above IIF condition what I am doing is passing the filter values if condition is True i.e if Param_Loc is selected .If Param_Loc value is not selected i.e False then u don't want to apply any filter right.So I am passing all the dataset values in else condition.since 1=1 is universal Truth.

    Please let me know if any issues and vote this answer if it helps u.


    Kishore Babu K

    Friday, March 07, 2014 9:16 AM

All replies

  • Location in('NY','NJ','DC') or @param_loc = 0

    JS

    • Proposed as answer by JS PADOAN Friday, March 07, 2014 8:14 AM
    • Unproposed as answer by Grigory Mendel Friday, March 07, 2014 8:55 AM
    Friday, March 07, 2014 8:14 AM
  • Location in('NY','NJ','DC') or @param_loc = 0

    JS

    That is not the behaviour i would like to implement . Depending on the selection of the drop down the filter should act . By what you have said ,the data set will always be filtered by ('NY','NJ','DC') . If the user selects 'False' I would not want the filter to be implemented . 

    Regards,

    Grigory

    Friday, March 07, 2014 8:55 AM
  • I think you need the below expressions for filter

    Expression
    -------------
    Fields!Location.Value
    
    Operator
    ------------
    In
    
    Value
    -------------
    =IIF(parameters!Param_Loc.Value = 1,Parameters!Location.Value,Fields!Location.Value)



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

    Friday, March 07, 2014 9:04 AM
  • Hi Mendel,

    Try this once

    Go to Filters in dataset properties-> then in the filter expression do the following stuff

    Expression =Select the value from the dataset,In ur  case it is "Location"

    Select Type = "Text"

    Operator = "in"

    Value = IIF(Parameters!Param_Loc.Value=True,"NY,NJ,DC",Fields!Location.Value)

    In the above IIF condition what I am doing is passing the filter values if condition is True i.e if Param_Loc is selected .If Param_Loc value is not selected i.e False then u don't want to apply any filter right.So I am passing all the dataset values in else condition.since 1=1 is universal Truth.

    Please let me know if any issues and vote this answer if it helps u.


    Kishore Babu K

    Friday, March 07, 2014 9:16 AM
  • Hi Mendel,

    Try this once

    Go to Filters in dataset properties-> then in the filter expression do the following stuff

    Expression =Select the value from the dataset,In ur  case it is "Location"

    Select Type = "Text"

    Operator = "in"

    Value = IIF(Parameters!Param_Loc.Value=True,"NY,NJ,DC",Fields!Location.Value)

    In the above IIF condition what I am doing is passing the filter values if condition is True i.e if Param_Loc is selected .If Param_Loc value is not selected i.e False then u don't want to apply any filter right.So I am passing all the dataset values in else condition.since 1=1 is universal Truth.

    Please let me know if any issues and vote this answer if it helps u.


    Kishore Babu K

    Thanks Kishore and Visakh . Your inputs were really helpful . Kudos to you guys .

    There was a slight issue with the expression  . So i modified it as :

    IIF(Parameters!Param_Loc.Value="1",Split("NY,NJ,DC",","),Fields!Location.Value)

    Thanks a lot,

    Grigory

    Friday, March 07, 2014 10:43 AM