Report Builder 2.0

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:

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.

Start SQL Server Management Studio (SSMS) and open a connection to the SSRS instance, as Figure 2 shows (not to the database server). Once connected, right-click the connection and select Facets to open the Surface Area Configuration for Reporting Services facet.

Ensure that the WebServiceAndHTTPAccessEnabled property is set to True, as Figure 3 shows, to use Report Builder (as well as Report Manager or other Simple Object Access Protocol—SOAP—applications that interact with SSRS). Click OK.

Next, navigate to the Server Properties dialog box and select Security. Make sure the Enable ad hoc report executions check box is selected. You might also want to clear the Enable Windows integrated security for report data sources check box, to prevent Trojan attacks.

Open a browser and navigate to Report Manager. Open the SSRS Configuration Manager or check with your report DBA to determine the correct Report Manager URL. It typically resembles https:// /Reports_/Pages/Folder.aspx.

Click Site Settings and enter /ReportBuilder/ReportBuilder_2_0_0_0.application in the Custom Report Builder launch URL text box. This URL will let users launch Report Builder 2.0 with one click, as long as their systems have sufficient rights.

Assign individual users (or preferably, Windows domain groups) to specific roles in Report Manager, as Figure 4 shows. This step is crucial for ensuring that only the appropriate users can access Report Builder and associated SSRS roles. Although this process is fairly easy, it’s beyond the scope of this article. For more information, see Peter Blackburn and William R. Vaughn, Hitchhiker’s Guide to SQL Server 2000 Reporting Services (Addison Wesley, 2004), Chapter 4.

In Report Manager, navigate to the Home directory in the SSRS report catalog. Notice that the Report Builder icon is displayed, along with a My Reports folder. Enable the My Reports check box, which instructs SSRS to build a dedicated folder for each user so that individuals have a private place to save reports. You can configure the security settings to prevent users from saving reports to any other folder, which is a good idea because doing so protects existing production reports from both inexperienced and malicious users. I also recommend creating a Run As shortcut to open a browser using the target user’s credentials to simulate what the end-user will actually encounter when running Report Manager. This approach is much easier than going to the user’s office and looking over his or her shoulder.

Managing Reports
Once you’ve enabled Report Builder and configured the appropriate site settings in Report Manager, end users can create reports or open existing reports from the SSRS catalog. To access an existing report, launch a browser and open Report Manager. Then, click the Report Builder icon to launch the click-once deployment process, which either installs Report Builder for the first time or verifies that the client’s installation is current. Note that in some versions of Windows and depending on the security settings, a user might not have sufficient rights to install applications such as Report Builder on the client system. In this case, the client must open the browser as Administrator.

Once Report Builder 2.0 is installed, you can launch it on a user’s system through the Start menu. After you launch Report Builder, you’ll see a Window that contains two icons. The Table or Matrix icon launches a wizard that steps you through the process of creating a new report based on a single Tablix control, which is new for second-generation RDL. The Chart icon starts a similar wizard that builds a report based on the Chart control.

Before you can reference an SSRS cataloged report, you must set another configuration property that addresses the specific SSRS instance containing the report catalog. Click the Files icon in the upper left corner of Report Builder, and select Options. In the Report Builder Options dialog box that opens, set the Report Server URL to point to your specific instance. As I mentioned previously, you can use the SSRS Configuration Manager utility to find this URL. Alternatively, you can set the URL to point to any SSRS instance that has exposed the report catalog via SOAP.

Modifying a cataloged report. After Report Builder opens a SOAP connection to the targeted SSRS catalog, you can pull up reports or shared data sources from the catalog. Let’s step through the process of extracting a report from the catalog, then editing, testing, and saving the report back to the catalog. (This process assumes the user has sufficient rights to retrieve and edit reports.)

Again, click the Files icon in Report Builder, and select Open. The Open Report dialog box that opens will display the home directory of the report catalog. Note that users will be able to see only the catalog elements to which they have rights. Select a report from the catalog to view and manage. Once the report’s RDL is fetched from the SSRS catalog, Report Builder shows a design view of the report.

In my example, which Figure 5 shows, Report Builder populated the report Parameters collection, as well as the data sets used by the report. In this report, the RDL specifies three data sets—two of which are used to populate user-interface parameter pick-lists that let the user specify parameter values that subsequently focus the main report rowset query on the desired subset of the products table.

You can now make whatever changes you want to the report. When you’re ready to view the changes in the finished report, click Run to start the local (i.e., client-side) Report Processor that renders the report in Report Builder’s view window. The Report Processor generates the parameter UI just as it would appear if the report were generated by SSRS and displayed in a browser.

Creating a locally hosted report. In some cases you might find it useful to save and retrieve reports without using the SSRS catalog. This approach is similar to the ReportViewer architecture but isn’t as intuitive. The process involves taking a side step out of the typical new report wizard process, to help Report Builder’s Report Processor find the report when you need it. (To learn about the differences between Report Builder and ReportViewer, see the sidebar “Report Builder vs. ReportViewer.”

Click the Files icon in Report Builder, and select New to open the Report Builder dialog box that contains the two wizard icons (i.e., Table or Matrix and Chart). Click the Table or Matrix icon to start building a locally-persisted report. (You can also use the Chart wizard to build a local report.)

The New Table or Matrix dialog box will open and prompt you for a data source (including opening a connection to an existing SSRS instance). The data source list is populated from the data source connections persisted in the system since the last time Report Builder ran. If you wanted to use a server-hosted report, you’d click Browse to scroll through the SSRS catalog. To create a new locally hosted report, click New to create a new data source.

The Data Source Properties dialog box that opens is used to build a SQL Server (or any other data source) connection string. Select the appropriate connection type (e.g., Microsoft SQL Server, OLE DB, Microsoft SQL Server Analysis Services) from the drop-down menu, and click Build for the .NET Data Provider to launch the typical connection dialog box. Select the appropriate data source and click OK to create a connection string.

To set the credentials in the connection string, click Credentials and select the type of authentication for the Report Processor to use to open the database connection, as Figure 6 shows. It’s preferable to use SQL Server for reports to make sure the reports can’t execute SQL queries or Data Manipulation Language (DML) operations that they shouldn’t. Keep in mind that end users can select the Use current Windows user setting, which could open your system to Trojan attacks. Click OK to accept the constructed connection string and credentials settings.

When the New Table or Matrix report wizard reopens with the newly created data source selected for this report, click Next to proceed to constructing the report. This action opens the Design a query dialog box. Note that the Database view pane is populated with only those database objects (e.g., tables, views, stored procedures) to which the authenticated user has rights.

From this point forward in the wizard you can simply follow the same pattern as in the Visual Studio BI or ReportViewer control report design wizards. You need to select the correct columns to aggregate or upon which to group rows or (for matrix reports) columns. For instructions on actually creating a report, see Peter Blackburn and William R. Vaughn, Hitchhiker’s Guide to SQL Server 2000 Reporting Services (Addison Wesley, 2004).

Deploy Cautiously
Microsoft reworked its original incarnation of Report Builder into an entirely different application. In fact, Report Builder 2.0 bears so little resemblance to the original tool that Microsoft should have considered renaming it, rather than carrying over the negative associations DBAs might have with Report Builder 1.0. Although Report Builder is valuable and generally quite easy to use, using the tool to alter existing deployed reports is somewhat difficult. In addition, DBAs and managers should conduct a rigorous security review before deploying the application.