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.
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.
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).
Data sources also use a slightly different dialog box for data connections (Figure 26).
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).
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.
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.
