ptg
131
Generating Reports with Visual Studio
8
Design Screen
After selecting your data, the next step is to design the presentation for the data. Layout
assistance is provided by the Toolbox, Report Data, and Design dockable windows.
Design view is a default that BIDS displays after you add a report (see Figure 8.11).
The Toolbox (shown in Figure 8.11) is accessible by selecting Toolbox from the View
menu. This has all the report item controls you might use while authoring reports. The
simplest way to use them is to drag the control you want onto the body of a report.
Data can be inserted from the Report Data window. (Just choose Report Data from the
View menu.) The Report Data window contains a tree list of available fields to use from
the data sets. If you drag a field onto the report body, BIDS creates a text box to contain
the field.
Any item on the report can be modified through the Properties window. The Properties
window can be accessed by pressing F4 or by selecting Properties Window from the View
menu. Figure 8.11 shows the Properties window for a report object.
Categorized
Alphabetical
Property pages
Properties
window
Properties
toolbar
Toolbox
Report data Main menu
Design tab
Preview tab
Report body
Context help/tip for a property

wide. Click the bar above each column; if selected successfully, the entire column
should be selected. After a column is selected, you can use the Properties window
and change the Width property to fit the information you are going to display. You
can do this for each column individually or select columns you want to change to
the same size by pressing and holding Ctrl key and selecting multiple columns.
NOTE
In SSRS 2008, the Table report item is actually represented by a new Tablix data region.
Tablix represents both Table and Matrix report items. This is why some of the menu
items and dialog boxes use Tablix even though we work with the Table report item.
3. From the Report Data window, drag the AdventureWorkDataset fields
(CountryRegionName, TerritoryName, and FirstName) to the first three columns of
the table. Note that when you drag a field over a column the entire column is
selected. Report Designer will add a value of a field to a data row of the table and the
name of a field to the header of the table. When adding a field name to table’s
header, the Report Designer will also insert spaces before each capital letter in the
field’s name.
4. To add more columns to the table, you can select the rightmost column of the table,
right-click the column, and select Insert Column, Right (see Figure 8.12). Repeat
three times. The table should now have six columns.
5. Alternatively you can continue dragging fields from the data set to the table, posi-
tioning your cursor where you want the column to be added, similar to Figure 8.13
where we add a column to the end of the table. Note how the cursor changes to
indicate an addition to the table.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
133
Generating Reports with Visual Studio
8
FIGURE 8.12

10. Click anywhere on the table, and then right-click the button at the upper-left corner
of the table.
11. Select Tablix Properties from the context menu.
12. Now display the Sorting tab.
13. Click the Add button three times to add three columns to sorting. Select the follow-
ing columns for sorting: CountryRegionName, TerritoryName, and LastName. Leave
the default sorting order A to Z (see Figure 8.14).
FIGURE 8.14
Adding columns for sorting.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
135
Summary
8
14. Click OK to complete the sorting assignment, and display the Preview tab to see
what the report looks like (see Figure 8.15).
This concludes the starter report. Preview mode has full report-viewing capabilities and
allows navigating multipage reports and exporting a report to a different format (such as
XML, Word, and Excel). Preview mode also has print capabilities (such as print preview
and page setup).
Summary
BIDS is nothing more than the shell of Visual Studio. Both products house the main
report development tool from Microsoft: SQL Server Report Designer. Over the next
several chapters, you will see how to use Report Designer to develop powerful and visually
appealing reports for all kinds of end users. You’ll learn more about client-side ad hoc
reporting in Chapter 18.
Find control: text
to find, find first,
find next

T
he preceding chapter introduced you to Report Designer
and Report Builder. This chapter takes a look at the output
from these tools.
SSRS is unique in that it uses Extensible Markup Language
(XML) to generate reports. This chapter provides an insight
into why Microsoft uses XML as their report-generation
language, and then delves into the structure of the result-
ing document.
Language: A Way to Communicate
At first glance, the name Report Definition Language (RDL)
might appear to be a misnomer. As you’ve already seen, it is
nothing more than an XML document, just like any other
XML document. Why would they call this a language? After
all, there is no compiler necessary, and the syntax is
nothing like C++, C#, or any other programming language.
To answer this, you need to think of things at a bit higher
level than most programming paradigms allow.
Remember, one of the goals of SSRS is to remain an open-
ended environment.
Likewise, as you have already seen, SSRS is composed of
many different components: There is the database server, the
Windows Service, the Report Server Web Service, and so on.
Most important, at least from an end-user perspective, are
the report designers and the rendering engine. As you
have seen, Microsoft already offers three designers: Report
Designer and Report Builder 1.0 and 2.0.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg

