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 Parts in SQL Server Report Builder 3.0


By:   |   Last Updated: 2011-01-31   |   Comments (8)   |   Related Tips: > Reporting Services Report Builder

Problem

One of the many new features in SQL Server Reporting Services 2008 R2 is one called Report Parts. You can publish parts of a report then reuse those parts in other reports. In this tip I will demonstrate how to take advantage of this new feature.

Solution

The Report Parts feature allows you to create a library of report components that you can add to a report you are designing. You create these report components by following the normal steps in designing a new report. You can use either Report Builder 3.0 or the version of Business Intelligence Development Studio (BIDS) that comes with SQL Server Reporting Services 2008 R2. Follow the normal steps to design a report then select the report parts that you want to publish. The components that you publish will be stored on the report server and you can specify a folder such as Report Parts. One important point is that only Report Builder 3.0 provides the ability to add report parts to a report; BIDS does not currently offer this capability. In this tip I'll use Report Builder 3.0 and SQL Server Reporting Services 2008 R2 running in native mode to demonstrate the report parts feature. I'll start out with an existing report, select report parts and publish them, then create a new report that is designed by using published report parts.

Publish Report Parts

I'll use the following sample report that has a matrix showing sales by channel and year, and a chart based on the same data as shown below:

a new feature of sql server reporting services 2008 r2 is called report parts

After opening the above report in Report Builder 3.0, click the icon in the top left of the Report Builder window then select Publish Report Parts from the menu as shown below:

open the report in report builder 3.0

The Publish Report parts dialog is displayed as shown below. You can publish all report parts or select specific report parts as shown below:

publish report parts

Choose Review and modify report parts before publishing; the following dialog is displayed where you can select specific report parts to publish:

you can select specific report parts to publish

Refer back to the sample report shown at the beginning of this section. The report has two parts to it - a matrix showing sales by channel and year, and a chart based on the same data. These two report parts are shown in the dialog above and they are named ChannelSalesTablix and ChannelSalesChart respectively. In addition the datasets and any report parameters used by these report parts will also need to be published. In the above dialog the ChannelSalesByYear is a shared dataset so it does not need to be published; i.e. since it is a shared dataset it is already published to the report server. Please refer to our earlier tip Getting Started with Shared Datasets in Report Builder 3.0 for the details on shared datasets which is also a new feature in SQL Server Reporting Services 2008 R2.

Select the report parts to publish then click the Publish button. Navigate to the Report Parts folder in the Report Manager and you will see the published report parts as shown below:

view ssrs report parts to see the published report parts

The folder where the report parts will be published is set in the Report Builder 3.0 settings. To review the settings click the icon at the top left corner of the Report Builder window then click the Options button; the report parts folder is specified for the prompt "Publish reports to this folder by default" as shown below:

view or modify report builder settings

Use Report Parts in a Report

Now that we have published a couple of report parts, let's create a new report that uses these report parts. Click the icon in the top left corner of the Report Builder window then select New Report and Blank Report as shown below:

use report parts in a reoprt

The report designer is displayed. To add published report parts to your report, click the Insert tab then click Report Parts as shown below:

click report parts

The Report Part Gallery will be displayed. Enter search criteria to find report parts or just click the search icon. The Report Part Gallery is shown below with the report parts that we published earlier:

the report gallery will be displayed

To add a report part to your report, drag and drop it from the Report gallery onto the report designer or just double click it. After adding both report parts to my new report and moving them around in the report designer, I have a report that looks like this:

using the report designer

Updating Report Parts

If a report part is updated then published again, any report that uses the report part can be notified that the report part has been updated and the report part can be updated in the report. This is done on an individual report basis. When you open the report in Report Builder you will see the following notification if any report parts used in the report have been updated:

updating report parts

Click View Updates to display the details for the updated report parts as shown below:

Report Parts is a new SQL Server 2008 R2 feature that can make it easier for business users to create their own reports.

Select any report parts to be updated in your report then click the Update. For each report part there is a checkbox where you can indicate that you want to be notified when the report part changes. By default the checkbox is checked.

Next Steps
  • Report Parts is a new SQL Server 2008 R2 feature that can make 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-31


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, June 27, 2013 - 8:07:11 PM - Csaba Toth Back To Top

A little off-topic, but I'm seeking for help: click-through doesn't seem to work in Report Builder 3.0 the way it was with Report Builder 1.0. See http://stackoverflow.com/questions/17322270/report-builder-3-0-does-not-generate-clickthrough-links/. Please help if you know a definitive/official answer for that. Thanks!

 


Monday, November 26, 2012 - 6:11:23 AM - Raymond Barley Back To Top

I don't know of any way to do that in reporting services. 

I believe you can get what you want with Power View which is a new thing in SQL Server 2012.  Presently PowerView is a feature of SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint Server 2010 Enterprise Edition.

For example take a look at this page of the documentation: http://msdn.microsoft.com/en-us/library/hh231514.aspx

The Power View docu,emtation starts here: http://msdn.microsoft.com/en-us/library/hh965698.aspx


Sunday, November 25, 2012 - 11:54:05 AM - G Back To Top

Hi,

Thanks but thats not quite what I meant. What I mean is if you click an item in Report A you somehow pass that information to Report B and it populates using that information. It doesn't have to be parts and can be within the same report

e.g You haved a bar chart. User clicks a column B. An ID relating to column B is passed to a table in the same report. The table is populated with rows from a query that uses the ID passed.

Using Lookup function disappointingly doesn't work. The function explicitly states that it cant do that. Obviously people want to do this which is why the notes say this - i'm hoping there is some other way that I haven't thought of??

Thanks


Saturday, November 24, 2012 - 1:16:39 PM - Ray Barley Back To Top

When you publish a report part it will also publkish any parameters used as filters in a dataset.  I did a test where I created 2 report tables that used different datasets that each had a startdate and enddate parameter.  I published both report tables as report parts.  When I inserted the report parts into a new report, both parameters were included in the report and th report ran successfully.

 


Thursday, November 22, 2012 - 12:40:41 PM - G Back To Top
Hi, I'm looking for a way to communicate between 2 parts using parameters (or something else)... is this possible do you know? Thanks

Friday, February 11, 2011 - 8:37:51 AM - Scott Back To Top

It's frustration that SQL Report Builder desktop tools are not smart card \ client certificate aware.


Thursday, February 03, 2011 - 1:46:43 PM - Adam Cassel Back To Top

@Ray FANTASTIC! Thank you for a terrific post, thoughtful and well done with polish! Very much appreciated!

@Don re: master/sub-reports & drill-down/drill-through examples, consider: 

SQL Server 2008 R2 Update for Developers Training Kit (January 2011 Update) Version 2.0

Extensive SSRS coverage, and one of the topics with both demo and hands-on lab, is;

"Adding Interactivity to a SQL Server 2008 R2 Reporting Services Report", which includes "...how to add interactivity...including...drilldown and drillthrough behavior..." 


Monday, January 31, 2011 - 10:35:44 AM - Don Back To Top

I love tidbits like this as I really don't have time to read the 900 so-so pages of the last WROX SSRS book.  I'd love to see a comparison to master/sub-reports and use cases where each is superior.


Learn more about SQL Server tools