Lesson 3: Applying an Existing Filter to a New Calculated Field (Report Builder 1.0)
In the previous lesson, you modified a formula, applied a filter to the entity, and then saved it as a new calculated field. In this lesson, you will create another calculated field and then apply the existing filter to this new field.
To apply an existing filter to a new calculated field
In the Entities list, select Sales.
In the Fields list, double-click Sum Order Qty.
In the design area, right-click the 0 cell for the Sum Order Qty column and select Edit Formula.
The Define Formula dialog box appears.
In the Formula for each Product area, double-click Sum Order Qty.
The formula expands to display the path of the formula.
In the Expanded formula for 'Sum Order Qty' box, click No filter applied for the Sales entity, point to Use an existing filter, and then click Order Date in last 2 years.
By applying this filter, Report Builder will return only the total quantity ordered for each product item over the past two calendar years.
To save and rename the calculated field
In the Define Formula dialog box, select the Save this formula as a new Product field check box.
The New Field Name dialog box appears.
Type Total Qty in Last 2 Years.
In the design area, notice that the Sum Order Qty field is renamed. If you select the Product entity, notice that the new calculated field appears in the Fields list.
You have successfully applied a filter to a formula and saved the calculated field to your report. In the next lesson, you will apply a filter to the entire report. See Lesson 4: Apply a Filter to the Entire Report (Report Builder 1.0).