Defining Report Datasets

When you create reports in Reporting Services, you first define one or more data sources and then you create a dataset for each data source. Each dataset specifies the fields from the data source that you plan to use in the report, as well as calculated fields that you can create. In addition to the query and field definitions, a dataset contains a pointer to the data source, parameters, and data options that include character sensitivities including case, kana type, width, accent, and collation information. For more information about the dataset options that you can specify, see Dataset (Query Tab, Report Designer).

The way you specify data that you want depends on the data source itself: relational databases can use Transact-SQL queries or stored procedures, multidimensional databases can use multidimensional or data mining queries, XML documents or Web services can specify queries with or without namespaces, report models can use report model metadata queries. Many types of data sources provide graphical query designers to help you explore the underlying data sources and visually build a query to specify the data to use for your report.

All datasets that you create for a report definition appear in the Report Datasets window. Each dataset node expands to show a collection of fields. Some data processing extensions provide a feature that analyzes the query for a dataset and determines the collection of fields. From the Datasets window, you can add fields or edit existing fields. A field represents the metadata for the underlying data from a data source. After you have defined the datasets for a report, you drag fields from the Datasets window onto a report layout to design the report's appearance and save the report definition to report server. When the report is processed, the actual data is retrieved from the data source and combined with the report layout to produce the rendered report.

A report can have one or many datasets. You can combine data from multiple datasets by using query parameters and subreports or drillthrough reports. You can also design queries to combine data at the data source and create a single dataset for the combined data.

Report models can be used as data sources for both Report Builder ad hoc reports and full-featured reports created in Report Designer. Report models use shared data sources, which are defined when the report model is defined. For more information about how to build report models, see Working with Model Designer. For information about creating ad hoc reports, see Working with Report Builder. For information about how to define datasets from a report model, see Defining Report Datasets from Report Model Data Sources.

To learn more about how to connect to and retrieve data from the data source types supported by Reporting Services, see the topics in this section.

In This Section

• General Characteristics of Data Retrieved From a Report Data Source
  Describes the characteristics of data retrieved from a data processing extension or data provider for report data.

• Defining Report Datasets for a SQL Server Relational Database
  Discusses how to define a dataset from a SQL Server relational database.

• Defining Report Datasets for Analysis Services Multidimensional and Data Mining Prediction Data
  Discusses how to define a dataset from an Analysis Services multidimensional database.

• Defining Report Datasets for Package Data from SQL Server Integration Services
  Discusses how to define a dataset from an Integration Services package.

• Defining Report Datasets for XML Data
  Discusses how to define a dataset from XML data in an XML document, Web service, or Web application.

• Defining Report Datasets for Relational Data from an Oracle Database
  Discusses how to define a dataset from an Oracle database.

• Defining Report Datasets for Relational Data from an SAP Database
  Discusses how to define a dataset from a mySAP Business Suite database.

• Defining Report Datasets for Multidimensional Data from an SAP NetWeaver BI System
  Discusses how to define a dataset from an SAP NetWeaver Business Intelligence data source.

• Defining Report Datasets from Report Model Data Sources
  Discusses how to define a dataset using a data source from an existing report model.

• Defining Report Datasets for Multidimensional Data from a Hyperion Essbase Database
  Discusses how to define a dataset from a Hyperion Essbase data source.

• Defining Report Datasets for Other Data Sources
  Discusses how to define a dataset using a data source from an OLE DB, ODBC, or other data provider.

See Also

Tasks

How to: Create a Dataset (Report Designer)
How to: Add, Edit, or Delete a Field in the Datasets Window (Report Designer)

Concepts

Data Sources Supported by Reporting Services
Report Data How-to Topics (Report Designer)
Working with Data in a Report Layout
Working with Data in Reporting Services
Connecting to a Data Source

Other Resources

Accessing and Changing Database Data
Working with Online Analytical Processing (OLAP)
Report Datasets (Report Designer)
Data View (Report Designer)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	New content: Added Hyperion Essbase link.
14 April 2006	New content: Updated links table for Report Model, SAP NetWeaver Business Intelligence, Oracle, SAP