Article
10/01/2010

Understanding Report Datasets (Report Builder 2.0)

You can visualize the data for a report dataset as a set of rows and columns. There must be at least one column and zero or more rows. At design time, you create a query that specifies the columns to retrieve from the data source. You are working with metadata at design time so that you can link dataset fields to data regions and report items on the design surface. At run-time, the report processor retrieves the actual data and combines it with the report layout. The report is then passed to a report renderer, which you use to view the report.

When you run a dataset query in a query designer at design time, you see a row set from the data source that shows you example data. At run time, when a user views the report, the dataset query may produce different values because the data on the data source has changed. Each time the report is processed, new data may appear.

Understanding Report Dataset Data

A report dataset can consist of the following types of data:

A result set from a relational database, which can result from running database commands, stored procedures, or user-defined functions. If multiple result sets are retrieved through a single query, only the first result set is processed, and all other result sets are ignored. For example, when you run the following query in the text-based query designer, only the result set for Production.Product appears in the result pane:
```
SELECT ProductID FROM Production.Product
GO
SELECT ContactID FROM Person.Contact
```
A flattened rowset from multidimensional data sources that use the XML for Analysis (XMLA) protocol. Some data providers supply additional cell and dimension properties from the data source that you cannot see in the result set but that are available in your report.
A flattened result set from XML data sources that include XML elements, their attributes, and their child elements.
A result set from any registered and configured .NET Framework data provider.
Data from a report model that has been designed for a specific data source, with predefined entities, entity relationships, and fields. For more information, see Creating Report Datasets for a Report Model (Report Builder 2.0).

When the report is processed at run-time, the actual result set returned for a query may have zero or more rows. It is also possible that the columns defined in the query may be missing from the data source. Null values from the data source are mapped to the .NET Framework value System.DBNull.Value.

Setting Data Properties

When you define a report dataset, you can set data properties in the query or accept the defaults set by the data provider. You can change a data type by using one of the following strategies:

Rewrite the dataset query to specifically convert a field to a different data type.
Edit the field in the dataset and provide a custom format.
Create a new custom field based on a database field and provide a custom format.

For data sources that support international data, you may need to adjust properties for a dataset that affect the sort order, international character properties, and whether to treat uppercase and lowercase characters as equivalent. These properties include case, kanatype, width, accent, and collation. For more information, see "International Considerations for Databases and Database Engine Applications" and "Working with Collations" in SQL Server Books Online. For more information about how to set these properties, see Dataset Properties Dialog Box, Options (Report Builder 2.0).

Understanding Data Types in a Dataset Field Collection

On the data source, the data is stored in data types supported by the data source. For example, data in a SQL Server database must be one of the supported SQL Server data types such as nvarchar or datetime. When you retrieve data from the data source, the data passes through a data processing extension or data provider that is associated with the data source type. Depending on the data processing extension, data may be converted from the data types used by data source into data types supported by the data processing extension. Reporting Services uses data types supported by the common language runtime (CLR) that is installed with Business Intelligence Development Studio. The data provider maps each column in the result set from the native data type to a .NET Framework common language runtime (CLR) data type.

At each stage, the data is represented by the data types as described in the following list:

Data source The data types supported by the version of the type of data source to which you are connecting.

For example, typical data types for a SQL Server data source include int, datetime, and varchar. Data types introduced by SQL Server 2008 added support for date, time, datetimetz, and datetime2. For more information, see Data Types (Transact-SQL).
Data provider or data processing extension The data types supported by the version of the data provider of the data processing extension you select when you connect to the data source. Data providers based on the .NET Framework use data types supported by the CLR. For more information about .NET Framework data provider data types, see Data Type Mappings (ADO.NET) and Working with Base Types on MSDN.

For example, typical data types supported by the .NET Framework include Int32 and String. Calendar dates and times are supported by the DateTime structure. The .NET Framework 2.0 Service Pack 1 introduced support for the DateTimeOffset structure for dates with a time zone offset.

Note

The report server uses the data providers that are installed and configured on the report server. Report authoring clients in Preview mode use the installed and configured data processing extensions on the client machine. You must test your report in both the report client and the report server environment.

Report processor The data types are based on the version of the CLR installed when you installed Reporting Services. SQL Server 2008 Reporting Services installs the CLR based on the .NET Framework 3.5.

For example, the data types the report processor uses for the new date and time types introduced in SQL Server 2008 are shown in the following table:

SQL Data Type	CLR Data Type	Description
Date	DateTime	Date only
Time	TimeSpan	Time only
DateTimeTZ	DateTimeOffset	Date and time with time zone offset
DateTime2	DateTime	Date and time with fractional milliseconds

For more information about SQL Server database types, see Data Types (Database Engine) and Date and Time Data Types and Functions (Transact-SQL).

For more information about including references to a dataset field from an expression, see Working with Data Types in Expressions (Report Builder 2.0).

Using Multiple Datasets

A report typically has more than one dataset. The following list describes how you can use datasets in a report:

You display the data from each dataset using a separate data region. For more information, see Working with Data Regions (Report Builder 2.0).
You can link more than one data region to a dataset and provide multiple views of the same data. For more information, see Linking Multiple Data Regions to the Same Dataset (Report Builder 2.0).
You can use datasets to provide a drop-down list of available values or default values for a report parameter. For more information, see Adding Parameters to Your Report (Report Builder 2.0).
You can link related data from multiple datasets by using parameters with drillthrough reports or subreports. For example, a sales report can show summary data for all stores, and a drillthrough link can specify the store identifier as a parameter to a report with a dataset query that retrieves the individual sales for the specified store. For more information, see Adding Drillthrough Reports (Report Builder 2.0) and Adding Subreports (Report Builder 2.0).
You cannot display detail data from multiple datasets in a single data region. However, you can display aggregate or built-in function values for multiple datasets within a data region. For more information, see Using Built-in Report and Aggregate Functions in Expressions (Report Builder 2.0). If you need to combine detail data from multiple datasets into one data region, you must rewrite your query to retrieve the data as a single dataset.

Importing Existing Queries for a Dataset

When you create a dataset, you can create a new query or you can import an existing query from a file or from another report. Only the .sql and .rdl file types are supported. Multidimensional Expression (MDX) queries or Data Mining Prediction (DMX) queries can only be generated by a Reporting Services query designer because a result set must be retrieved as a flattened rowset.

When you import a query from another report, you can choose which query to import from the list of datasets in the report.

Displaying a Message When No Rows of Data are Retrieved at Run-time

During report processing, when the query for a dataset runs, the result set may contain no rows. In the rendered report, a data region linked to an empty dataset displays as an empty data region. You can specify text to display in the rendered report in place of the empty data region. You can also specify a message for subreports when the queries for all datasets produce no data at run time. For more information, see How to: Set a No Data Message for a Data Region (Report Builder 2.0).

Detecting Missing Fields

When the report is processed, the result set for a dataset may not contain values for all of the columns specified because the columns no longer exist on the data source. You can use the field property IsMissing to detect whether values for a field were returned at run-time. For more information, see Using Dataset Fields Collection References in Expressions (Report Builder 2.0).

Displaying Hidden Datasets

When you create a parameterized query for some multidimensional data sources, Reporting Services automatically creates datasets to provide valid values for the parameter. By default, these datasets do not appear in the Report Data pane. For more information, see How to: Show Hidden Datasets (Report Builder 2.0).