in the document a set of XML nodes should appear, and which nodes should have attrib-
utes describing them.
The RDL specification is the XML schema that describes the layout of the XML used to
create reports. The XML itself basically becomes the programming language of the report.
Declarative Programming
Just as a programming language lets a programmer tell a computer how and what to
produce to the end user, the RDL tells the Report Server what data to use and how to lay it
out. Now, there is a little trick here: Most programming languages communicate a what
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
139
Report Elements
9
and a how to do something. There is no way to tell ASP.NET to produce a web page just by
giving it a template. However, that is what the RDL does. The RDL communicates what
the output is to look like and where the source data is to come from. This leaves the appli-
cation free to decide how to generate the defined look and feel, regardless of the program-
ming language or underlying architecture. This model is called the declarative model.
A producer application is an application that is used to generate RDL files. Business
Intelligence Development Studio (BIDS) and Report Builder fall into this category. For
most users, it is helpful to have a graphical user interface (GUI), although you can develop
a report purely in your favorite text editor.
A consumer application is simply one that takes the RDL from the producer and creates
the desired output. In its simplest form, it queries for the data and displays the results in
the specified format. This is where a lot of the custom elements come in. Using the
custom elements, it is possible to send instructions for one output format, which could
then be ignored by all others.
Report Elements
To create a report, you need to know a few things:

Report
element. Examples of these child elements include
PageHeader
,
Body
,
PageFooter
,
DataSources
,
DataSets
, and
Parameters
.
The following code listing shows an example of the
Report
element. The RDL is of an
empty report with a
Line
report item:
<?xml version=”1.0” encoding=”utf-8”?>
<Report xmlns:rd=”http://schemas.microsoft.com/SQLServer/reporting/reportdesigner”
xmlns=”http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition”>
<Body>
<ReportItems>
<Line Name=”Line1”>
<Top>0.0175in</Top>
<Height>0.25in</Height>
<Width>1in</Width>
<Style>

...
<xsd:element name=”ReportParameters” type=”ReportParametersType” minOc-
curs=”0” />
...
<xsd:complexType name=”ReportParameterType”>
<xsd:choice minOccurs=”1” maxOccurs=”unbounded”>
<xsd:element name=”DataType”>
<xsd:simpleType>
<xsd:restriction base=”xsd:string”>
<xsd:enumeration value=”Boolean” />
<xsd:enumeration value=”DateTime” />
<xsd:enumeration value=”Integer” />
<xsd:enumeration value=”Float” />
<xsd:enumeration value=”String” />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name=”Nullable” type=”xsd:boolean” minOccurs=”0” />
<xsd:element name=”DefaultValue” type=”DefaultValueType” minOccurs=”0” />
<xsd:element name=”AllowBlank” type=”xsd:boolean” minOccurs=”0” />
<xsd:element name=”Prompt” type=”StringLocIDType” minOccurs=”0” />
<xsd:element name=”ValidValues” type=”ValidValuesType” minOccurs=”0” />
<xsd:element name=”Hidden” type=”xsd:boolean” minOccurs=”0” />
<xsd:element name=”MultiValue” type=”xsd:boolean” minOccurs=”0” />
<xsd:element name=”UsedInQuery” minOccurs=”0”>
<xsd:simpleType>
<xsd:restriction base=”xsd:string”>
<xsd:enumeration value=”False” />
<xsd:enumeration value=”True” />
<xsd:enumeration value=”Auto” />

, or
String
.
Graphical report designers, such as Report Designer in BIDS, map elements to some graph-
ical presentation. Figure 9.1 presents the General tab of the Report Parameter Properties
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
142
CHAPTER 9 Report Definition Language
FIGURE 9.1
Report Parameter Properties dialog box.
dialog box. Here you can clearly see a mapping between
ReportParametersType
and
graphical elements:
Name
,
Prompt
,
Data Type
, and so on.
Armed with an understanding of the RDL, you can write your own graphical report
designers if you so choose. SQL Server Books Online have an example of such a generator
under the title “Tutorial: Generating RDL Using the .NET Framework,” which is also avail-
able online at http://msdn.microsoft.com/en-us/library/ms170667.aspx. The RDL genera-
tor in the example simply uses .NET’s
System.Xml.XmlTextWriter
class to stream RDL
elements to a file.

Parameter names must be unique within the
ReportParameters
parent element.
DataType
Required
Enumeration
Programmatic data type of the parameter.
Because it is a required value, there is no
default.
Boolean
|
DateTime
|
Integer
|
Float
|
String
.
Nullable
Optional
Boolean
Whether the value of the parameter can be
null. Defaults to
False
if excluded.
DefaultValue
Optional
Element
Value used for the parameter when not

