Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Dataset and tablix filtering in SQL Server Reporting Services


By:   |   Last Updated: 2012-01-27   |   Comments (7)   |   Related Tips: > 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:

SQL Server Reporting Services Dataset Properties

Once the Dataset Properties window opens choose Filters:

SQL Server Reporting Services Dataset Filtering

Select one of the fields in the dataset:

SQL Server Reporting Services Dataset Filtering

Select the type of filtering you want:

SQL Server Reporting Services Dataset Filtering

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:

Setting the DatasetName property of the Tablix in SQL Server Reporting Services

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:

Accessing the properties of the Tablix in SQL Server Reporting Services

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
Site Information queried in SQL Server Management Studio

Weather conditions
Weather conditions queried in SQL Server Management Studio

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:

Sample Site filtering in SQL Server Reporting Services

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

Final Report Rendering
Next Steps


Last Updated: 2012-01-27


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, December 24, 2014 - 11:28:19 AM - Philip Grooms Back To Top

I am trying to use the National Weather Service to return the weather data for my location and present it on an SSRS report like you have done above however I am at a lost on what to do.


Wednesday, December 24, 2014 - 11:17:32 AM - Philip Grooms Back To Top

I am trying to display weather information on an SSRS report used as a dashboard. I cannot seem to get the current weather results returned.


Wednesday, December 24, 2014 - 9:52:53 AM - Tim Cullen Back To Top

Phillip:

 

Could you be a little more specific?

 

Tim


Wednesday, December 24, 2014 - 8:38:57 AM - Philip Grooms Back To Top

This is exactly what I am fighting with right now. Trying to get weather for my SSRS Report. Can you PLEASE help me????


Tuesday, January 31, 2012 - 8:36:45 AM - Tim Cullen Back To Top

I'll sit down this evening and work with the situation described above.  Thanks for presenting it!


Monday, January 30, 2012 - 4:43:16 AM - Neuro Back To Top

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

 


Friday, January 27, 2012 - 12:27:33 PM - Neuro Back To Top



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)



Learn more about SQL Server tools