In SQL Server Reporting Services, Filters are similar to SQL WHERE Clause. Filters is used to restrict the Records displayed by the Report. SSRS supports two types of Filters: Filters at Tablix Level and Filters at Dataset Level. In this article we will show you, How to filter the data at Table level (or tablix level) in SQL Server Reporting Services 2014 with example.
TIP: It is always better to apply filters at Tablix level only because it will only be applicable to this table only rather than all the tables using that Dataset.
We are going to use below shown report to explain, filtering the data at Table level. Please refer SSRS Table Report article to understand the Shared Data Source and Dataset we used for this report. If you observe the below screenshot, It was a normal report with Product Name, Color, Sales Amount and Tax Amount columns.
If you observe the above screenshot, it is displaying the 1373 pages of data.
Filters at Tablix Level in SSRS
In order to filter data at table level, or tablix level, Please Select the Top Left Corner of the report and Right click on it will open the context menu. Please select the Tablix Properties.. option from it
Once we click on the Tablix Properties.. option, a new Tablix Properties window will be opened. Please select the Filters tab as shown in below screenshot.
Click on the Add button will display the below properties
- Expression: It will allow you to select the available Column from the Dataset or we can create our own Expression by clicking the fx button
- Data Type: By default Text is selected as Data type. You can change as per your requirement.
- Operator: Please select the operator you want to use (Like, <, <= etc)
- Value: Please specify the value you want to check against the Expression. Please click on the fx button to write value Expression.
In this example, We are going to display the records whose [Sales Amount] is Greater than or Equal to 3500 so, Please select the [Sales Amount] as Expression and Operator as >= and Value as 3500.
Here [Sales Amount] is Float value so, We have to convert 3500 to Decimal value. If you know the conversion function you can directly write it. If not, Please click on the fx button and use the built-in functions
Click Ok button to finish writing the Value Expression.
Click Ok button to finish configuring Filters at Tablix Level. Let us Preview the Report
If you observe the above screenshot, it is displaying the 36 pages of data because remaining data is not satisfying the Filter Condition.
If you want to delete the unnecessary filter, Please select the Filter and click on the Delete button as shown in below screenshot
Thank You for Visiting Our Blog
