SQL Server Reporting Services - Report Builder 2.0

SQL Server 2008 Reporting Services (SSRS 2008) includes the Report Designer and Report Builder 1.0 tools, which you can use to create reports ranging from the very simple to the highly sophisticated. Report Designer is hosted in Business Intelligence Development Studio (BIDS), so you get all of the fine control you need to get your reports exactly as you want them. However, BIDS is a daunting environment for non-developers and it requires a considerable investment on the part of the user in order to become proficient in its use. Report Builder 1.0 (RB 1.0), on the other hand, is targeted at non-developers such as information workers, who need to be able to create ad-hoc reports quickly and easily. It provides easy to use drag and drop functionality in a Microsoft Office-like environment and shields users from the complexity of the underlying data source(s) by enabling them to interact with report models. A report model is an abstraction layer that sits over the top of a data source and exposes the underlying data using business-oriented language that is more meaningful to the end user.

Report Builder 2.0 (RB 2.0) is a new tool that was made available as a separate download at the end of October 2008. It offers various improvements over RB 1.0, but it does not replace it directly; you can use both tools side by side, if required. Whereas RB 1.0 is a ClickOnce application that can be installed by users from the same report server web site where they access their reports, RB 2.0 is a standalone application that must be installed separately where required. (RB 2.0 is scheduled to be released as a ClickOnce version as part of SQL Server 2008 Service Pack 1 – you’ll then be able to choose whether version 1.0 or 2.0 is installed when users click the link on the report server web site).

So what does RB 2.0 offer that RB 1.0 doesn’t? Well, firstly the Office-like theme is continued, but the look and feel are more like Office 2007; for example, there is a ‘ribbon’ in place of the older style toolbar (Figure 1). Whilst this is a matter of personal preference, I see this as an improvement.





Figure 1 – The Report Builder 2.0 user interface

Report creation wizards
RB 2.0 simplifies report creation with the new wizards for creating table, matrix and chart based reports, which guide you through the process. You can select a data source (see below), drag and drop is used to add fields to the report and you can arrange fields into columns, rows and values by using selection boxes. Once you’ve created a basic report, you can easily modify it by adding data regions including lists and gauges (new in SSRS 2008) and report items including images and text boxes.
Access to shared data sources
One of the drawbacks of using RB 1.0 is that it requires a report model to be created in advance so that it can interact with it. RB 2.0 can also access report models, keeping report creation easy for information workers, but it can additionally work with other data sources directly. These can be shared data sources that already exist or embedded sources that you create using RB 2.0 itself. The report creation wizards include connectors for a wide variety of sources (Figure 2). Once you have created your source, you can select the data that you wish to extract by using a text based editor (or in the case of SQL Server connections, a graphical editor).




Figure 2 – Data source properties dialog box


Editing of reports stored on the report Server
RB 2.0 supports the editing of reports that are stored on the report server. This enables users to use RB 2.0 to customize reports that were created and published by using Report Designer or RB 1.0. RB 1.0 is limited in this respect as reports that you create using RB 1.0 and then open and modify using Report Designer cannot then be opened again in RB 1.0, which is somewhat restrictive. Now, a developer can create and publish a sophisticated report using Report Designer, and users can access and modify the report themselves using RB 2.0 or Report Designer without any problem.

Summary
Because of these (and other) improvements, RB 2.0 will be attractive not only to information workers, but for developers too. True, it doesn’t support the full range of functionality that BIDS does, but it supports most of the major items – and it has the added benefit that you don’t need to install BIDS in order to create advanced reports.
For more information on Report Builder 2.0, visit Books Online at http://msdn.microsoft.com/en-us/library/dd207008.aspx