Understanding Report Datasets
A dataset specifies the data that you want to use from a data connection. A dataset is based on a data connection that has been saved in the report as an embedded data source or a reference to a shared data source on a report server. The dataset includes a query that specifies a set of fields. As you drag these fields to the design surface, you create expressions that evaluate to the actual data when the report runs.
There are two types of datasets:
Shared dataset. A shared dataset is defined on the report server. You can browse to the server to create a shared dataset or to select a predefined one to add to your report. For more information, see Managing Shared Datasets.
Embedded dataset. An embedded dataset is defined in and used by only the report it is embedded in.
A dataset also includes parameters, filters, and data options that specify character sensitivities, such as case, kana type, width, accent, and collation information.
Components are reusable report items that you can include in your report. If a component has dependent datasets, the datasets are embedded in the component. When you add a component to your report, you also add all the datasets that they depend on. After these datasets are added to the report, they are no different than datasets that you add from the report server or create manually. For more information, see Publishing Report Parts in Report Designer.
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.Person;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 Using Report Models as Data Sources.
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. For more information about how to set these properties, see Dataset Properties Dialog Box, Options.
When you add a shared dataset to a report, you cannot change the query or the filter that is defined as part of the shared dataset definition. You can add additional filters and overrides dataset options for the instance in the report. For more information, see Embedded and Shared Datasets (Report Builder 3.0) in in the Report Builder 3.0 documentation on msdn.microsoft.com.
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.
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 Functions (Transact-SQL).
For more information about including references to a dataset field from an expression, see Working with Data Types in Expressions (Reporting Services).
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.
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.
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.
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 and Adding Subreports.
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 (Reporting Services). 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.
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 (Reporting Services).
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.
See Also
Reference
Concepts
Data Sources Supported by Reporting Services
Data Processing Extensions and .NET Framework Data Providers