On this tab (Fig.) you indicate which fields you want the system to use to display the results of the calculation for your aggregation task.
Calculated Fields are columns that contain summaries of the numerical fields from transaction tables that come from the source.
A new Calculated Field also can be created and set up with a calculation formula that may reference other columns in order to populate it.
Calculated Fields Tab Tool Bar
The Tool Bar provides standard functionality identical to that of the Detail tab (see above).
Calculated Fields Tab’s Data Grid
The grid of the Calculated Fields tab consists of 8 columns:
1. Selected Calculated Field Name – displays names of the selected calculated fields
2. Selected Calculated Fields Descriptions – displays descriptions of the calculated fields
3. Aggregation Method combo box – provides a menu (Fig. ) listing supported methods of aggregation for your selection.
4. Calculate Total checkbox – a checkmark here indicates the total will be calculated for this column. If the column contains a percentage of values or any other type of value for which calculating totals makes no sense, the checkmark should be removed to prevent the system from performing an unnecessary task.
5. Calc. Formula – Here you enter an SQL expression which will determine how the Calculated Field should be populated. This formula can reference the columns in the original model(s) by preceding column names with a “$” sign. For instance, for a calculated field “transaction_amount” the formula could be “$debit_amount – $credit_amount”.
Button provides the lookup listing columns available for the use in your formula.
Note: In order for a field to be available for the use in Calculation Formula, it must be set up as a Detail, Calculated, or an Auxiliary field.
The fields referenced in the formula should either be calculated fields themselves or set up as auxiliary fields (see below).
If a calculated field is populated using a formula, mapping for it is not needed.
6. Value Type – This field becomes active when a Calculation formula is used. You are enabled to select the appropriate Data format from the arrow button menu (Fig.).
7. Value Size – This field becomes active when a Calculation formula is used. Input here indicates what number of characters the value in the field would NOT exceed.
8. Value Nullable? – This field becomes active when a Calculation formula is used. Check Mark here will allow nulls in the Calculated field.
Adding New Calculated Field
You can create a calculated field by using the Add New button of the tab’s toolbar, entering the Field’s name by hand, and then mapping it to appropriate fields in the Data Models (see below).
You can use new calculated fields and populate them with data not present in the original source.
For example, your model may have a field called “foreign gross amount” and another called “exchange rate” while the report requires the results in “Domestic gross amount” (that is Foreign Gross expressed in domestic currency). In order to express the “Foreign” amount in the domestic currency, you would multiply a “foreign amount” by the “exchange rate”.
If, at the same time, you do not want to show the “foreign gross amount” and “exchange rate” fields in your aggregation results you would:
1. Set them up as auxiliary fields into the Auxiliary Fields tab
2. On the Calculated Fields, tab create a new field called “gross_domestic_amount” and as the formula enter “foreign gross amount” * “exchange rate”