In SQL Server Reporting Services, Filters are similar to SQL WHERE Clause. SSRS Filters are used to restrict the Records displayed by the SSRS Reports.
SSRS supports two types of Filters: Filters at Tablix Level and Filters at Dataset Level. In this article we will show you the steps involved, to apply filters at Dataset Level in SSRS (SQL Server Reporting Services) with an example.
TIP: If you want to apply the filters on multiple reports then, you can apply filters at shared dataset. It will be applicable to all the tables using that Dataset.
We are going to use the below-shown report to explain, filtering the data at Dataset 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 Dataset Level in SSRS
Select the Dataset from the Report data tab of the report and Right-click on it will open the context menu. Please select the Dataset Properties.. option from it
Once we click on the Dataset Properties.. option, a new Dataset 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 Datatype. 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 [Color] is Black so, Please select the [Color] as Expression and Operator as Like and Value as Black.
Click the Ok button to finish configuring Filters at Dataset Level. Let us Preview the Report
If you observe the above screenshot, it is displaying the 224 pages of data because remaining data is not satisfying the Filter Condition (their colour is not Black).
Let us add one more filter at Dataset level, for better understanding. Here, We are going to display the records whose [Sales Amount] is between 600 and 1000 so, Please select the [Sales Amount] as Expression and Operator as Between and Value as 600 and 1000.
Here [Sales Amount] is Float value so, We have to convert both 600 and 1000 to a 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
TIP: If you want to delete the unnecessary filter, Please select the Filter and click on the Delete button.
Click the Ok button to finish configuring Filters at Dataset Level. Let us Preview the Report
If you observe the above screenshot, it is displaying the 10 pages of data because remaining data is not satisfying the 2 Filter Conditions.
Thank You for Visiting Our Blog
