Share via


Creating a New Field (Report Builder)

The data source that you use in Report Builder consists of fields grouped into entities. Every field within the data source has a formula in it that tells Report Builder what information should be pulled from the underlying database. The person who created your data source probably created numerous text, aggregate, number, date and time, money, Boolean, and image fields in anticipation of how you and other users would want to work with the data in the data source. However, perhaps you need to manipulate the data in such a way that none of the available fields represents exactly the data you want to add to your report. In this case, you can create fields using the functions available in Report Builder.

For example, suppose you need to determine the age of each employee in the company. The only information you have is each employee's birth date. By creating a new field and using the DateDiff and Today( ) functions, you can calculate each age value. Here is an example using text data: suppose you want to combine the first, middle, and last name fields and display each employee's name as a single field. To do this, you need to create a new field, and then use the concatenate function to combine the three fields.

Before you start to create your field, use the Explorer pane to locate the entity you want to use for the context of your field. By locating this entity, you are pointing Report Builder to the information that it needs to pull and/or manipulate from the underlying database. Using the examples above, you would locate the Employee entity before you begin to create the new field.

Once you have located the context entity for your field, simply click the New Field button to launch the Define Formula dialog box. To create your field in the Define Formula dialog box, you can drag fields into the formula and use the available functions. When you are done creating your field, you can give the field a meaningful name and save it within the context entity. Once you have saved your field, it is listed in the Fields area. You can continue to refine the formula and work with the new field just like you would any other field.

When you save your new field, keep in mind that this field is not saved back into the data source and is not available to anyone else using the same data source. Instead, the field is saved to the current report only. If you share your report with others and they have permission to edit the report, they can use the new field too.

Note

If you find yourself creating the same type of field repeatedly or if others might find your new field helpful, you should contact the person who creates your data source so that they can incorporate the field into the data source.

See Also

Concepts

Understanding Formulas (Report Builder)
Modifying Formulas (Report Builder)
Using Functions (Report Builder)
Formatting a Report (Report Builder)

Help and Information

Getting SQL Server 2005 Assistance