The Basic Functionality of Report Designer

In the recent installments of our series dedicated to the most prominent
features of SQL Server 2005 Express Edition, we have been discussing its
reporting capabilities. So far, we have presented its most basic
characteristics, stepped through its installation procedure, and demonstrated
creation of a sample report using the Report Project Wizard incorporated into
Business Intelligence Development Studio. In this article, we will illustrate
another approach to generating reports, relying on the Report Server Project
template, offering a considerably wider range of flexibility than its
wizard-driven counterpart does.

Regardless of the choice of a template, the ability to produce reports
relies on the Report Designer component of Reporting Services, which
constitutes the only built-in mechanism providing this functionality in SQL
Server 2005 Express Edition. (Full-fledged editions include an alternative
solution in the form of the Report Builder, which facilitates ad-hoc reporting
carried out by end users). Its implementation is fully integrated with Business
Intelligence Development Studio and, consequently, all of its actions
(including publishing a final product to the Report Server Web site) are
performed within the same interface (consistent with other Microsoft Integrated
Development Environment products). Report Designer yields a report definition
(.rdl) file in an XML-based Report Definition Language format, that identifies
the data source of a target report (based on underlying query), as well as its
visual characteristics, such as layout or formatting.

In the previous article, we have described a wizard-driven process of
creating a report based on the query described in the Sales and
Marketing Scenario
MSDN article, allowing you to view address data of
individual customers recorded in AdventureWorks database. Now we will produce a
similar outcome without resorting to the help of a wizard, in order to gain
more familiarity with options available in the Business Intelligence
Development Studio interface.

Start by creating a new project (via the New
Project...
menu) based on the Report Server Project template.
This should reveal the Solution Explorer window with two subfolders labeled Shared Data Sources and Reports. From the context sensitive menu
of the latter, select the Add New Item...
entry. In the resulting Add New Item
dialog box, choose Report
template and type its name (e.g. Individual
Customer Addresses.rdl
). Clicking on Add will display an empty, centrally
located area representing the newly created RDL file (along with a
corresponding Datasets window), with three tabs labeled Data, Layout,
and Preview. The next step
involves exposing data necessary to generate our report.

The underlying query combines data extracted from Person.Contact, SalesIndividual, Sales.CustomerAddress, Person.Address, Person.CountryRegion, and Person.StateProvince tables of the
AdventureWorks database (as described in the MSDN article we have referenced).
In order to make it available, we have to define a corresponding dataset
(consisting of a data source and a query identifying records that our report
will be based on). This is done via the Dataset
listbox on the Data tab of
the report window within the Business Intelligence Development Studio. After
selecting its <New Dataset...>
entry, you will be presented with Data
Source
dialog box, where you need to assign dataset name, its
type (Microsoft SQL Server),
and a corresponding connection string. The easiest way to set the last of these
parameters is by pressing the Edit...
command button, which activates the Connection
Properties
dialog box, where (on the General tab) you can simply point to the
local server and SQL Server 2005 Express Edition instance, ensure that Windows
Authentication is used to log on, and designate a database to connect to (AdventureWorks in our case). This should
result in the connection string in the format Data Source=localhostSQLExpress;Initial Catalog=AdventureWorks.
Verify that Use Windows Authentication
(Integrated Security)
is selected on the Credentials tab and click on OK to
return to the Data tab of
our report.

At this point, you are ready to designate which data records are in scope.
This can be accomplished by employing either the default generic query designer
or its graphical equivalent. In the case of the former, you would simply copy
and paste the query as it is presented in the MSDN article (with the Command type listbox set to Text). To use the latter, click on a
toggle button in the toolbar, which will automatically alter the active window,
splitting it into four horizontally stacked panes, intended, respectively, for
tables, a grid containing selected columns (along with their aliases,
filtering, and ordering parameters), a corresponding T-SQL statement, and
results of its execution. The extended toolbar exposes a number of extra
command buttons. Use the rightmost one to add required tables, including Address (Person), Contact (Person), CountryRegion (Person), CustomerAddress (Sales), Individual (Sales), and StateProvince (Person) to the newly
created dataset. Next, select checkboxes appearing to the left of each of the columns
listed in the SELECT statement, namely Individual
(Sales).CustomerID
, Contact
(Person).LastName
(with Ascending
Sort
type and Sort Order
of 1), Contact (Person).FirstName (with Ascending Sort type and Sort Order of 2), Address
(Person).AddressLine1
, Address
(Person).City
, StateProvince
(Person).Name
, Address
(Person).AddressLine1
(aliased as StateProvince), and CountryRegion
(Person).Name
(aliased as CountryRegion).
Once the query is completed, you can verify its syntactical correctness and
execute it to confirm that it generates the desired output (ensure that all
relevant foreign key constraints appear in the diagram in the top window pane
before you do so). This step completes the data-specific portion of configuring
the report definition file within our project.

