May 26, 2019
Hot Topics:

Introduction to SQL Server Report Builder

  • July 14, 2005
  • By Mike Gunderloy
  • Send Email »
  • More Articles »

To the left, the Explorer window shows all of the tables in the report model. Beneath that, the Fields window shows the entities in the selected table. Note that not everything in this window is a column in the table; the report model also creates aggregate entities such as Sum List Price and calculated fields such as Sell End Year. In the center of Report Builder you see the report layout area where you build the actual report. To the right is the Report Layout gallery, where you can select the default tabular report, a matrix report, or a chart report.

Note that Report Builder also installs its own extensive help when it installs itself. This help remains available on the end user's computer, and explains the basic concepts behind Report Builder. It also contains a great deal of how-to information for the analyst who wants to construct reports. (As of the June 2005 CTP build of SQL Server, the Report Builder help file is still quite unfinished).

Building a Report

The Report Builder designer is a drag-and-drop environment. To use it, you drag entities from the field list and drop them on the design surface. For example, with the Products table selected, drag the Name field and drop it in the area marked Drop Column fields. Then double-click on Products in the Explorer window to show related child tables. Select the Sales Order Details table, then drag the Sum Order Qty field and drop it to the right of the Name field. Click the Run Report toolbar button to produce the report shown in Figure 3.

A simple report based on two tables

Note that you can adjust some of the report's properties interactively even while you're viewing its data. For example, click the Sort and Group button to select up to two sorting and one grouping fields. I chose to sort by Sum Order Qty descending. Changes you make to sorting, grouping, and filtering are saved with the report definition; they're not merely temporary.

Publishing a Report

You can, if you like, use Report Builder strictly as a tool for exploring data on your own desktop. But you can also use it to build reports that are shared with others in your own organization. That's because Report Builder can easily make the loop back to Reporting Services by publishing its reports to your Reporting Services server.

To publish a report, first use Report Builder to tweak the design to your liking. Then select Save from the File menu. The default save location is the home folder on the Reporting Services server that hosts the data model that you're working with. Give your report a name, click OK, and it will be available to anyone else who can browse to the Reporting Services home page.

After you publish a report, it's completely independent of Report Builder. Other users can open it directly in the Reporting Services browser-based interface, as shown in Figure 4.

A report in Reporting Services

Going Beyond the Basics

As I promised in my title, that's the barest of introductions to Report Builder - just enough to give you a hint of its promise. But there's a lot more to this polished tool. In addition to tabular reports, it can build crosstabs and charts. The design environment offers you full control over formatting, fonts, colors - indeed, the entire range of look and feel properties for your reports. You can export reports to a variety of formats, including XML, CSV, and PDF. You can even create "ClickThrough Reports," where clicking on data unfolds more detailed reports with additional information.

With the introduction of Report Builder, Microsoft has added a well-designed end-user reporting capability to SQL Server. By basing reports in explicit data models, Report Builder overcomes the objections that DBAs have to completely ad hoc tools that can drag down the performance of a database. By making the report design and formatting engine easy to use and flexible, Report Builder allows end users to see data the way they want it, without waiting for overburdened DBAs and developers to respond to change requests. It's a big win all around, and I'm looking forward to seeing it deployed in action.

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his latest books, Coder to Developer and Developer to Designer, both from Sybex. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.

Page 2 of 2

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date