Avoid Using Dataset Filters in SSRS

ByHariharan Rajendran

Avoid Using Dataset Filters in SSRS

If you are Microsoft SQL Server Report Developer, then you have a chance to notice the dataset properties as it is the place where we supply the data subsets to the report.

Let us understand the filters in SSRS first and analyse why dataset filters should be avoided.

We have three different main places to filter the data in SSRS

  1. T-SQL Script
  2. Dataset Filters
  3. Run-time Filter using Parameters

Filter in T-SQL Script

When we understand the business requirements, need to categories what are the filters static and dynamic.

Always use the static filters in T-SQL script. Dynamic filters can be done through parameters but still need to add the filter in T-SQL Script with parameters.

For example,

If you want to filter country values as the US on your country table.

SELECT State, City, Country FROM dbo.COUNTRY

WHERE Country=’US’

Note: In this case, we are restricting data in database server itself.

Dataset Filters

You can filter in SSRS report dataset.

Go to Dataset -> Filters

RD

Note: In this case, we are taking all the data to report server and filtering the data in the report.

Run-time filter using Parameter

It is a special kind of filter because this will be the requirement from business in most of the cases.

First 2 filters won’t be visible to end users but this filter will be visible and user needs to filter the value while running the report.

This can be achieved by adding the parameter in T-SQL script,

SELECT State, City, Country FROM dbo.COUNTRY

WHERE Country=@Country

RD2

Why should we avoid dataset filters?

The main reason is to improve the performance of the report.

In case 1 (Filter in  T-SQL Script), we are filtering the data in database layer itself. It means we are taking only required data to the reporting layer and showing the result. The performance of the report will be good as it is processing only fewer records.

In case 2 (Dataset filter), we are taking all the records from table database and filtering in the report server. It means report server has to make process and filter the data and show the result. We are giving burden to report server so it will affect the performance.

 

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Leave a Reply