We are finally ready to tackle the visual aspects of the report. This
process involves defining its layout, consisting of static and data-bound (also
known as data regions) elements. As we mentioned, our goal is to produce an
outcome equivalent to the one generated by the Report Project Wizard we
described in the previous article of this series (listing address data for
individual customers of AdventureWorks in alphabetical order, based on last and
first names, and grouped according to country and region). To accomplish this,
we need to begin by switching to the Layout
tab of our report definition window within the Business Intelligence
Development Studio, activating the Toolbox window (by either selecting its
entry from the View menu or pressing the Ctrl+Alt+X
key combination), and enabling Layout, Report Borders, and Report Formatting
toolbars.

To create a report label appearing on the initial page of the report, select
the Textbox item in the Toolbox, drag it over to the top of design surface, and
extend its size to accommodate whatever descriptive name you come up with (e.g.
Individual Customers of AdventureWorks).
Modify its appearance as needed via the Properties window (or via Report
Formatting toolbar). To configure a top level grouping (based, in our case, on
country or region) add the List element (again, by dragging it from the
Toolbox) below the newly created textbox, size it appropriately to fill out the
rest of the design surface, and set its DataSetName
property to AdventureWorks.
Click on the ellipsis button of the Grouping
entry to trigger the display of the Grouping and Sorting Properties dialog box. In the Expression listbox
of the Group on section, click on the General tab, select =Fields!CountryRegion.Value and assign Ascending
order to it via the Sorting tab. The actual value representing each group
of records will appear in a textbox, which you drag from the Toolbox over to
the form and position at the top of the newly added list (make sure that its
name appears as the value of its Parent
property). Assign a descriptive name (e.g. txtCountryRegion)
to it and select =Fields!CountryRegion.Value
in the Value listbox. (Modify
its appearance as desired via Report Formatting toolbar or its Font properties).

Individual records returned by our query will be displayed in a table. Drag
its icon from the Toolbox to the lower portion of the form and assign AdventureWorks as the value of its DataSetName property. By default, the
table consists of three rows marked as Header,
Detail, and Footer. To accommodate the second level
grouping (based on the value of StateProvince
aliased column), we will add an extra row directly underneath the Header. To implement this, select Insert Group... item from the context
sensitive menu of a handle of the Detail
row (a gray box on its left hand side). In the resulting Grouping and Sorting Properties dialog
box, on the General tab,
type in a descriptive name (e.g. tblStateProvince),
select =Fields!StateProvince.Value
in the Expression listbox of
the Group on section, clear
the Include group footer
checkbox, and enable the Repeat group
header
option. Switch to the Sorting tab and add =Fields!StateProvince.Value to the Expression section with Ascending in the Direction column of Sort on section. Click on OK to finalize your choices.

To populate the Detail row,
we will use individual components of our AdventureWorks
dataset. In order to view them, activate the Dataset window, which should
contain seven entries corresponding to columns referenced in the SELECT clause of our query. Drag the
first one ( CustomerID )
from the Dataset window to the leftmost free column in the Detail row (note that this will
automatically populate its value with =Fields!CustomerID.Value
and enter Customer ID in the
Header row. Repeat the same
process with FirstName, LastName, AddressLine1 (change its header to Address), and City columns (use Insert Column to the Right option from
the context sensitive menu in the table to add extra columns whenever
necessary). In addition, to ensure that the header of our table will appear on
each page of the report, select the TableRow1
element (by clicking on its handle), and change its RepeatOnNext property to True.

Use the Preview tab of
the Report Designer to determine the outcome of our implementation. Obviously,
it is possible to further improve its appearance by taking advantage of other,
more advanced graphical designer features available within Business
Intelligence Development Studio. We will explore some of them by leveraging a
number of predefined sample reports (provided by Microsoft) in our next article
of this series.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles