Working with Fields (Report Builder)

A field contains the data that can be added to the report layout. When you select an entity, the fields contained within that entity are displayed in the Fields area of the Explorer pane. To work with fields directly, you can add fields to the design area by dragging the field name, double-clicking the field name, or by selecting the check box next to the field name. To work with a group of default fields, you can add entities to the design area by dragging the entity name or double-clicking its name. The default fields associated with that entity are added to the design area. For example, if you add the Employee entity of the AdventureWorks model to your report, the following fields are added to the design area: National ID Number, First Name, Middle Name, Last Name, and Email Address. Properties set in the data source determine which fields are added.

Using the fields and entities available to you, you can customize the information displayed in the report. You can:

• Drag and drop fields to almost any location within the report layout, in the order that you specify.
• Format a field so that the contents appear the way you want them to.
• Filter on the contents of any combination of fields.
• Sort the report data by one or more fields in ascending or descending order.
• Search for fields within the data source and within the current entity.
• Edit a field's formula.
• Create new fields.

Field Types

Fields can contain two types of values: single values and totals over a set of values (also known as aggregates). For example, Last Name and Unit Price are single values, while Total Sales is an aggregate, that is, a total value based on the sum of a set of values. Report Builder uses the following fields: Text, Number, Date/DateTime, Money, Aggregate, Image, and Boolean.

You can create your own fields by defining formulas for them. For example, you can create a field that represents the sum of three other fields that appear within the report. When you create the field, you define the formula that tells the report server to add the specified fields together and display the result in the field you just created.

The order of the fields appearing in the Fields area of the Explorer pane is determined by the data source. Typically, the most frequently used fields will be toward the top of the list. New fields that you create are displayed at the top of the list.

Both single value and total fields can have variations. A field variation is a field that contains data that is based on the top-level field. For example, the Product entity contains a Sell End Date field. This field has field variations nested below it such as Sell End Day, Sell End Month, Sell End Year, and Sell End Quarter. These fields all contain data based on the Sell End Date field.

Field Groups

As you drag fields to the report layout, Report Builder creates groups in which to display the fields. You can see the field groups by selecting the report layout. The groups that Report Builder creates are based on the relationships between the fields. You can modify how the fields are grouped by moving them around in the report layout.

Note

Adding fields to the report layout individually will give you a different set of results than when adding fields to the report layout as a group. When building your reports, you should be aware of how this affects your report structure and query.

Below are some basic rules that Report Builder uses to group fields:

• When any field is dragged to the left edge of the design area, it becomes a separate group.
• When a field is dropped to the right side of the design area, it is grouped into the grouping to the immediate left.
• When an aggregate field is dropped, totals are automatically displayed in all groups.

Note

When you add a field by double-clicking it or by selecting the check box, the field is added to the right of the design area and, if possible, is grouped with the last group of fields in the report. If it cannot be added to the last group, a new group is created to the immediate right in the report.

If you want to add a field to a specific group, drag the field to a position within the group or onto the group tab that appears above the group.

Field Icons

Icon	Type	Description
	Text	Indicates a field where the data is text; for example, the word "Yes."
	Number	Indicates a field where the data is a single numeric value; for example, the number 5.
	Date/date time	Indicates a field where the data is a date and/or a time value; for example, 4/3/2005.
	Money	Indicates a field where the data is a currency value; for example, $782.95.
	Aggregate	Indicates a field where the data is a single numeric value that represents a calculation on a set of numbers. For example, the total sales amount for 25 sales people in the year 2010 is calculated by adding up the sales figure for each of the 25 sales people and the sum total is displayed as a single numeric value.
	Boolean	Indicates a field where the data is a true/false or yes/no value; for example, the Yes/No value in a column that indicates whether a specific product has been discontinued.
	Image	Indicates a field where the data is an image. For example, suppose your database contains images of each of your products. When these images are included in your data source, this icon is displayed with the field name.

See Also

Concepts

Creating a Report (Report Builder)
Understanding Report Layout and Rendering (Report Builder)
Formatting a Report (Report Builder)
Working with Data (Report Builder)
Working with Report Layouts (Report Builder)

Other Resources

Report Builder Terms

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	New content: Added information about using a check box to add entities and fields to the design area.
17 July 2006	Changed content: Added note.
14 April 2006	Changed content: Clarified language and provided example about default fields. Clarified language around aggregates.