Lesson 2: Creating a Calculated Field (Report Builder)
New: 14 April 2006
In this lesson, you will expand an existing formula, apply a filter to the formula, save the filter, and then save the modified formula as a new calculated field.
Your source of data probably consists of numerous entities and each entity contains at least one field. However, depending on what data you want to retrieve, the available fields might not return the exact data that you need. In that case, you can modify formulas in pre-existing fields and save them as new fields, or you can create your own calculated fields to retrieve the exact data that you want.
Just as each field is tied to an entity, any new field that you create must be tied to an entity as well. This context provides information that Report Builder uses to pull and/or manipulate data from the underlying database.
Every field contains a formula. To view the underlying elements that comprise the formula, you can expand the formula. Expanding a formula allows you to see the relationship between the entities referenced in your field. Once you have expanded a formula, you can change it, apply filters anywhere along the relationship path, change the aggregation level, and remove duplicates.
After a new calculated field is created, it is stored in the report. You can reuse the field in other formulas or filters throughout the report; however, if you want to use the calculated field in another report, you will need to create the field again in the new report. If you find that you are creating the same calculated field repeatedly, contact the person who created the source of data and ask them to incorporate your calculated field into the data source.
To expand a formula
In the design area, right-click the 0 cell for the #Sales column and select Edit Formula.
The Define Formula dialog box appears.
In the Formula for each Product box, double-click #Sales.
Expand the formula to see the context of the formula within the source of data.
To apply a filter to a formula
In the Expanded formula for '#Sales' box, click No filter applied for the Sales entity and then select Create a New Filter.
The Filter Data dialog box appears.
In the Entities list, select Sales Order.
In the Fields list, double-click Order Date.
The Order Date field is added to the Filter area.
In the filter clause, click equals, point to Relative Dates, point to Last (n), and then select Years.
Notice that equals changes to in last.
Click the spin box up arrow to select 2.
By applying this filter, Report Builder will return only the last two calendar years' worth of sales data in calculating the # Sales.
To name a filter
In the Filter name box, type Order date in last 2 years.
Click OK.
You can create numerous filters, name them individually, and then apply them anywhere in your formulas. If you do not create a name for the filter, the filter will reflect the actual field name to which it is applied.
To save and rename the calculated field
Select the Save this formula as a new Product field check box.
Click OK.
The New Field Name dialog box appears.
Type # Sales in Last 2 Years.
Click OK.
The design area is displayed again.
In the design area of the Report Builder window, note that the # Sales field has been renamed. Also, in the Entities list, select the Product entity and note that the calculated field that you created in this lesson is displayed at the top of the Fields list.
You have successfully created a new calculated field. In the next lesson, you will create another calculated field and apply a pre-existing filter to the field that determines the total quantity of each product that was sold over the past two years. See Lesson 3: Applying an Existing Filter to a New Calculated Field (Report Builder).
See Also
Other Resources
Creating a New Field (Report Builder)
Modifying Formulas (Report Builder)
Working with Formulas (Report Builder)
Working with Filters (Report Builder)
Filter Data Dialog Box (Report Builder)
Define Formula Dialog Box (Report Builder)
Report Builder How-to Topics