• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Blog
  • Power Platform
    • Power Apps
    • Power Automate
    • Power BI
    • Power Virtual Agents
  • Dynamics 365
  • Azure
  • About Me
Joe Gill Logo

Joe Gill

Microsoft MVP - Power Platform Consultant

Home » Power Platform » Dynamics 365 » Reporting Services – Shared Datasets

Reporting Services – Shared Datasets

7th July 2010 by Joe Gill Leave a Comment

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

Reporting Services - Shared Datasets Joe Gill Dynamics 365 Consultant

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
Reporting Services - Shared Datasets Joe Gill Dynamics 365 Consultant

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.
Share This On Social:
  • Tweet
  • Reporting Services - Shared Datasets Joe Gill Dynamics 365 Consultant

Filed Under: Dynamics 365, SQL

Reader Interactions

Leave a Reply Cancel reply

You must be logged in to post a comment.

Primary Sidebar

Popular Categories

  • Power Platform
    • Power Apps
    • Power Automate
    • Power Virtual Agents
  • Azure
    • Logic Apps
  • Dynamics 365
  • .NET
  • AI
  • SQL

More to See

Power Platform Requests – Base Request Capacity

17th December 2020 By Joe Gill

Power Automate Desktop

Power Automate Desktop – UI Flow

26th November 2020 By Joe Gill

Tweets

Footer

Joe Gill

Microsoft Business Applications MVP – Power Platform, Dynamics 365 and Azure.

An architect with over twenty years experience designing and developing technology solutions. Specializing in the Microsoft technology stack including Power Platform, Dynamics 365 and Azure. Microsoft MVP Profile

Connect on Social

Useful Links

  • Home
  • Blog
  • About Joe Gill
  • Power Platform
  • Dynamics 365
  • Azure

Featured Posts

Power Platform Requests – Base Request Capacity

Power Automate Desktop – UI Flow

MB-600 – Solution Architect – Supportability

AI Builder – Form Processing Layouts

© 2021 · Joe Gill