Reporting Services – Shared Datasets

Shared datasets which were introduced in Reporting Service 2008 R2 allow a dataset to be shared across reports improving performance and avoiding the typical duplications of common querys and joins you find in most reporting services projects. Like most things in Reporting Services they are pretty easy to use.

The following is an example of how to use shared datasets with the AdventureWorks database. A shared dataset is populated from the ProductSubCategory table which can then be resued across reports avoiding constants querying and code duplication of data that changes infrequently.

In solution explorer right click on shared dataset to add a new shared dataset as shown

In my example I created a simple product list report and populated it’s dataset using the following sql

SELECT   ProductID, Name, ProductNumber, MakeFlag, Color, SafetyStockLevel, ProductSubcategoryID  FROM         Production.Product WHERE     (ProductSubcategoryID IS NOT NULL)

I now want to display the product sub category in the report which I could do by joining to the table SubCategory table however I can use the shared dataset by adding it to my report as shown

and  adding a textbox with the following expression which uses the SubCategoryID from the product dataset to lookup the SubCategory from the shared dataset.

Hopefully this simple example highlights the advantages of using shared datasets in reporting services.