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.