Working with Data in a Report (Reporting Services)
[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]
The Report Designer includes a Data pane where you can see all the data available for your report. The Data pane contains the datasets and fields defined for the report, calculated fields, built-in fields, report parameters, data sources, and other resource files like images. To add data to a report, drag items to report items on the design surface.
When you open Report Designer, you will be prompted to create a dataset for your report. For more information about creating datasets, see Querying and Defining Report Data (Reporting Services). You can add multiple datasets to a report, and you can do so from the Data pane. Right-click a dataset to add another dataset from the same data source; right-click a data source to create a new dataset from a new data source.
On the design surface, data appears in report items, such as data regions and text boxes, as placeholder text (for example, [FieldName]) and represents an underlying expression that refers to the actual data item. When the report is processed, the real data is combined with the report layout and rendered. For more information, see Expression Placeholder Text (Reporting Services).
Types of Report Data
Report data includes any of the following:
- Report dataset with a field collection A dataset is comprised of a data source definition and a query that defines the fields collection, along with metadata that labels the retrieved data. You can also add customized fields to a dataset by creating calculated fields based on an expression. For more information, see Working with Fields in a Report Dataset (Reporting Services).
- Built-in Fields Reporting Services supplies built-in fields that include values such as the page number and report name. For more information, see Using Global Collections in Expressions (Reporting Services).
- Report parameters Parameters defined in the dataset query are automatically displayed in the Data pane as report parameters. You can also define separate report parameters. For more information, see Working with Parameters in Reporting Services.
- Images Image files can be separate data items in the Data pane, or they can be embedded in a database. You can specify images that are embedded in a report definition, that are files located on the report server, that are specified by a URL, or that are in a database. For more information, see Adding an Image to a Report.
Defining Report Datasets
Before designing a report, you must create a report dataset, which is comprised of a data source connection and a query.
For a SQL Server data source, the query is a Transact-SQL statement and the field collection defaults to the column names of the result set. For a multidimensional data source, the query is an MDX or DMX statement, and the field collection defaults to the column names for the result set as a flattened row set. For an XML data source, the query is a customized specification for XML elements and attributes, and the field collection defaults to the element and attribute names for a flattened XML document. For more information about report datasets, see Querying and Defining Report Data (Reporting Services).
When you define a dataset for your report, you specify the data source type from a list of installed and registered Reporting Services data processing extensions or .NET Framework-managed data providers. Reporting Services supports the following data source types: SQL Server, SQL Server Analysis Services, Report Server Model, XML, Oracle, SAP NetWeaver Business Intelligence, Hyperion Essbase, ODBC, and OLE DB. You can install other data providers and configure other data processing extensions. For more information about supported data processing extensions, see Defining Report Datasets and Configuring a Report Server Installation. For more information about creating your own data processing extension, see Implementing a Data Processing Extension.
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.
Filtering and Sorting Report Data
You can use filters to control the data that is displayed in a report. Filters can be set on the report dataset both before and after data is retrieved from the data source, on a data region, and on groups within a data region. Filters can be based on report parameters. For more information, see Filtering Data in a Report.
You can set the default sort order for report datasets and groups. You can base sort order on report parameters. You can provide interactive sort buttons on data region columns to provide the report reader with the ability to change the sort order. For more information, see Sorting Data in a Report.
Using Expressions
Use expressions to control the content and appearance of a report. Many properties of report items support expression-based values. Expressions are evaluated at run time when the report is processed and rendered. Properties are automatically set to default expressions for you when you perform certain actions, like when you drag a field to a table cell, but you can edit the default expressions to customize the content or appearance for each report item.
You can use expressions to refer to built-in collections like dataset fields and parameters. You can also write expressions that conditionally change how data appears, for example, changing the text color for negative currency values. For more information, see Using Expressions in Reporting Services (Reporting Services).
In addition, you can write expressions to calculate summary values for the data specified by a report dataset, data region, or group. You can calculate values in relation to other groups by specifying a scope as a parameter to the aggregate functions. For more information, see Calculating Totals and Other Aggregates (Reporting Services) and Using Built-in Functions in Expressions (Reporting Services).
See Also
Concepts
Querying and Defining Report Data (Reporting Services)
Working with Data Regions (Reporting Services)
Report Design Basics
Adding an Image to a Report
Other Resources
Using Expressions in Reporting Services (Reporting Services)
Working with Parameters in Reporting Services
Tutorial: Adding Parameters to a Basic Tabular Report
Tutorial: Advanced Features Using Parameters