Report Builder 3.0, which comes with SQL Server 2008 R2, is a new enhanced report development and report authoring tool intended to be used by business users for ad-hoc reporting. Report Builder 3.0 has numerous new features and one of them is Report Part Gallery, which allows you to create and publish report parts and reuse them in other reports. In other words, you can publish different report parts on the report server and reuse them in different reports when required, without recreating them from scratch.
By default Report Part Gallery appears on the right side of the Report Builder User Interface (UI) but if you don’t see it, you can enable it from the View menu tab on the ribbon. On the top of the Report Part Gallery you will notice one search box, which you can use to search a report part available/published on the connected report server.
In the next section I will demonstrate you how you can create and publish a report part. To use an already created report part in your report, you just need to drag it from the Report Part Gallery to the report area where you want that report part to appear.
You can publish tables, matrices, charts, gauge, images etc. as a report part to the report server (which will have .rsc file extension). Once a report part is published, a user with the appropriate permissions can modify it and publish it again either by overwriting the existing report part or creating an altogether new one. When a report part is published, the associated dataset (or other item on which the report part depends) is embedded in it if you are not using Shared dataset.
Please note, you can create and publish a report part either in both Business Intelligence Development Studio Report Designer or in Report Builder 3.0 but you can only search and use it in Report Builder 3.0.
Creating and Publish Report Part in Report Builder 3.0
In my last article “Getting started with Report Builder 3.0 I talked about some of the new features of Report Builder 3.0 and showed you how to start creating reports in it. In this article I am not going to show you step by step how to create a report but rather I will be more focused on creating, publishing and using report parts.
You can publish part of any existing report or create a new report to publish its part. In this example I am going to create a table/matrix report, which displays customer, product wise sales data. I am going to use this query to get data from the database (AdventureWorks2008R2) for this report.
SELECT C.CustomerID, C.AccountNumber, ISNULL(Per.Title, '') + ' ' + ISNULL(Per.FirstName, '') + ' ' + ISNULL(Per.MiddleName,'') + ' ' + ISNULL(Per.LastName, '') AS CustomerName, SOH.SalesOrderID, SOD.ProductID, P.ProductNumber, P.Name AS ProductName, SOD.OrderQty, SOD.UnitPrice, (SOD.OrderQty * SOD.UnitPrice) AS ProductPrice FROM Sales.SalesOrderHeader SOH INNER JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID INNER JOIN Sales.Customer C ON SOH.CustomerID = C.CustomerID INNER JOIN Person.Person AS Per ON C.CustomerID = Per.BusinessEntityID ORDER BY C.CustomerID, SOH.SalesOrderID
Launch the new report creation screen and choose Table or Matrix Wizard as shown below. On the next two screens you need to specify or create a data source for this report.
Figure 1 - Getting Started
On the next screen, specify that you want the query as a text rather than selecting columns of tables/views; then you need to place the query that I have provided above as shown in the screenshot below:
Figure 2 - Specifying Query
On the next screen, arrange the fields and specify the row groupings and sales values as shown below:
Figure 3 - Arranging fields
Then on the next screen specify the layout of the report and choose whether to have Expand/Collapse symbols as shown below:
Figure 4 - Choosing Layout
The last screen lets you choose the style for your report; there is some built in styling, which you can use for your report. If required, you can customize the styling as well.
Figure 5 - Choosing Style
Now that we're done with the report wizard and report has been created, you are back in Report Builder User Interface (UI). In this UI, by default you can see the Report Data pane on the left side; on the bottom you can see row groups and column groups and in the middle you can see the report in design mode.
Figure 6 - Report Builder 3.0 UI
If you notice, I have selected the Product ID to be displayed on the report, which is not intuitive and hence I want to put Product Number (or Product Name). For that simply go to the dataset on the left side, select the column/field that you want to be on the report and drag it on the designer where you want to place it. You can see below that I have done the same for Product Number.
Figure 7 - Adding additional fields to report
You can now hit F5 or click on the Run ribbon item to preview the report. While being in preview mode, you can click on the Design ribbon item to return back to design mode.
Figure 8 - Previewing report
We are now ready with the report and want to publish it. For that, go to menu and click on the Publish Report Parts menu item as shown below; you need to be connected to the report server to publish the report part. You can change the report server URL and location to publish the report part by going to Options.
Figure 9 - Publishing Report Part
When you click on the above Publish Report Parts menu item, you will see two options for publishing. The first one lets you publish all the reports parts of the report with the default settings, whereas the second option lets you specify the report parts to be published.
Figure 10 - Specifying Report Parts to publish
As I have selected the second option, the wizard lets me specify the report parts to be published. We can provide a meaningful name and description for the report part (recommended) as shown below. By default report parts are deployed on the Report Parts folder on the connected report server, which can be changed by browsing to a new location.
Figure 11 - Select Report Parts to Publish
Once you are done with the changes, you can click on the Publish button on the above screen to start the publishing. The result of this will be displayed on the Results area.
You can go to the report server and verify the published report part as shown below. If you are authorized, you can manage the properties and security for that report part on the report server.
Figure 12 - Verifying published report part
Using Report Part in Report Builder 3.0
Using a published report part is very easy. You just have to go to Report Part Gallery, choose the report part that you want to use in your report and drag it to the report area where you want the report part to appear.
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 and if you have required permissions you can republish the changed report part to the report server.
If the report part is updated on the report server and if the reports with that report part are opened, it will notify you about the updated report part. You can choose to keep the existing report part as is or accept/bring the new updated report part in the report, which means it will replace the already available report part in the report with the latest/updated report part from the server.
Let’s start and create a blank report as shown below:
Figure 13 - Creating a blank report
Report Part Gallery normally appears on the right side of the Report Builder 3.0 UI. If it is not available go to Insert menu and click on Reports Parts as shown below:
Figure 14 - Enabling Report Part Gallery
Report Part Gallery displays all the published report parts on the connected report server. You can browse and search report part by typing some keywords and clicking on the magnifier icon as seen in the image below:
Figure 15 - Searching Report Part
Now to put the report part in your report, simply drag it from the Report Part Gallery to the report area where you want it to appear. This is what I have done below; you can see that associated objects like data source, dataset etc. will also be brought into your report.
Figure 16 - Adding report part to report
Now you can preview the report as you normally do with normal reports and do modification in your report if needed. Report Builder maintains an association and hence if you have the required permission you can republish the changed report part to the report server.
Figure 17 - Previewing report part
In this article I talked about a new feature called Report Part of Report Builder 3.0. I demonstrated how to create and publish a report part and how you can reuse it in other reports.