Adding a Calculated Item

You can add the total value from a calculated column as a Text item using the $CALC variable. This variable displays the results of a calculation performed within the parentheses of an argument used to generate a calculated column and its values in a dive window. For example, using the sales_90day.mdl file, this topic demonstrates how to create a calculated column called Margin whose values are derived by subtracting Cost values from Revenue values using the following calculation: $CALC(Total[Revenue]-Total[Cost]).

To demonstrate how to insert the value of a $CALC operation in a report, complete the following steps:

  1. Open a sales cBase or model, for example, sales_90day.mdl.

  2. On the Console, double-click Company.

  3. Select Edit > Select Columns and, if not already selected, move Cases, Revenue, and Cost into the Selected box.

  4. Select Edit > Edit Columns and click the Add button to open the Add Column dialog box.

  5. Type Margin in the Name text box.

  6. Click Revenue in the Model Columns section.

  7. Click the Minus sign (-) in the Definition section.

  8. Click Cost in the Model Columns section to finish Closeddefining the Margin column calculation.

  9. Click OK twice to add the Margin column to the dive window.

    NOTE: You might have to choose Edit > Select Columns and move Margin from the Available list to the Selected list.

  10. Click the Report icon to open the Default Report dialog box, and click OK to Closeddisplay the report with the calculated column.

  11. Unlock the report and delete the total value (456,324.51) for the Margin column.

  12. Click the Add Item icon to open the Add Item dialog box.

  13. Type the following argument in the text box, $CALC(Total[Revenue]-Total[Cost], and perform the following actions:

    • In the Breaklevel tab, set the Breaklevel dimension to Company and the Location to Tail.
    • In the Position tab, set the column position to Column E.
    • In the Style tab, set the Style Name to Summary and the Top Line to Normal.

  14. Click OK Closedto view the report with the calculated total for the Margin column.