Dataset and tablix filtering in SQL Server Reporting Services

By: Tim Cullen   |   Comments (7)   |   Related: > Reporting Services Filtering

Problem

I'm creating a SQL Server Reporting Services (SSRS) report based on datasets that I have no control over and they have more data in the result set than what is required for the report. Is there a way that I can filter the datasets inside the SSRS report itself?  Check out this tip to learn more.

Solution

You may run into situations when a dataset provides more information than what is needed for a report. Thankfully, data can be filtered in a few locations in a report. The first is at the dataset level. In order to filter the dataset right-click on the dataset and Choose Dataset Properties:

Once the Dataset Properties window opens choose Filters:

Select one of the fields in the dataset:

Select the type of filtering you want:

Finally, enter the criterion for the filter and click OK.

Another location where a dataset can be filtered is in a table, or tablix. The interface for setting up filtering at the tablix level is similar to setting it up for the dataset. The only differences are:

1. The DataSetName property of the tablix should be set to the relevant dataset:

2. When you click inside a tablix an outer rectangle will appear with a square in the upper left-hand corner. Right-click the square and choose Tablix Properties:

For the example I am building a report that will display weather observations for selected sites around the Atlanta area; however, I want each site to be shown separately (the data was collected from the National Weather Service). I have two datasets available-one that has information about the collection sites and another that has the actual weather conditions for the sites. Below are samples of each dataset:

Site Information

Weather conditions

Notice in each dataset that there are multiple sites. In general I will create two tablixes per site: one to show information about the site and the other to show the observations for the site. Below is an example of the filters where I'm filtering on a particular Site ID:

Below is a sample of the final report rendering, showing that each area has been filtered by Site ID:

Next Steps

• There are always a number of ways to create reports and other objects; this is just one example of how it can be done.
• Thanks to the National Weather Service for the public use of their servers and data. Read the disclaimer for additional information.
• Find out more about Filter Equation Examples.
• Read about the Tablix Data Region in SQL Server Reporting Services.
• Read more about Adding Dataset Filters, Data Region Filters, and Group Filters.
• Read more Reporting Services Development tips
• Check out the SQL Server Reporting Services Tutorial

About the author

Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

View all my tips