Microsoft released the first version of Report Builder in 2005, touting it as a development and management tool that would help end users and DBAs build customized reports. Unfortunately, the result was a complex set of templates and a complicated development paradigm that made the tool more difficult to use than simply using Visual Studio’s business intelligence (BI) tools or the ReportViewer toolset to create reports. Report Builder 2.0 bears little resemblance to the initial product, and the reincarnated tool is easy for both end users and report managers to use. However, Report Builder isn’t ideal for all environments; to determine whether it’s right for your organization, see the sidebar “Are You Ready for Report Builder 2.0?”
Architecture
Report Builder 2.0 leverages the code from SQL Server 2000 Reporting Services (SSRS); this chunk of code is basically a locally hosted Report Definition Language (RDL) Report Processor with a custom report-rendering extension that uses Windows API calls to paint the generated report on the user’s screen rather than passing HTML to a browser.
Like the server-hosted SSRS Report Processor, Report Builder’s Report Processor (see Figure 1) executes the following operations after the end user connects to a specific SSRS instance report catalog:
- Opens a selected report in the SSRS catalog; this RDL file can also be loaded from the network file system as a second-generation RDL file (I discuss first- and second-generation RDL file formats later in the article)
- Interprets and validates the RDL report definition file
- Opens the cataloged data source specified in the RDL file and subsequently opens the database connection to authenticate the user given the credentials referenced in the data source
- Captures unpopulated input parameters by exposing UI elements
- Executes the embedded queries and passes the rowset to the Report Processor; merges the data with the RDL report definition’s report layout
- Displays the report to the user
- Displays alert dialogs to the user if any exceptions occur
This is the same sequence of events that the SSRS engine executes on the server when it renders simple reports. However, instead of returning HTML code to a browser (as SSRS does), Report Builder’s Report Processor performs the steps on the client system and creates the report in a Report Builder window. The biggest difference between Report Builder and SSRS is that the database connection is opened by the remote client’s Report Processor—not by the server-side SSRS Report Processor.
Note that the connection string defined in the data source must still be able to use the same credentials to access data. That is, if the data source uses Security Support Provider Interface (SSPI) authentication, the credentials passed to SQL Server are the same as those passed if the report is referenced from a Windows-hosted browser. Report Builder doesn’t let report developers reference SSRS cataloged reports that have user-specific credentials—only shared data source reports are supported. It’s possible to create locally persisted reports that use user-supplied, hard-coded, or SSPI authentication, but these reports can’t be saved back to the SSRS catalog—only to the file system. (For more information about security, see the sidebar “Report Builder 2.0 Security.”
Because of its basic design, Report Builder 2.0 tends to lead report users and developers toward creating, persisting, and retrieving reports that have already been saved to a specified instance of SSRS. Although it’s possible to create reports that reference data from a variety of data sources, it’s not exactly intuitive how to do so. As I step through the process of using Report Builder, I’ll also explain how to use data sources other than those exposed by SSRS.
Getting Started
You can download Report Builder 2.0 from the Microsoft download site. Install Report Builder 2.0 on your SSRS server, which lets users easily install the program from Report Manager with a single click. However, you need to verify that your DBA hasn’t disabled Report Builder. In this section, I walk through the process of enabling (or disabling) Report Builder functionality. Let’s start by configuring the SSRS instance to expose Report Builder 2.0 to users who have access to the appropriate security groups through Report Manager.