Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted December 7, 2011

Creating and using Report Parts in Report Builder 3.0

By Arshad Ali

Introduction

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.

Getting Started
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:

Specifying Query
Figure 2 - Specifying Query

On the next screen, arrange the fields and specify the row groupings and sales values as shown below:

Arranging fields
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:

Choosing Layout
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.

Choosing Style
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.

Report Builder 3.0 UI
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.

Adding additional fields to report
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.

Previewing report
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.

Publishing Report Part
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.

Specifying Report Parts to publish
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.

Select Report Parts to Publish
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.

Verifying published report part
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:

Creating a blank report
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:

Enabling Report Part Gallery
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:

Searching Report Part
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.

Adding report part to 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.

Previewing report part
Figure 17 - Previewing report part

Conclusion

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.

Resources

What's New in Report Builder 3.0

Report Parts (Report Builder 3.0)

See all articles by Arshad Ali



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM