Working With Fields in a Report Dataset

In Report Designer, a field can be a direct link to the database field, database field property, column, or alias, or it can be a calculated field that is based on fields in the database. Fields provide the data used in a report. Fields can also be used in an expression for the value of a text box or image. To add fields to a report, drag them from the Datasets list to your report layout. You can add fields to text boxes, tables, lists, charts, and matrices.

Working with the Datasets List

The Datasets list displays a list of fields for each dataset. When a dataset is created, Report Designer retrieves a list of fields from the data source and populates the list. A field can be either a database field or a calculated field. A database field contains the name of the field from the query. A calculated field contains an expression. You can use calculated fields to provide additional fields that are not in the database and that are not calculated in the query.

All fields have names. By default, a database field has the same name as the column name from the query, but it can be changed. Calculated fields must be given a name when they are created.

You can drag a field from the fields list onto the report layout when you are in Layout view. If you drag a field onto the report body, a list data region, or other unstructured area, Report Designer will create a text box with a field expression in it. If you drag a field onto a structured area like a table or a matrix cell, or onto an existing text box, a field expression is placed in the cell or text box.

Fields in a data region return a row for every value returned as part of the result of the query when the report is processed.. Fields in text boxes or other report items that are not part of a data region return a single value when the report is processed.

To refer to a member property for a field, drag the parent field onto the report, and change the default property Value to the name of the property you want.

For some data sources, Report Designer may be unable to retrieve field data and populate the Fields list. There are several steps you can take to resolve this issue.

• Make sure that you have permissions to retrieve field information from the database. For some data sources, you may have permissions to access the table or object, but not have permissions to view the columns within the table or object.
• Run the query in the generic query designer. The graphical query designer may not be able to run queries against some data sources.
• Construct the fields list manually. In the Datasets window, right-click the dataset and select Add. The Add New Field dialog box appears. For more information, see Edit Field/Add New Field (Report Designer).

Working with the Datasets Window

To display the Datasets window when working in a query designer, from the View menu, choose Datasets. Expand any node to view the fields defined for the dataset. Use the Refresh Fields () button on the toolbar to save your changes to the report definition file and update the field definitions for a dataset. For more information, see How to: Refresh Fields in the Datasets Window (Report Designer) and Report Datasets (Report Designer).

To use the fields in your report, change from Data view to Layout view and drag the fields onto the report layout. Changing to Layout view automatically saves the fields to the report definition.

See Also

Tasks

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

Concepts

Adding a Text Box
Designing the Report Layout
Working with Data Regions
Working with Data in a Report Layout

Other Resources

Using Expressions in Reporting Services

Help and Information

Getting SQL Server 2005 Assistance

Change History