Manage Learn to apply best practices and optimize your operations.

An overview of SQL Server Report Builder 2.0

In this chapter excerpt from "Learning SQL Server 2008 Reporting Services", learn about Report Builder 2.0, including its interface details, and how it can be used to create descriptive reports.

Learning SQL Server 2008 Reporting Services   This chapter excerpt from Learning SQL Server 2008 Reporting Services, by Jayaram Krishnaswamy, is printed with permission from Packt Publishing, Copyright 2009.

Click here to purchase the entire book.

About the author


Report Builder overview

In the present version of SQL Server 2008 [Enterprise Evaluation edition], there are two Report Builders available. Report Builder 1.0, which has remained as a program that can be launched from the Report Manager, and the new Report Builder 2.0, which is a stand alone report authoring tool that needs to be independently launched.

Although Report Builder 1.0 can access Report Models built with Visual Studio 2008 and the Report Manager, it cannot be used to create reports using those models. It also does not work with Reports generated by Visual Studio 2008 / BIDS / Report Builder 2.0. The errors can be summarized as follows:

  • When you try to access the Report Server 2008 from the link provided on the Report Builder 1.0 interface you get the following error message:

    Specifying credentials in a URL is not supported

  • When you try to open a report created using VS2008/BIDS/Report Builder2.0 using the Open Report… and Open File… navigational items in Report Builder 1.0 you get the following error message:

    System.IO.StreamReader: The Report element was not found.

  • Report Builder 1.0 allows you to access Report Models created with VS2008/BIDS/Report Manager and even allows you create a report in design view but this report cannot be processed on the Report Server. If you try to do so, you get the following error message:

    MemoryStream length must be non-negative and less than 2^31 - 1 - origin. Parameter name: offset; Remote GDI stream version: ?. Expected version: 11.0.1.

In this chapter the Report Builder 2.0 interface will be described along with the new features that are incorporated into this version. Report Builder 2.0 is admirably suited to address all items in the Report Definition Language of 2008. The Hands-on exercise 6.1 describes enabling My Reports, which allows each user to save his reports in his own folder helping the author to make his reports available after verifying that the report meets all the requirements. Hands-on exercise 6.2 describes how you can take an existing report authored with Report Builder 2.0 or otherwise (VS2008) and modify its formatting and other features using Report Builder 2.0. Hands-on exercise 6.3 shows how you may create a report from scratch and embellish with charts and gauges.

One of the important features of Report Builder 2.0 is the empowerment it provides business users to create ad hoc reports using the Report Models built on the databases they use. Building these models was discussed in Chapters 3 and 5. In the next chapter, you will be creating ad hoc reports based on the models you created earlier using Report Builder.

In this chapter, you will be learning mostly about the Report Builder 2.0 interface details and working with it to create reports or modify them. It may be noted that Report Builder generates 2008 compliant RDL files as described here and therefore, cannot work with reports generated using 2005 technology.

Report Builder 2.0 user interface description

Report Builder is a report authoring tool and the basic procedure for authoring a report consists of the following steps:

  • Report planning
  • Connecting to a source of data
  • Extracting a dataset from source
  • Designing the report and data binding
  • Previewing the report

Although deploying the report is not included in the above, Report Builder can deploy the report as well. It is not always necessary to deploy a completed report, as any part of a report definition file can be deployed. This makes modifying a report on the server very flexible.

In the following sections, the various parts of the Report Builder interface will be described starting at the very top and going to the bottom of the interface.

The menu for file operations

Report Builder 2.0 can be accessed from Start | All Programs | Microsoft SQL Server 2008 Report Builder | Report Builder 2.0.

This brings up the Report Builder Interface 2.0 as shown with the design area containing two icons: Table or Matrix and Chart. Each of these will launch a related wizard which will step you through the various tasks. The Report Builder 2.0 interface is very similar to Office 2007. More than one instance of Report Builder can be launched.

Report Builder instance

At the very top of the following screen shown you have the undo and redo controls as well as a save icon.

Report Builder controls

When you click on the save icon the Save as Report window gets displayed as shown. Here you provide a name for the report. The default save extension is *.rdl and it will be saved to the report server. It may also be persisted to a folder on your machine.

Report Builder Save as Report window

Clicking on the Office button (top left) opens a drop-down window shown in the following screenshot:

Report Builder Office button

In this window, you can carry out a number of tasks such as creating a new report, opening an existing report, saving a report, and saving a report with a different name.

The Save button saves it to the default location seen earlier and Save as invokes the same window to save the report with a different name as seen earlier displaying the report server instance as the Save to location.

The Recent Documents pane shows the more recent reports created with this tool.

New allows you to create a new report. When you click on Open, the following Open Report window gets displayed with the default location http://Hodentek2:8080/ReportServer_SANGAM/My Reports. You will also notice the message: This folder is not available because the My Reports feature is not enabled on the computer. Also the Open Reports window allows you look for reports with the extension .rdl. Therefore, unless the My Reports feature is enabled, this window is unusable. This is supposed to be possible from Report Manager but there are no controls in Report Manager that would do this. An alternative was suggested by one of the MSDN forum moderators (see here). Hands-on exercise 6.1 describe how you may enable My Reports. The idea of My Reports is similar to My Documents where each user can keep his reports.

When the Options button (in the previous screenshot) is clicked it opens the window Report Builder Options window with two tabbed pages Settings and Resource shown as follows:

Report Builder Options window

Here you can view, as well as modify, Report Builder settings. The defaults are more than adequate to work with the examples in this book.

Clicking on the Resources button brings up this interesting window which enables you to interact with Microsoft regarding SSRS activities, concerns, community, and so on. If you are serious about Reporting Services, these are very valuable links. The About button when clicked can provide you with Report Builder version information.

Report Builder About and Resources button

The ribbon

The main menu consists of Home, Insert, and View menu items which are part of the "ribbon". The ribbon introduced by Microsoft in Office 2007 is actually a container for other toolbar items. The ribbon is the replacement for the classic menus, toolbars, and is supposed to be more efficient and discoverable by the user. In fact you see a lot more on the "ribbon" than in the classic menu.

Want to read the rest of this chapter? Purchase the entire book from Packt Publishing.

Jayaram Krishnaswamy has over 80 publications in refereed and non-refereed publications and 8 issued patents. He has been working in the IT related fields since 1997 and was once a Microsoft Certified Trainer in Networking and a Siebel developer. During 2006-2007 he wrote more than 200 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun and other vendor products.

Dig Deeper on Microsoft SQL Server 2008