Determines whether a user should be
prompted to enter a parameter.
If this element is
False
, the user interface
will prompt a user to enter the parameter.
NOTE
Because all XML is character based, technically, any data type is a string. To be more
specific about a range of possible string values, this book generally uses acceptable
type names (Type column in the Table 9.1). For example,
Boolean
indicates that the
string value could be
True
or
False
.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
144
CHAPTER 9 Report Definition Language
DataSets Element
The
DataSets
element is a collection of individual
DataSet
elements (see Table 9.2). As a
whole, the collection contains information about how to get the data used in the reports.
Each individual

}
Default =
Auto
. If a
ParameterA
is included
in an expression or a query for a
ParameterB
, this element determines how to
handle a refresh of a default value for
ParameterB
when
ParameterA
changes.
Auto
relies on SSRS to determine whether a
dependency exists.
ValidValues
Optional
String
Helps to improve security when you define a
parameter of type
String
. If you do not use
this option, your report may be vulnerable to
a SQL injection attack.
Prompt
Optional
String
The

CommandText
,
CommandType
,
DataSourceName
,
QueryParameters
, and
Timeout
.
The
Fields
collection contains
Field
elements. In an online transaction processing
(OLTP) system, the
Fields
collection usually maps to the columns returned by your data-
base query. There is also the ability to add calculated fields. The field name is the name
referenced in the layout sections of the report. The
Field
element must have either a
DataField
child element or a
Value
child element, but not both. As you might have
guessed, the
DataField
simply maps to a query column. A
Value

Filter
expression (such as
=Fields!ProductID.Value
), operator (such
as
Equal
), and a value of one of a data types
(such as
Integer
).
Interpret
Subtotals
AsDetails
Optional
String
Restricted to
True
,
False
, or
Auto
. Directs
whether to interpret results of a query that
returns subtotals as detail rows instead of
aggregate rows. Subtotals are interpreted as
detail rows when this element is set to
True
and the report does not use the
Aggregate()
function to access any fields in the data set.

collection. Each individual
Filter
element contains a collection of
FilterExpression
,
Operator
, and
FilterValues
. Basically, for every row in the data set,
the report-processing engine is responsible for evaluating the expression against that row
and using the operator to compare it to the list of values. So, keep in mind that depending
on the expression, this can be time-consuming.
The following code listing displays an example of the
Query
and
Filter
elements:
<DataSets>
<DataSet Name=”AdventureWorks”>
<Fields>
<Field Name=”ProductID”>
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name=”Name”>
<DataField>Name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>

(when text is accent sensitive, the character
’a’
is
not equal to the character
’ã’
),
WidthSensitivity
(determines whether single-byte and
double-byte representation of the same character is identical), and
KanatypeSensitivity
(sensitivity to two types of Japanese characters).
...
<xsd:element name=”DataSets” type=”DataSetsType” minOccurs=”0” />
...
<xsd:complexType name=”DataSetsType”>
<xsd:sequence>
<xsd:element name=”DataSet” type=”DataSetType” maxOccurs=”unbounded” />
</xsd:sequence>
<xsd:anyAttribute namespace=”##other” processContents=”skip” />
</xsd:complexType>
...
<xsd:complexType name=”DataSetType”>
<xsd:choice minOccurs=”1” maxOccurs=”unbounded”>
<xsd:element name=”Fields” type=”FieldsType” minOccurs=”0” />
<xsd:element name=”Query” type=”QueryType” />
<xsd:element name=”CaseSensitivity” minOccurs=”0”>
<xsd:simpleType>
<xsd:restriction base=”xsd:string”>
<xsd:enumeration value=”True” />
<xsd:enumeration value=”False” />

, and
PageFooter
elements.
ReportItems
contain user interface elements, such as
Tablix
,
Image
,
Line
,
Subreport
, and
Rectangle
. Because SSRS allows you to nest controls, you can also
find report items within other report items. Each report item must contain at least one
child element.
Because many elements inherit from a report item, it is advantageous to be familiar with
the shared properties. These are mostly related to presentation.
Height
,
Width
,
ZIndex
,
Top
,
and
Left
are all used to size and position an item. Each report item can have its own style

<CustomProperties>...</CustomProperties>
<ActionInfo>...</ActionInfo>
<ReportItems>...</ReportItems>
<DataElementName>...</DataElementName>
<PageBreakAtStart>...</PageBreakAtStart>
<PageBreakAtEnd>...</PageBreakAtEnd>
</{REPORT ITEM}>
...
</ReportItems>
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
149
Report Elements
9
Table 9.3 describes some common elements of a report item.
You can find additional information, including more discussion about RDL, in subse-
quent chapters.
TABLE 9.3
Common
ReportItems
Elements
Name Required or
Optional
Type Description
Name
Required
String
Unique name given to the report item.
Style

value.
Width
Optional
Size
The lateral size of the item. If omitted, the
value defaults to the width of the containing
object minus the
Left
value.
PageBreakAtStart
Optional
Boolean
Instructs the Report Server to put a page
break before a report item.
PageBreakAtEnd
Optional
Boolean
Instructs the Report Server to put a page
break after a report item.
Visibility
Optional
Element
Specifies the initial visibility of an item and a
toggle trigger item for the visibility.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
150
CHAPTER 9 Report Definition Language
Data Regions