Report Elements | Microsoft SQL Server 2005 Reporting Services

To create a report, you need to know a few things:

Where and what is your source data?
What is the report layout?
Are there any other properties, such as external images or parameters?

To cover this much information, the RDL schema has many elements. The RDL specification (schema) itself is an open schema, and Microsoft fully expects third parties to add onto it to extend it. In the scope of this book, it would be very time consuming and arduous to cover every element, so this book covers just a few main elements. For more information, the RDL specification is available for download on the Microsoft website at the following location:

http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx

You can also view the XML of any report, by opening the report in SQL Server Business Intelligence Studio and selecting the View, Code menu while in Design view. Alternatively, you can view a report's XML by right-clicking the report in Solution Explorer and selecting View Code from a drop-down menu.

Report Element

The Report element is the highest-level element in the RDL XML hierarchy. The Report element contains all the information needed to process the report. There can be only one Report element in every report. In fact, every other element is a child node of the 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:

[View full width]

  [View full width] 
 <?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"  xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">   <DataSources></DataSources>   <BottomMargin>1in</BottomMargin>   <RightMargin>1in</RightMargin>   <InteractiveWidth>8.5in</InteractiveWidth>     <Body>     <ReportItems>       <Table Name="table1"></Table>     </ReportItems>     <Height>2in</Height>   </Body>   <LeftMargin>1in</LeftMargin>   <DataSets></DataSets>   <Width>6.5in</Width>   <InteractiveHeight>11in</InteractiveHeight>   <Language>en-US</Language>   <TopMargin>1in</TopMargin> </Report>

Report Parameters

This element lists the parameters for a report (see Table 7.1). This is an ordered list of ReportParameters elements.

Table 7.1. Report Parameters

Element Name	Required or Optional	Type	Description
Name	Required	String	Unique name of the parameter (the value of this element is used when other expressions need to refer back to this parameter). Note: Parameter names must be unique within the ReportParameters parent element.
DataType	Required	Enum	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 supplied by the end user . If the Prompt element is omitted, the value is null. DefaultValue becomes required when there is no Prompt element and when either Nullable is False or a ValidValues element exists that does not contain Null (an omitted value).
AllowBlank	Optional	Boolean	Whether an empty string is an acceptable value for the parameter. The default value is False, and the element is ignored if the DataType is not String.
Hidden	Optional	Boolean	The element that determines whether a user should be prompted to enter a parameter. If this element is false, the user interface prompts a user to enter the parameter.
Prompt	Optional	String	The element that designates the text that the user interface should display when prompting the user for parameter values. If the element is omitted, the user is not prompted for a parameter's value and additionally the parameter cannot be modified any other way. For example, it can't be modified through URL Access. Report Designer does not allow this element to be blank. Manual RDL editing and setting this element to blank or removing this element have a similar effect to the Hidden element being set to true.

Note

Because all XML is character based, technically, any data type (Type) that is specified in Table 7.1 and further is a String. To be more specific about a range of possible string values, this book generally uses acceptable descriptions. For example, Boolean indicates that the string value could be true or false .

DataSets

The DataSets element is a collection of individual DataSet elements (see Table 7.2). As a whole, the collection contains information about how to get the data used in the reports . Each individual DataSet element has to have a unique name element. The DataSet element itself contains elements for basic properties, such as AccentSensitivity, CaseSensitivity, Collation, and so on.

The actual database query is contained in the Query element. Each data set can only have one query. When using the Query element, you can see some of the influences of the .NET Framework, particularly ADO.NET. The child elements are 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 database 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 element should contain an expression used to calculate a field. In the designer, this shows up as a calculated value. An example of the Fields collection follows :

 <Fields>   <Field Name="ProductID">     <rd:TypeName>System.Int32</rd:TypeName>     <DataField>ProductID</DataField>   </Field>   <Field Name="Name">     <rd:TypeName>System.String</rd:TypeName>     <DataField>Name</DataField>   </Field> </Fields>

