Working with Fields in a Report Dataset
Dataset fields are used to provide a link between report items such as tables and charts, and the report data. A field can be a direct link to a column returned by a query, or it can be a calculated field that you define.
Dataset fields are not the same as built-in fields. Built-in fields are a set of predefined fields provided by Reporting Services that provide report information such as the report name or the timestamp when the report was processed. For more information, see Using Built-in Field References in Expressions (Reporting Services).
To display dataset field values in a report, you drag them to text boxes on the design surface or data region cells, or to the drop-zones of charts and gauges. Dataset fields can represent numeric and non-numeric data, as well as other types of data in a database, such as URLs for drillthrough reports, images, and e-mail addresses for subscribers.
Working with the Field Collection
Dataset fields are specified by the dataset query and by any calculated fields that you define. After you create a dataset, you can view the dataset field collection if you expand the dataset node in the Report Data pane. Whenever you change the query for the dataset, the database fields are automatically refreshed.
You specify a calculated field manually by creating an expression. Calculated fields can be used to create new values that do not exist on the data source. For example, a calculated field can represent a new value, a custom sort order for a set of field values, or an existing field that is converted to a different datatype. For more information, see How to: Add, Edit, or Delete a Field in the Report Data Pane.
Linking a Dataset Field with a Report Item
The following report items can display data from a dataset: Textbox, Table, Matrix, List, Chart, and Gauge. To link a field with a report item, you can drag the field from the Report Data pane, or use the field selector icon on the report item. For example, the simplest way to link a field with Chart category drop-zones is to use the field selector. For a Tablix with an existing row group, you can drag a field to the Tablix row group area to add additional nested fields. When you drag a field to the Tablix body area, you can add a new column with default values automatically filled in for the field.
For more information about adding fields to report items on the design surface, see specific report item types in Designing the Report Layout.
If you do not see the expected results in your dataset field collection, see Troubleshooting Reports: Reports Exported to a Specific File Format.
Understanding Default Expressions
A text box can be a Textbox report item in the report Body, or a text box in a cell in a Tablix data region. When you link a field with a text box, the location of the text box determines the default expression for the field reference. In the report body, a text box value expression must specify an aggregate and a dataset. If only one dataset exists in the report, this default expression is created for you. For a field that represents a numeric value, the default aggregate function is Sum. For a field that represents a non-numeric value, the default aggregate is First.
In a Tablix data region, the default field expression depends on the row and group memberships of the text box that you add the field to. The field expression for the field Sales, when added to a text box in the detail row of a table, is [Sales]. If you add the same field to a text box in a group header, the default expression is (Sum[Sales]), because the group header displays summary values for the group, not detail values. When the report runs, the report processor evaluates each expression and substitutes the result in the report.
Using Extended Field Properties
Data sources that support multidimensional queries, such as SQL Server Analysis Services, support field properties on fields. Field properties appear in the result set for a query, but are not visible in the Report Data pane. They are still available to use in your report. To refer to a property for a field, drag the field onto the report, and change the default property Value to the field name of the property you want. For example, in an Analysis Services cube, you can define formats for values in the cube cells. The formatted value is available by using the field property FormattedValue. To use the value directly instead of using a value and setting the format property of the text box, drag the field to the text box and change the default expression =Fields!FieldName.Value to =Fields!FieldName.FormattedValue.
Note
Not all Field properties can be used for all data sources. The Value and IsMissing properties are defined for all data sources. Other predefined properties (such as Key, UniqueName, and ParentUniqueName for multidimensional data sources) are supported only if the data source provides those properties. Custom properties are supported by some data providers. For more information, see specific topics about extended field properties for your data source type in Creating a Report Dataset. For example, for a SQL ServerAnalysis Services data source, see Using Extended Field Properties for an Analysis Services Dataset.
Working with Field Data Types
When you create a dataset, the data types of the fields on the data source may not be exactly the data types used in a report. Data types may go through one or two mapping layers. The data processing extension or data provider may map data types from the data source to common language runtime (CLR) data types. The data types returned by data processing extensions are mapped to a subset of common language runtime (CLR) data types from the .NET Framework.
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) version installed with Business Intelligence Development Studio.
Report data travels from the data source through the data provider or data processing extension to the report processing component. At each stage, the data is represented by the data types as described in the following list:
The data provider maps each column in the result set from the native data type to a Microsoft .NET Framework CLR data type:
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 2005 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 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. For more information dates with time zones, see Choosing Between DateTime, DateTimeOffset, and TimeZoneInfo.
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. SQL Server 2008 Reporting Services installs the CLR based on the .NET Framework 2.0 Service Pack 1.
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 Data Types and Functions (Transact-SQL). For more information about CLR data types, see Working with Base Types.
For more information about Microsoft .NET Framework data provider data types, see Data Type Mappings (ADO.NET).
For more information, see Working with Data Types in Expressions (Reporting Services) and Report Definition Language Data Types.
See Also