Tutorial Gateway

  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
  • MySQL

Filters at Dataset Level in SSRS

In SQL Server Reporting Services, Filters are similar to SQL WHERE Clause. SSRS Dataset level Filters are used to restrict the Records displayed by the SSRS Reports. 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.

SSRS supports two types of Filters: Filters at Tablix Level and Filters at Dataset Level.

TIP: If you want to use the SSRS filters on multiple reports, apply filters at the shared Dataset level. It will apply to all the tables using that Dataset. Please refer to SQL WHERE Clause article.

We use the below-shown report to explain, SSRS filtering the data at Dataset level. Please refer to Table Report to understand the Shared Data Source and Dataset we used for this SSRS report. If you observe the below screenshot, It was a regular report with Product Name, Color, Sales, and Tax Amount columns.

Filters at Dataset Level in SSRS 1

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

Filters at Dataset Level in SSRS 2

Once we click on the Dataset Properties.. option, a new Dataset Properties window will open. Select the Filters tab.

Filters at Dataset Level in SSRS 3

Click on the Add button will display the below properties

  • Expression: It allows to select the available Column from the Dataset, or create an Expression by clicking the fx button
  • Data Type: By default, Text selected as Datatype. Change as per the need.
  • Operator: Select the operator you want to use (Like, <, <= )
  • Value: Please give the value you want to check against the Expression. Please click on the fx button to write value Expression.

In this SSRS Filters at Dataset Level example, We are going to display the records whose [Color] is Black. So, select the [Color] as Expression and Operator as Like and Value as Black.

Filters at Dataset Level in SSRS 4

Click the Ok button to finish configuring Filters at Dataset Level in SSRS. Let us Preview the Report

Filtering data at Dataset Level in SSRS 2014 4

If you observe the above screenshot, it is displaying the 224 pages of data because the remaining data is not satisfying the Filter Condition (their color 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.

Filtering data at Dataset Level in SSRS 2014 5

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, 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

Filtering data at Dataset Level in SSRS 2014 6

If you observe the above screenshot, it is displaying the 10 pages of data because the remaining data is not satisfying the 2 Filter Conditions.

Filed Under: SSRS

  • Learn SSRS in 28 Days
  • Create New SSRS Project
  • Create a New SSRS Report
  • SSRS Query Designer
  • SSRS Shared Data Source
  • SSRS Shared Dataset
  • SSRS Embedded Dataset
  • SSRS Embedded Data Source
  • SSRS Lookup – combine datasets
  • SSRS Report Items
  • SSRS Table Report
  • SSRS Table using report wizard
  • Format SSRS Table Report
  • SSRS Matrix Report
  • SSRS Matrix with report wizard
  • Display Image in SSRS Report
  • Add Rectangle to SSRS Report
  • Add TextBox to SSRS Report
  • Create a SSRS List Report
  • Grouping in SSRS Matrix Reports
  • Grouping in SSRS Table Reports
  • Create SSRS Report using Views
  • SSRS report with store procedure
  • Add Total and Subtotal to Report
  • Add Headers & Footers to Report
  • SSRS Global References
  • Export Report to PDF, XML, Excel, Word
  • Format SSRS Table Report
  • Format Textbox Fonts & Bg Color
  • Format Numbers in SSRS Report
  • Format SSRS Report Date, Time
  • Add Row Numbers to Report
  • Headers Visible While Scrolling
  • Repeat Headers on Each Page
  • SSRS Sorting
  • SSRS Interactive Sort
  • Filters at Dataset Level in SSRS
  • Filters at Tablix Level in SSRS
  • Page Breaks in SSRS Report
  • Custom Code in SSRS Report
  • SSRS Calculated Fields
  • SSRS Dashboard Reports
  • SSRS Document Map
  • SSRS Drill Through Reports
  • SSRS Drill Down Reports
  • SSRS Drill Down Matrix Report
  • SSRS Go To URL Action
  • SSRS Subreports
  • SSRS Report Parameters
  • SSRS Cascading Parameters
  • SSRS Multiple Parameters
  • SSRS Multi Value Parameter
  • SSRS Drop Down List Parameters
  • SSRS Charts
  • SSRS Area Chart
  • SSRS Bubble Chart
  • SSRS Column Chart
  • Format SSRS Column Chart
  • SSRS Data Bars
  • Data Bars in SSRS Matrix Reports
  • Format SSRS Data Bars
  • SSRS Funnel Chart
  • SSRS Linear Gauges
  • SSRS Radial Gauges
  • Format SSRS Gauges
  • SSRS Indicators
  • Format SSRS Indicators
  • SSRS Line Chart
  • SSRS Pie Chart
  • Format SSRS Pie Chart
  • SSRS Pyramid Chart
  • SSRS Range Chart
  • SSRS Radar Chart
  • SSRS Scatter Plot
  • SSRS Sparkline
  • SSRS Stacked Bar Chart
  • SSRS Configuration Manager
  • Change Report Manager Title
  • SSRS Deploying Reports in BIDS
  • Deploy using Report Manager
  • SSRS Report Manager Shared DataSource
  • SSRS Cached Reports
  • SSRS Linked Reports
  • SSRS Report Parts
  • SSRS Report Snapshot
  • SSRS Report History Snapshot
  • SSRS Report Subscription
  • SSRS Security
  • Upload File to Report Manager
  • Install SSRS Report builder
  • Report Builder – Create a Report
  • Report Builder – Create a Dataset
  • Report Builder- Create Bar chart
  • Report Builder – Create Pie Chart
  • Report Builder – Create report without wizard

Copyright © 2021ยท All Rights Reserved by Suresh.
About | Contact | Privacy Policy