This blog is part of my series Making Data Tell a Story With SSRS Properties.
Property: Tablix Filters
The purpose of this property is to allow you to filter the results of a dataset shown in a tablix.
To access the property, right-click a row or column group in the tablix and select Tablix Properties. Select Filters. The options are Expression, Operator, and Value.
Expression – a field from the data set. You can also select the fx button to write an expression.
Operator – the comparison operator. Options are =, <>, Like, >, >=, <, <=, Top N, Bottom N, Top %, Bottom %, In, Between.
Value – the value you want to compare the expression to. This can be a single value, or you can select the fx button to write an expression.
Example: I have a report that shows sales per year by sales territory. I want to filter it so only orders placed after 2006 show in totals.
I add the Expression [OrderYear], I set the Operator to >, and I set the Value to 2006.
When run, the report only shows totals for orders placed in 2007 and after.
There are many ways to filter the data that will be shown on a report. You can filter the T-SQL, through the use of a WHERE clause. You can filter the dataset. You can filter a specific item. Why choose the latter? Two main use cases come to mind. You may have a stored procedure you cannot modify that returns more data than you need. You may also have one dataset for multiple report items, and you want to show different sets of data in each item.