Getting Started with Report Builder 3.0 Shared Datasets

By: Ray Barley | Comments (4) | Related: > Reporting Services Report Builder

Problem

I have many reports that allow users to select a year filter from a dropdown list. Is there a way that I can define this query once then reference it in multiple reports?

Solution

SQL Server Reporting Services 2008 R2 has a new shared dataset feature that will do exactly what you need. Prior to 2008 R2 a dataset definition was embedded in a report so it could not be shared across multiple reports. In this tip I will walk through creating a shared dataset then using it in a report. I'll use Report Builder 3.0 to create the shared dataset and a report that uses it. You need the 3.0 version of Report Builder to create a shared dataset. You can download it from the SQL Server 2008 R2 Feature Pack site.

I will first create a shared data source then move on to create the shared dataset and the report that uses it. I'll use the Contoso BI Demo Data (SQL Server database) in the sample report.

Create a Shared Data Source

A shared data source can be used by multiple reports. It provides the information necessary to retrieve data to be rendered on a report. In order to create a shared dataset you need a shared data source. You can create a shared data source in any of the following ways:

A Business Intelligence Development Studio (BIDS) Report Server Project or the Report Server Project Wizard
Report Manager web application (when Reporting Services is running in native mode)
SharePoint library (when Reporting Services is running in SharePoint Integrated mode)
Note: Report Builder does not provide the capability to create a shared data source.

To create a new shared data source in a BIDS Report Server project, right click Shared Data Sources in the Solution Explorer as shown below, then select Add New Data Source from the menu:

To create a new shared data source in the BIDS Report Server Project Wizard, please refer to the section Create a Shared Data Source in our SQL Server Reporting Services Tutorial.

For both the BIDS Report Server Project and Report Server Project Wizard the shared data source must be deployed before it can be used. To deploy right click on the project in the solution explorer then select Deploy from the menu.

To create a new shared data source in Report Manager, navigate to a folder (e.g. Data Sources) and click New Data Source on the toolbar as shown below:

To create a new shared data source in a SharePoint library, navigate to the library, click the Documents tab, click New Document, then click Report Data Source from the menu as shown below:

Please refer to the section Preparing a Document Library in the tip Integrating SQL Server 2008 R2 Reporting Services with SharePoint 2010 for the details on setting up content types which is required in order for Report Data Source to appear in the New Document menu.

Regardless of which method you choose to create a shared data source, the information required is essentially the same. As an example the screen shot below is taken from a shared data source in a SharePoint library:

The main pieces of information are the Data Source Type, Connection String and Credentials.

Create a Shared Data Set

Launch Report Builder 3.0 from the Microsoft SQL Server 2008 R2 Report Builder 3.0 program group. Click New Dataset as shown below:

Note that the getting started dialog is the only place where you can create a new shared dataset in Report Builder 3.0. If you check the "Don't show this dialog box at startup" checkbox, you won't see the getting started dialog. If that happens go to the Report Builder Options dialog and click the Show the Getting Started dialog at startup checkbox. To get to the Report Builder Options dialog click the icon in the top left corner of the Report Builder window then click the Options button.

After clicking New Dataset choose the data source as shown below:

You can choose a data source from the list or click the Browse other data sources hyperlink to find the data source you need. Click Create after making your data source selection. The query designer will be displayed as shown below:

this query designer is for a data source type of microsoft sql server

The query designer shown above is for a data source type of Microsoft SQL Server. The layout of the query designer may be slightly different for another type of data source; e.g. Microsoft SQL Server Analysis Services. You can design the query by selecting columns from tables or views; you can also select a stored procedure. You can click Edit as Text to type in the query, or click Import to copy a query from a report or shared dataset. I'll use the following query to retrieve the current year and the previous four years to populate the dropdown list for the Year filter:

SELECT DISTINCT CalendarYear
FROM dbo.DimDate
WHERE CalendarYear > (DATEPART(year, GETDATE()) - 5)
ORDER BY CalendarYear DESC

Save the query by clicking the familiar diskette icon on the top of the Report Builder window. I named the dataset YearFilter. I'll create another shared dataset that will provide some sales data for our report. I named this dataset SalesByChannel and the query is shown below:

SELECT d.CalendarYear, c.ChannelName, f.SalesAmount
FROM dbo.FactSales f
JOIN dbo.DimDate d ON d.DateKey = f.DateKey
JOIN dbo.DimChannel c ON c.ChannelKey = f.ChannelKey
WHERE d.CalendarYear = @CALENDAR_YEAR

Note that the SalesByChannel query has a parameter named @CALENDAR_YEAR. This parameter will get its value from a report parameter.

Create a Report

To create a report using Report Builder 3.0 click New Report then Table or Matrix Wizard as shown below:

Choose the shared dataset for the report as shown below:

Arrange the query fields as shown below (drag and drop a field into the Row groups, Column groups, or Values):

Accept the default values for the remaining steps in the wizard.

The last step is to populate the CALENDAR_YEAR report parameter dropdown list from the YearFilter shared dataset. The CALENDAR_YEAR parameter is available in the Report Data section as shown below (if the Report Data section isn't visible click the View tab then click the Report Data checkbox):

populate the calender_year report parameter dropdown list

Right click on Datasets in the Report Data section then select Add Dataset from the menu. Fill in the Dataset Properties as shown below (you may have to click Browse and navigate to the location of the YearFilter shared dataset):

Right click on the CALENDAR_YEAR parameter underneath Parameters in the Report Data section of the Report Designer, select Parameter Properties from the menu, and fill in the Report Parameter Properties as shown below:

fill in the report parameters properties

Now we are ready to run the report. The output is shown below:

The CALENDAR YEAR report parameter renders the list of years retrieved from the YearFilter shared dataset.

Next Steps

Shared datasets is a new SQL Server 2008 R2 feature that is very handy for streamlining your development and making it easier for business users to create their own reports.
There are many new features in SQL Server Reporting Services 2008 R2; you can find some useful tips here.
Report Builder 3.0 is becoming a robust reporting tool for business users; you should give it a try.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips