Dataset and tablix filtering in SQL Server Reporting Services

By: Tim Cullen | Read Comments (3) | Print Tim has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer. Related Tips: More

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

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Friday, January 27, 2012 - 12:27:33 PM - Neuro

Hi Tim since your post is about Tablix and filtering I think I cam across an SSRS bug, and I wondered if you were aware of it or came across it before to start off nice and simple here is the dataset Select 'ABC'[type1],'Inc'[type2],'Line1'[detail],1[value] union all Select 'ABC','Inc','Line2',1 union all Select 'ABC','Exc','Line1',1 union all Select 'ABC','Exc','Line2',1 union all Select 'ABC','Exc','Line1',1 union all Select 'ABC','Exc','Line2',1 union all Select 'DEF','Exc','Line1',1 union all Select 'DEF','Exc','Line2',1 union all Select 'DEF','Inc','Line1',1 using the dataset create 3 Tablix's (Matricies) with Type 2 as the row group and value as the data 1)in the first Matrix dont apply a filter of any kind , the total should be 9 2) in the second Matrix apply a filter on the Tablix , so that only Type1 ABC should appear , the total should be 6 okay so thats the boring one out of the way 3)Apply the Tablix filter for ABC again, now this time add a filter on the group for detail where the detail = Line1 you would expect that you would only get Inc = 1 and Exc = 2, but in fact you get Exc = 4 and Inc = 2 Strange isnt it? (using SSRS 2008 R2)

Monday, January 30, 2012 - 4:43:16 AM - Neuro
Hi Tim If you can replicate the issue can you please vote on connect https://connect.microsoft.com/SQLServer/feedback/details/721513/ssrs-tablix-filtering

Tuesday, January 31, 2012 - 8:36:45 AM - Tim Cullen
I'll sit down this evening and work with the situation described above.  Thanks for presenting it!

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code