Microsoft SQL Server 2005 : Report Definition and Design (part 5) - Report Builder

1/11/2011 4:30:12 PM

Report Builder

Report Builder is an ad hoc reporting tool delivered as a .NET 2.0 ClickOnce application that a user downloads from the report server. Predesigned report templates are used as a design surface, with drag-and-drop placeholders for data. The data is presented as a report model with predefined relationships and column attributes. Users can sort, group, filter, chart, and summarize (with limited support for formatting and formulas).

Ad Hoc Reporting

Report Builder is designed for non-technical business users who understand the data but not how to use SQL statements to build complex queries. All reporting must be done through report models that mask the underlying table relationships. Ad hoc reporting is the primary benefit of this tool. You can save the reports to the report server or to a local file. Both locations allow re-editing of the report definition. Once a report is modified by another tool (such as Report Designer), Report Builder can no longer open it.

After the tool downloads, the user is asked to select a report model. The list will include all models on the report server for which the user has permissions. The user must also select a template: table, matrix, or chart. There is no free-form reporting or list capability. The templates are restricted so that only a small subset of formatting properties are available. There are no expressions, code windows, or wizards. The user picks fields from the report model and drops them into labeled locations on the template. The result is a basic but useful report layout like the matrix shown in Figure 23.

Figure 23. Report Builder in Design Report mode with a matrix template

Report Builder offers clever client-side sorting and filtering tools. The filter clause appears on the bottom of each report, which is very handy. The completed report can be run directly in Report Builder, as shown in Figure 24.

Figure 24. Report Builder in Run Report mode with a matrix report

Report Builder has limited appeal to professional designers. Savvy business professionals already familiar with reporting capabilities in Microsoft Excel and Access will be more comfortable with those tools. You can use Report Builder to start a simple report that will be finished by a professional. (The tool would save little overall development time.)

Predefined Report Models

Report Builder relies on report models published to the report server. Models are a semantic description of business data. This means that the table and field names might be different from the underlying data. The real value of this is that it allows business users to work with familiar terms. Models also hide the relationships and joins required for complex relational queries.

Report models have their own project type in Visual Studio. The Chap21 solution includes a project called Models that contains a single model. The project has three folders. The Data Sources folder stores the pointer to a data connection. It uses a slightly different dialog box (Figure 25).

Figure 25. Report Model Data Source Designer dialog box

Data sources also use a slightly different dialog box for data connections (Figure 26).

Figure 26. Report Model Data Source Connection Manager dialog box

Once the data source is set, you can create a data source view, which is basically a subset of the tables and columns available in the underlying data source. One project can have many data source views. Data source views have a graphical relationship editor (Figure 27).

Figure 27. Report model data source view

There can be multiple diagrams in the Diagram organizer on the left side of the designer. Table relationships are shown in the center panel. You can create logical keys and relationships even if the underlying data does not define primary and foreign keys. This is useful when you report from legacy systems in which database changes are not possible.

Report models are created from a data source view. One project can have many models. The models sift through the data by doing repetitive queries, and they determine the cardinality and constraints of the data to create a map of possible entities and attributes tuned to the data. For instance, numeric columns have a predefined total, minimum, maximum, and average. The business user can later pick these logical fields directly without knowing SQL query syntax. Other column types, such as dates, automatically create fragments such as month, quarter, and year. Model designers can tweak and customize the entities and attributes to create a more meaningful description of the data. The finished report model has attributes and roles, as shown in Figure 28.

Figure 28. Completed report model with attributes and roles

Keep in mind that this is not an Analysis Services cube that calculates and stores aggregate values. Instead, it is a map that a business user can use when building a report definition.

Completed models are deployed to the report server. Report Manager and SQL Management Studio include tools that automatically generate models based on data source schemas. You cannot edit field or table names, but this is a quick way to get started with models. In Report Manager, open the properties page for a data source and click the Generate Model button.

The Reporting Services models are stored in an XML file with the Semantic Model Definition Language (SMDL) extensions. They do not use Universal Modeling Language (UML)—they have their own syntax and personality.

Report Definition Language

The report definition file format is an XML grammar controlled by an XML Schema Definition (XSD) and namespace that serve as the blueprint for a report. Use the Visual Studio Solution Explorer to view code for a report definition, or open the RDL file in any XML editor.

The root of the XML document is the <Report> element. There are nested sections for <DataSources>, <Body>, and <DataSets>. Each <DataSets> section has a <Query> element and a <Fields> collection. <Body> has a <ReportItems> collection containing <Table>, <Matrix>, <List>, or <Chart> elements. Each of these elements has child elements that define the numerous settings and properties required to render a report.

The XML is well documented and easy to generate using some clever XML coding techniques. Because so many graphical tools (such as Report Designer and Report Builder) already produce well-formed file definitions, there is little reason to programmatically generate your own XML for anything other than a coding exercise.

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone