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

 

Getting Started with Report Builder 3.0 Shared Datasets


By:   |   Last Updated: 2011-01-11   |   Comments (4)   |   Related Tips: > 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:

create a new shared data source in a BIDS Report Server project

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:

create a new shared data source in Report Manager

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:

create a new shared data source in a SharePoint library

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

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:

Launch Report Builder 3.0 from the Microsoft SQL Server 2008 R2 Report Builder 3.0 program group

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:

cosse the data source

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:

click new report then table or matrix wizard

Choose the shared dataset for the report as shown below:

choose the shared data set

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

arrange the query fields

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):

add dataset from the menu

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:

run the report and view the output

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.


Last Updated: 2011-01-11


get scripts

next tip button



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

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.



    



Thursday, February 21, 2013 - 5:35:54 AM - Ray Barley Back To Top

Change the YearFilter query so that it returns a label and a value; the label is what you see in the dropdown and the value is what you pass as a parameter to a query in the report.  Use union all in the YearFilter query to include 'ALL', 0 in the results so you get ALL in the report parameter dropdown.  

Change the SalesByChannel query to have a where clause like this (assuming you pass in 0 when all is selected)

WHERE d.CalendarYear = 
case @CALENDAR_YEAR when 0 then d.CalendarYear else @CALENDAR_YEAR end

Wednesday, February 20, 2013 - 2:09:59 PM - Hommer Back To Top

Cool article!

 

How can you make it to have a "All" in the year dropdown?

 

I can include the "All" in dataset YearFilter by adding an union select 'All', but I haven't find a way to let my SalesByChannel dataset ignore the where clause when 'All' is selected. 

 

 


Monday, January 02, 2012 - 7:06:23 PM - Ray Barley Back To Top

I don't think there is any capability in Report Builder to let you know which reports use a particular dataset.

The report itself is an XML file.  If you save your reports to your local hard drive you can search thru them.  You'll have to open up a report in a text editor (e.g. NotePad) so you can see how the dataset is represented in the XML.

 


Monday, January 02, 2012 - 2:24:58 PM - Jack Owens Back To Top

How easy it is to generate a list of report (Report Names) from where a user can identify where all the "Shared Dataset" is currently in use

Just to get a perspective of reports that will get affected if I need to  make any changes to an existing dataset


Learn more about SQL Server tools