Introduction to SQL Server 2008 Report Builder 2.0

Problem
I have heard that there is a new version of Report Builder in SQL Server 2008. Can you provide some details and examples? What is new with Report Builder 2.0? How does the new interface look? What is the learning curve with this tool?

Solution

SQL Server 2008 includes a brand new version of the Report Builder which was first introduced in SQL Server 2005. The key new features in Report Builder 2.0 are:

  • A completely new user interface that conforms to the Office 2007 look and feel
  • A local client install rather than a click-once application that you download and install from Report manager
  • Supports running reports locally or on the server
  • A Report Model is not required; you can create your own queries using a query designer, import queries from existing reports, or manually type in your queries
  • A Tablix report type which is a combination of the matrix and table reports

In this tip we will review installing Report Builder 2.0, the new user interface, developing a sample report, and deploying the report to a SQL Server 2008 report server. I'll cover the new Tablix report in a future tip.

Installing Report Builder 2.0

Report Builder 2.0 is not included on the SQL Server 2008 media. Instead you can download it from theMicrosoft SQL Server 2008 Feature Pack, October 2008site. Look for the link in the section titled Microsoft SQL Server 2008 Report Builder 2.0 to download the installer. Report Builder 2.0 requires the Microsoft .Net Framework version 3.5. You will see a link to download the .Net Framework as well. As an aside you will find a number of other useful downloads on the feature pack site.

The original Report Builder that was released with SQL Server 2005 is still available. When you open the Report Manager, you will still see the Report Builder button as shown below (assuming you are a member of the Report Builder role in SSRS):

Please note that this will not launch Report Builder 2.0 which must be installed locally as noted above. After installing Report Builder 2.0 you launch it by clicking Start, All Programs, Microsoft SQL Server Report Builder 2.0, Report Builder 2.0.

Report Builder 2.0 User Interface

After launching Report Builder 2.0 you will see the following:

You will notice a number of differences from the original Report Builder interface in SQL Server 2005, namely:

  • The Office 2007 ribbon interface; on the Home tab shown above you can control just about everything about the format of your reports.
  • The Report Data pane on the left provides a single place for accessing the built-in fields, report parameters, images, and data fields. The data fields will appear after you define your queries.
  • The center region is the report designer; click on the Table or Matrix or Chart icons to begin designing a report or chart.
  • Row Groups and Column Groups (cut off in the screen shot above) allow you to setup your groupings by dragging and dropping columns onto this area.

Clicking the Insert tab on the ribbon bar displays the following:

The Insert ribbon allows you to drag and drop elements onto the report designer.

You will find that the new user interface is pretty intuitive and easy to use. Now let's develop our first report.

Creating a Report with Report Builder 2.0

The steps that we will follow to create a report are:

  • Create a data source
  • Specify a query
  • Lay out the data fields into row groups, columns groups or values
  • Choose a layout
  • Choose a style

The above sequence is very similar to the steps in the Report Wizard in prior versions of SQL Server Reporting Services.

We will use the AdventureWorksDW2008 sample database as the data source for our report; you can download the sample database from thissite.

To begin click on the Table or Matrix icon in the report designer area. The following dialog will be displayed:

Click New to create a new data source. Fill in the following dialog as shown (in my case I have SQL Server 2008 installed on my local machine as the named instance sql2008):

After completing the Data Source Properties dialog you will be returned to the New Table or Matrix dialog; click Nextto proceed to the Design a query dialog:

The Design a querydialog allows you to specify your query in one of the following ways:

  • Click Edit as Text to manually type in your query in a text editor; you can also select a stored procedurefrom a dropdown list.
  • Click Import to retrieve a query from another report. You can browse the file system for a report file to open then copy a query from it.
  • You can use the built-in query designer.

Click Edit as Text and copy/paste the following query into the dialog:

SELECT
d.CalendarYear
,p.EnglishProductName
,c.EnglishProductCategoryName
,s.EnglishProductSubcategoryName
,f.SalesAmount
FROM
FactInternetSales f
INNER JOIN DimProduct p
ON p.ProductKey = f.ProductKey
INNER JOIN DimDate d
ON d.DateKey = f.ShipDateKey
INNER JOIN DimProductSubcategory s
ON s.ProductSubcategoryKey = p.ProductSubcategoryKey
INNER JOIN DimProductCategory c
ON c.ProductCategoryKey = s.ProductCategoryKey

Click Next to proceed to the Arrange Fields dialog. Drag and drop the fields from the Available fields list into the Row groups and Values lists as shown below.

Click Next to proceed to the Choose the Layout dialog as shown below:

Accept the defaults as shown above then click Next to proceed to the Choose a Style dialog as shown below:

Click Finish and you will now see the report in the designer as shown below:

You can click in a cell and edit the contents. In the report designer above the title and column headings were edited. You can also select a cell and click on the various toolbar icons on the Home tab of the ribbon; e.g. Bold, Italic, etc. to set your formatting.

Click on the Disketteicon at the top of the window to save the report as an RDL file on your local hard drive. Click the Run icon on the Home ribbon to run the report locally. You will see the following output:

Click the + to expand the 2001 year, then click the + to expand the Category; you will see the following:

As shown in the screen shot above, there are options to navigate between pages of the report, zoom in or out, print, or export thereport to various formats. Click on the Export button and you will see a new option in SQL Server 2008 - Microsoft Word.

Although our sample report is a very simple one, you can see that this new version of the Report Builder may be a good fit for folks who are not developers. It may also be useful when you just want to create a simple report.

Deploying a Report

To deploy a report to a SQL Server 2008 report server, you should specify the URL of your default report server in the Report Builder settings. Click the database icon in the top left portion of the Report Builder window and you will see the following popup menu:

Click the Options button and you will see the settings dialog as shown below:

Enter the URL of your default report server or SharePoint site where you want to deploy reports. If you are unsure of the exact URL of the report server, connect to Reporting Services using SQL Server Management Studio (the 2008 version), right-click the report server in the Object Explorer, and take a look at the value shown for the URL. For my installation the URL is:

The value that you see for the URL is what you need to enter in the settings dialog shown above. Check with your SharePoint administrator for the SharePoint URL that you need.

When you select Save or Save As from the popup menu that you launch by clicking the database icon in the top left corner of the Report Builder window, you can navigate to a folder in the file system or the URL of your report server or SharePoint site.

As always you can also deploy a report to the report server by navigating to the report manager and clicking the Upload File button on the toolbar.