SQL Server Reporting Services provides a full range of ready-to-use tools and services to help you create, deploy, and manage reports for your organization, as well as programming features that enable you to extend and customize your reporting functionality.

 

One of the many new features in SQL Server Reporting Services is Report Parts. You can publish parts of a report then reuse those parts in other reports. This Report parts are introduced in SQL Server Reports Services 2008 R2 and later versions.

 

What is Report Part?

 

As I see it, Report items such as charts, gauges, images, maps, parameters, rectangles, tables, matrices, and lists can be published as report parts. Report parts are paginated report items that have been published separately to a report server or SharePoint site integrated with a report server and that can be reused in other paginated reports. Report parts have an .rsc file extension.

 

Publishing Report Parts

 

From my point of view, when you publish a report part, Report Designer assigns it a unique ID. From then on, it maintains that ID, no matter what else you change about it. The ID links the original report item in your report to the report part. When other report authors reuse the report part in Report Builder, the ID also links the report part in their report to the report part. To publish a report part, you must have a connection to and sufficient permissions on a SQL Server report server when you deploy the report.

 

I have observed, publishing a report part that displays data, such as a table, matrix, or chart, base it on a shared dataset; otherwise, publishing a report part, the dataset that it depends on is saved as an embedded dataset, anyone who reuses this report part will need to provide the credentials for the embedded data source. To avoid this, base your embedded and shared datasets on shared data sources with stored credentials.

 

Publishing a report part in Report Designer is a two-step process:

 

1. Flag the report items that you want to publish in the Publish Report Parts dialog box.

SSRS

 

2. Deploy the Report

 

Publishing a report part in Report Builder

 

1. On the Report Builder menu, click Publish Report Parts.
If you are not connected to a report server, you will be prompted to connect. If you cannot connect to a report server, you cannot publish report parts.
2. To save your report parts with default settings to the default location, click Publish all report parts with default settings.
Otherwise, click Review and modify report parts before publishing.
3. Edit the report part name and description: Double-click the name to edit it and click in the Description field to add a description.
4. To save your report part to a folder other than the default, click the Browse button.
5. When you have set the options for all the report parts in the report, click Publish.
After you publish the report parts, the dialog box shows which were successfully published and which were not. You can view details in the Publish Report Parts dialog box for the report parts that failed to publish.
6. Click Close.

 

SSRS1SSRS2

 

Reusing Report parts

 

In Report Designer, publishing is one-way: you can publish a report part from Report Designer, but you cannot reuse an existing report part in Report Designer. In Report Builder, enables collaborative authoring: I create a report part and publish it. You can reuse, modify, and republish it.

 

To reuse the existing report Parts in the server, Create a blank report in Report Builder and add report parts to report body from Report Part Gallery. Report Part Gallery normally appears on the right side of the Report Builder UI. If it is not available to go to Insert menu and click on Reports Parts as shown below:

 

SSRS3

 

After adding a report part to your report, the Report Builder maintains an association between the report part in the report and the report part on the report server; you can modify the report part as per your need in the report. Changing the report item in your report will not affect the instance of the report part published on the site or server, nor will it break the relationship between the instance in the report and on the site or server. If you have sufficient permissions, you can save the updated copy back to the site or server.

 

Conclusion:

 

From my point of view, with report parts, work groups can now take advantage of the different strengths and roles of their team members. For example, if you are responsible for creating charts, you can save your charts as separate parts that you and your coworkers can reuse in other reports. You can publish report parts on a report server or SharePoint site integrated with a report server. You can reuse them in multiple reports, and you can update them on the server.

 

Find out why should you consider upgrading to SQL Server 2016 Reporting Services in this webinar.

Usha
Usha
Software Engineer