Managing a calculated field

Apply formulas to existing fields to create custom fields to generate data that you want.

Before you begin

Use the following syntax for formulas:
  • Use double quotation marks (") for labels in fields and measures. For example, "Customer ID" and "Date ordered".
  • Use single quotation marks (') for text. For example, '--'.
  • Use single quotation marks (') for levels. For example, 'ColumnGroup' and 'Total'.

About this task

Create a custom field and its corresponding formula to conduct queries. The following example describes a calculated field and formula.

Procedure

  1. Create an Ad Hoc view by clicking Create > Ad Hoc View.
  2. In the Select Data wizard, click the View as tree icon.
  3. From the domain source list, select Audit Data Repository, and click Choose Data to open the Data Chooser window.
  4. In the Data Chooser window, select all of the sources from the source list, and click OK to open a new Ad Hoc View.
  5. In the Ad Hoc view, hover over the drop-down icon next to Fields, and select Create Calculated Field from the menu to open the New Calculated Field dialog box.
    The Formula Builder is displayed.
  6. Select Show arguments in formula if it is not already selected.
  7. Enter a name for the field in Field Name.
    For example, Russ email.
  8. In the Formula Builder tab, double-click User Email in the Fields and Measures list.
  9. Double-click Starts With in the Functions list.
    The Formula box displays the text "User Email" StartsWith("TextFieldName", 'string expression').
  10. Edit this text as follows, StartsWith('User Email', 'rsgo').
  11. Click Validate to verify that the formula does not have any syntax errors.
    After you validate your formula, you can customize its summary calculation.

Creating a summary calculation

About this task

The Ad Hoc Editor creates a default summary calculation based on the type of formula you enter. Follow this procedure to select a different summary function.

Procedure

  1. Click the Summary Calculation tab.
  2. Select Mode from the Calculation menu.
  3. Click Create Field.
    After you select Create Field, a new calculated field appears in bold text at the end of the list of available fields. Calculated fields are designated by special icons.