Understanding Report Datasets (Report Builder 2.0)

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

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).

See Also

Concepts