Filters and Visibility

Filters and Visibility are two ways of filtering and hiding data you don’t need to show to the end user. Both are used in separate situations.

Visibility is a tab in any row, column, or tablix. It functions as, if the expression you give it evaluates to true, it hides that region.

Filters are used similarly, but with an Expression, which can be simply a value or more, select its datatype, its operator to filter with, and a value. Certain operators change the way the value will behave (BETWEEN gives you two values, obviously, to filter against)

Detailed below are some examples:

Operators that can be used as filter operators in SSRS are as follows, and most are self explanatory. (Note: Make sure your expression type and the value type match)

·   =

·   <>      

o   Does not equal.

·   LIKE

o   The same as the way it behaves in SQL in a WHERE clause.

o   *east

o   %o%th*

·   >, >=, < and <=

o   Works with dates, as well as numbers.

·   TOP N and BOTTOM N

o   As per the SQL usage with SELECT.

·   TOP % and BOTTOM %

o   As per the SQL usage with SELECT.

·   IN

o   The same as the way it behaves in SQL in a WHERE clause

o   Used to filter multiple values as an =.

·   BETWEEN

o   The same as the way it behaves in SQL in a WHERE clause

And finally, here are some examples I’ve used.

Filter Table Based on a Value(s)

1         Right click the Top left tab of the table and click Tablix Properties. Click on the Filters tab. In here you can filter your results based on expressions, and it’s really open ended, for example, I needed to filter my results based on a start and end date the customer provided. So with those are date/time parameters, I did this

a.       Expression: createdDate

b.      Operator: Between

c.       Value: =Iif(IsNothing(Parameters!startDateRange.Value), CDate("12/01/2000"), Parameters!startDateRange.Value)

d.      =Iif(IsNothing(Parameters!endDateRange.Value), Now(), Code.ConvertEndDate(Parameters!endDateRange.Value))

e.      Since createdDate specified exactly what I needed, I got the filter based off of whenever the transactions changed, which is exactly what the report was wanting to show.

Setting a Group/Row/Table’s Visibility Based on an Expression

1         Right click on the group/row/tablix and go to its specific properties. Click on the Visibility tab and select the radio button with “Show or Hide based on an expression: “. In here, put the expression that, if true, will hide the table/group/row.

2         =Fields!name.Value <> Previous(Fields!name.Value, "DataSet1") And Count(Fields!TurnTime.Value) = Previous(Count(Fields!TurnTime.Value), "DataSet1")

3         This hides extra rows if they would contain the same value. In addition, this was done over a count of appointmentId’s, grouped by Door #. It was very difficult to find the correct filter/expression to use as there were many ways to possibly get it to work.