Wednesday, July 07, 2010

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.
=Lookup(Fields!ProductSubcategoryID.Value, Fields!ProductSubcategoryID.Value, Fields!Name.Value, "ProductSubCategory")

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









4 comments:

  1. Very useful information!!

    ReplyDelete
  2. Why would yoy not use a view or stored procedure if you want to share the same query?

    Shared data sets doesnot improve query performance they actually worsen it because it brings al the data first to report server and then filters it for example there is shared data set with 30 columns and user only wants 5 in the report but when you create report using report builder it will bring all 30 columns for whole range of data even if you want filtered information, so simply its just a marketing gimmic I dont see any use of them.

    ReplyDelete
    Replies
    1. I believe there are circumstances where they could be useful for example if you have many thousands of users running a set of reports which needs to join to a simple lookup type table. Rather than having to join to that small table for every reports you could use a shared dataset.

      Delete
  3. So it's been a couple of years since you wrote this. I'm wondering if you still think shared datasets are all that. I don't see how they are any different from using a stored procedure, other than you also now have to deploy an rsd file in addition to the stored procedure.

    ReplyDelete