By: Ray Barley | Comments (8) | Related: > 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:
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:
The Publish Report parts dialog is displayed as shown below. You can publish all report parts or select specific report parts as shown below:
Choose Review and modify report parts before publishing; the following dialog is displayed where 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:
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:
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:
The report designer is displayed. To add published report parts to your report, click the Insert tab then click Report Parts as shown below:
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:
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:
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:
Click View Updates to display the details for the updated report parts as shown below:
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.
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
