locked
Filtering with a Parameter RRS feed

  • Question

  • SSRS 2008 R2 BIDS

    I have a table that I want to filter based upon a parameter.

    For example
    if @param = 1 and field = 'Apple' then don't display
    if @param = 2 and filed = 'Car" then don't display

    Normally I would do this in my query but the query would be very hard to write in this case.

    How can I do this?

    Wednesday, August 10, 2011 4:47 PM

Answers

  • arunsinghit's solution should work, but be aware of the differences between using row visibility and filtering data.  When you make rows hidden then that data still exists for aggragate functions perfomed at parent group levels, and in SSRS you can filter at the dataset/table/or group (including the details group) level, and it is good to know the differences.  I'm not sure how complex your report is or if you have multiple Tablix's that use the same dataset or not, if it is simple 1 table report than it may not make a difference.

    If you want to completely exclude those rows than it really isn't too hard to implement in the sql query - assuming you're on sql server or other modern database system. otherwise you could implement a dataset filter.  The idea is the same.

    For tsql you could use this Case statment

    WHERE 1 = CASE WHEN @param = 1 AND field = 'Apple' THEN 1
     WHEN @param = 2 AND field = 'Car' THEN 1
     ELSE 0 END
    


    For the dataset filter use the same idea but with two expressions

    Exprssion1

    =SWITCH(Parameters!param.Value = 1 ANDALSO Fields!field.value = "Apple", 1, Parameters!param.Value = 2 ANDALSO Fields!field.Value = "Car", 1, TRUE, 0)
    

    Operator "="

    Value =1

     

    Hope that helps!

    • Proposed as answer by William Vaughn Wednesday, August 10, 2011 6:28 PM
    • Marked as answer by Scott_Hanebutt Wednesday, August 10, 2011 8:46 PM
    Wednesday, August 10, 2011 6:04 PM

All replies

  •  

    SELECT Table row and goto Property -> Hidden and write below expression

    =iif((Parameters!param.Value = 1

    and Fields!field .Value = "Apple") OR (Parameters!param.Value = 1 and Fields!field .Value = "Car") = "" ,TRUE , FALSE

    )

    Wednesday, August 10, 2011 5:12 PM
  • Hi Scott,

    try this,

    = IIf ( ( Parameters!Param.Value = 1 AND ( Fields!field .Value = "Apple" ) ) OR ( Parameters!Param.Value = 1 AND ( Fields!field .Value = "Car" ) ) = "" , TRUE , FALSE )


    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Wednesday, August 10, 2011 5:20 PM
  • arunsinghit's solution should work, but be aware of the differences between using row visibility and filtering data.  When you make rows hidden then that data still exists for aggragate functions perfomed at parent group levels, and in SSRS you can filter at the dataset/table/or group (including the details group) level, and it is good to know the differences.  I'm not sure how complex your report is or if you have multiple Tablix's that use the same dataset or not, if it is simple 1 table report than it may not make a difference.

    If you want to completely exclude those rows than it really isn't too hard to implement in the sql query - assuming you're on sql server or other modern database system. otherwise you could implement a dataset filter.  The idea is the same.

    For tsql you could use this Case statment

    WHERE 1 = CASE WHEN @param = 1 AND field = 'Apple' THEN 1
     WHEN @param = 2 AND field = 'Car' THEN 1
     ELSE 0 END
    


    For the dataset filter use the same idea but with two expressions

    Exprssion1

    =SWITCH(Parameters!param.Value = 1 ANDALSO Fields!field.value = "Apple", 1, Parameters!param.Value = 2 ANDALSO Fields!field.Value = "Car", 1, TRUE, 0)
    

    Operator "="

    Value =1

     

    Hope that helps!

    • Proposed as answer by William Vaughn Wednesday, August 10, 2011 6:28 PM
    • Marked as answer by Scott_Hanebutt Wednesday, August 10, 2011 8:46 PM
    Wednesday, August 10, 2011 6:04 PM
  • mattpage - your answer was perfect. Thank you!
    Wednesday, August 10, 2011 8:46 PM