In a lot of cases, a database query or stored procedure returns more information than most readers would like or need. In this case, you can apply a filter to the data set through the Filters 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. 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">     <Query>       <CommandText>SELECT     ProductID, Name OM          Production.Product</CommandText>       <DataSourceName>DataSource1</DataSourceName>     </Query>     <Filters>       <Filter>         <Operator>Equal</Operator>         <FilterValues>           <FilterValue>=Cint("866")</FilterValue>         </FilterValues>         <FilterExpression>=Fields!ProductID.Value</FilterExpression>       </Filter>     </Filters>     <Fields>       <Field Name="ProductID">         <rd:TypeName>System.Int32</rd:TypeName>         <DataField>ProductID</DataField>       </Field>       <Field Name="Name">         <rd:TypeName>System.String</rd:TypeName>         <DataField>Name</DataField>       </Field>     </Fields>   </DataSet> </DataSets>

Table 7.2. DataSet Elements

Name	Required/Optional/or Multiple	Type	Description
Name	Required	String	Unique name given to the data set. This cannot be the same name given to any data region or grouping.
Fields	Optional	Element	List of fields that are included in the data set. They can map to columns in the data set.
Field	1-N	Element	The name of the field as it is referred to within the report.
Query	Required	Element	Information used to gather data from the data source. This parameter includes connection information, query text, query parameters, and so on required to get the data from the data source.

Report Items

Report items define the contents of the report. They are under the PageHeader, Body, and PageFooter elements. Report items contain user interface elements, such as Tables, Matrixes, Images, Lines, SubReports, Lists, and Rectangles. Because SSRS allows you to nest controls, report items can also be found 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 section. The Action, Visibility, and Drill Through elements all aid in reporting interactivity. Generic RDL of a report item that contains some common elements is shown in the following code listing. {REPORT ITEM} abbreviates any report item, such as Textbox, Table, and so on.

 <ReportItems>         ...          <{REPORT ITEM} Name="...">                 <Style>...</Style>                 <Top>...</Top>                 <Left>...</Left>                 <Height>...</Height>                 <Width>...</Width>                 <ZIndex>...</ZIndex>                 <Visibility>...</Visibility>                 <ToolTip>...</ToolTip>                 <Bookmark>...</Bookmark>                 <RepeatWith>...</RepeatWith>                 <Custom>...</Custom>                 <ReportItems>...</ReportItems>                 <PageBreakAtStart>...</PageBreakAtStart>                 <PageBreakAtEnd>...</PageBreakAtEnd>          </{REPORT ITEM}>         ... </ReportItems>

Table 7.3 shows explanations and types of common ReportItem elements.

Table 7.3. Common ReportItem Elements

Name	Required/Optional	Type	Description
Name	Required	String	The unique name given to the report item.
Style	Optional	Element	The style information, such as padding, color , font, and so on for the element.
Action	Optional	Element	An action such as a bookmark link or a drillthrough action that is associated with the ReportItem. This aids in making reports interactive.
Top	Optional	Size	The distance of the report item to the top of the containing object. If excluded, the value becomes 0 in.
Left	Optional	Size	The distance of the report item to the left of the containing object. If excluded, the value becomes 0 in.
Height	Optional	Size	The vertical size of the item. If omitted, the value defaults to the height of the containing object minus Top value.
Width	Optional	Size	The lateral size of the item. If omitted, the value defaults to the width of the containing object minus Left value.
PageBreakAtStart	Optional	Boolean	The instruction to the Report Server to put a page break before a report item.
PageBreakAtEnd	Optional	Boolean	The instruction to the Report Server to put a page break after a report item.
Visibility	Optional	Element	An action to specify initial visibility of an item and a toggle trigger item for the Visibility.

You can find additional information, including more discussion about RDL, in Chapter 11, "Working with Report Items."

Data Regions

Data regions are the primary mechanism used to display data and a base class of controls that generate repeating content based on a data in a data set. Four basic elements inherit from a data region: Chart, Table, List, and Matrix. Each data region is unique in its own way and, as such, has many of its own specialized elements and attributes. Because all of the data regions display data, all have the <DataSetName> tag. For more information on the specifics of data regions, please see Chapter 11.