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.
Further reading:
Jes Borland is a Premier Field Engineer - SQL Server for Microsoft, where she is a trusted technical adviser, deep technical support, and teacher for her customers. Her experience as a DBA, consultant, and 5-time Data Platform MVP allow her to help the business and the IT teams reach their goals. She has worked with SQL Server as a developer, report writer, DBA, and consultant. Her favorite topics are administration, automation, and performance. She frequently presents at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef.
