Working with Data in Reporting Services

When you write a report in Report Designer in Data view, you begin by defining datasets. A dataset is the metadata that represents the underlying data on a specific data source. You choose the source of data, specifying where it is located and how to connect to it. Report Designer then displays a query designer associated with that type of data source so you can explore the underlying data and interactively design a query. You create a query that specifies the data you want from the data source. If you run the query, you get back a result set. The result set is represented by a collection of fields for the dataset. You can also define additional calculated fields. In summary, each dataset consists of a pointer to a data source, a query, and a collection of fields. You can create as many datasets as you need to for representing report data.

After you have defined all the datasets for a report, you switch to Layout view and drag fields onto the report layout, arranging the report data on the report page. For more information, see Understanding Report Layout and Rendering.

Data Sources

A Reporting Services data source definition contains information about a connection to a data source. For a database, this includes information such as a server name, a database name, and user credentials. The information contained within a data source definition varies depending on the type of underlying data. Reporting Services supports the following data source types: Microsoft SQL Server, Microsoft SQL Server Analysis Services, Report Server Model, XML, Oracle, SAP NetWeaver Business Intelligence, Hyperion Essbase, ODBC, and OLE DB. A Report Server Model data source type enables you to create reports based on an existing report model. For more information about report models, see Working with Models.

A data source can be contained within a single report, or it can be shared by several reports. The definition for a report-specific data source is stored within the report itself, while the definition for a shared data source is stored as a separate item on the report server. A report can contain one data source (report-specific or shared) or many. For more information about data sources, see Defining Report Data Sources.

Data Processing Extensions

When you choose a data source type, you are choosing from a list of installed and registered Reporting Services data processing extensions or .NET Framework-managed data providers. Additional data processing extensions can be configured when you install the corresponding data provider. For more information about supported data processing extensions, see Defining Report Datasets and Configuring Reporting Services Components. For more information about creating your own data processing extension, see Implementing a Data Processing Extension.

Datasets

A Reporting Services dataset contains a data source definition, a query of the data source and a resulting fields list, and possibly parameters, calculated fields, and collation and other character sensitivity information to be used by a report.

A report can contain multiple datasets. These datasets can be used by different data regions on the report, or they can be used to provide dynamic lists of parameters. You can use filtering, sorting, and grouping to change the data shown in the report. For more information about datasets, see Defining Report Datasets. For more information about data regions, see Understanding Data Regions.

Fields

Each dataset in a report contains a collection of fields. Typically, the fields refer to columns or fields returned by the query in the dataset. For an XML data source, the fields refer to values from XML element nodes and their attributes. Fields that refer to database fields contain a pointer to the database field and a name property. You can use the name property to provide a friendly name in place of the name of the database field. In addition to database fields, the fields list can contain calculated fields. Calculated fields contain a name and an expression. The expression can be as simple as a concatenation of two database fields (for example, first name and last name), or it can be used to perform complex calculations. For more information, see How to: Add, Edit, or Delete a Field in the Datasets Window (Report Designer).

For information about how to reference the field collection from a report item, seeUsing Global Collections in Expressions (Reporting Services).

Some query languages are flexible enough so that a query can be written to return friendly field names and perform calculations, making changes to the fields list unnecessary. The fields list is especially useful when using a database or query language that does not provide this flexibility. For more information, see How to: Refresh Fields in the Datasets Window (Report Designer).

See Also

Concepts

Report Design Basics
Working with Data in a Report Layout

Other Resources

Data View (Report Designer)

Help and Information

Getting SQL Server 2005 Assistance

Change History