• Article

Working with Fields (Report Builder 1.0)

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 or by double-clicking the field name. To work with a group of default fields, you can drag an entity to the design area. 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.

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 field types: Text, Number, DateTime, Time, 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 variations nested below it such as Sell End Day, Sell End Month, Sell End Year, and Sell End Quarter. These fields, called field variations, 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 table data region within 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.

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 icon

Text

Indicates a field where the data is text; for example, the word "Yes."
Icon for number fields

Number

Indicates a field where the data is a single numeric value; for example, the number 5.
Date/date time field icon

Date/DateTime

Indicates a field where the data is a date and/or a time value; for example, 4/3/2005 or Friday, October 18, 2009 12:09 PM.

Fields that contain DateTimeOffset CLR data type values appear in the Fields list as date time fields.
The time icon.

Time

Indicates a field where the data is a time value displayed as text; for example, 07:32:25.
Money field icon

Money

Indicates a field where the data is a currency value; for example, $782.95.
Aggregate field icon

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 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 Field Icon

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 Icon

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.