Lesson 11: Creating a Calculated Field

New: 5 December 2005

When creating reports, users might want information that is not directly available from the database. You can add calculated fields to the model in order to make these often-used calculations available to model users. In this lesson, you will create two new fields and then specify properties for each field.

To create a new field

1. Return to the AdventureWorks Model in the SQL Server Business Intelligence Development Studio window.

2. In the Tree view, right-click the Purchase Order Detail entity, point to New, and then click Expression.

3. In the Define Formula dialog box, double-click Order Qty in the Fields list.

   The Order Qty field appears in the Formula for each Purchase Order Detail box. Notice that operator buttons appear below the box.

4. Click the - (minus) operator button.

5. In the Fields list, double-click Rejected Qty.

   The expression appears as Order Qty - Rejected Qty.

6. Click OK.

7. In the List view, select the NewExpression attribute.

8. In the Name box of the Properties window, type Fulfilled Qty.

9. Locate the Nullable property and then set it to True.

   This field is not required to be populated.

To create a new field using a function

1. In the List view, right-click anywhere in the white space, point to New, and then click Expression.

2. In the Define Formula dialog box, select the Functions tab.

3. In the Functions list, expand the Aggregate node.

4. Double-click Sum.

   SUM(aggregate) appears in the Formula for each Purchase Order Detail box.

5. Select the Fields tab.

6. Double-click Fulfilled Qty.

   The formula appears as SUM(Fulfilled Qty).

7. Click OK.

8. In the List view, select the NewExpression attribute.

9. In the Name box of the Properties window, type Sum Fulfilled Qty.

10. Locate the IsAggregate property and then set it to True.

    Setting this property to True enables subtotals for this field.

11. Locate the Nullable property and then set it to True.

    This field is not required to be populated.

12. Locate the EnableDrillthrough property, and then set it to True.

    Setting this property to True allows users to click the value in this field to view a clickthrough report of the Purchase Order Detail records, if one exists.

13. Locate the VariationOf property and then select Fulfilled Qty.

    The field is now a variation of the scalar field Fulfilled Qty. After this property is set, the Sum Fulfilled Qty field should be nested under the Fulfilled Qty field in List view.

14. Locate the DefaultAggregateAttribute property for Fulfilled Qty, and then select Sum Fulfilled Qty.

    Setting this property causes the Sum Fulfilled Qty field to appear as the top level field with the Fulfilled Qty field nested below it in Report Builder.

15. On the File menu, click Save All.

Next Steps

When creating reports, it is useful to have clickthrough reports in some instances. In the next lesson, you will specify clickthrough report options. See Lesson 12: Specifying Clickthrough Report Options.

See Also

Tasks

Tutorial: Refining a Report Model in Model Designer

Concepts

Reporting Services Tutorials

Other Resources

Working with Model Designer
Model Designer Object Properties

Help and Information

Getting SQL Server 2005 Assistance