Custom Fields

You can create new fields in a report by applying mathematical formulas to an Ad Hoc report's existing numeric fields. For example, consider a report that includes both a Cost and a Revenue field. You could calculate the profit for each record by creating a custom field that subtracts the Cost field from the Revenue field. Create a custom field by selecting an existing field in the list of fields or in the report area of a table report; the Create Custom Field option appears on the context menu when
you click a numeric field.

The Ad Hoc Editor supports two types of functions:

• Basic functions include addition, subtraction, multiplication, and division. You can use these functions with a constant (such as multiplying the cost by two to calculate a standard 50% markup), or you can use them with multiple fields. Select multiple fields using Ctrl-click; the columns' borders change color to indicate that multiple fields are selected. 
• Special functions include round, percent of total, and rank. These functions don't take constants, nor do they support multiple fields.

A custom field can only include a single function. To use more than one function to create the field you need, create two custom fields, with one building on the other. In this case, pay special attention to the order in which you create the custom fields, as this may affect the results; for example, rounding then multiplying yields different results than multiplying then rounding.

Because custom fields can build upon one another, you can create complex calculations. For example, you could divide the Profit custom field in the previous example by the Revenue field to express each record's margin as a percent. When a custom field is the basis of another field, you can't delete it until you delete the one that builds on it.

When working with multiple fields:

• You can only select basic functions.
• You can multiply or add any number of fields, but the ordered functions (subtract and divide) can only be used with two fields at a time.
• For ordered operations, the order in which you select fields matters. For example, to calculate profit in the example above, click Revenue first, then Ctrl-click Cost. When you click Create Custom Field > Subtract #, the Ad Hoc Editor assumes you are subtracting the Cost field from the Revenue field. You can always change the order of fields using the context menu's Swap Fields option.

To create a custom field based on one field:

1. In the list of fields, click a numeric field and select Create Custom Field from the context menu.
2. Depending on the type of function you want to create, click Basic Functions or Special Functions.
3. If you selected a basic function, select Add #, Subtract #, Multiply #, or Divide #, and then enter a number in the field that appears; this number is the constant to use in the formula.
4. If you selected a basic function, click Swap Field and Number to put the constant before the field in the formula; this is only useful in conjunction with ordered functions. Note that you can toggle the order of the field and function from the context menu (Edit Formula > Swap Number and Field).
5. Click Create Field. The custom field appears in the list of fields.

To create a custom field based on multiple fields:

1. In the list of fields, Ctrl-click two or more numeric fields, then click Create Custom Field from the context menu.
2. If it is not expanded, click Basic Functions, and select Add, Subtract, Multiply, or Divide. (You can only subtract or divide when exactly two fields are selected)
3. Click Swap Fields from the context menu to change the order of the fields in the formula; this is only useful in conjunction with ordered functions.
4. Click Create Field. The custom field appears in the list of fields.

Notes:

• To edit an existing custom field, click it and select Edit Formula from the context menu. You can then choose a different function, or enter a different value (if you are editing a basic function).
• When you create a custom field based on a field in the table, the new field appears in the report area; when you create it from a field in the list of fields, it appears at the bottom of the list.
• The Round function uses the standard method of rounding (arithmetic rounding), in which values greater than 0.5 are rounded to the next largest whole number.
• A custom field's label is determined by the fields, constants, and functions it includes; for example, the default label for the Profit field in our example above is Store Sales - Store Cost. Click the column and select Edit Label to enter a more compelling name.
• By default, the Ad Hoc Editor supports only two decimal places. If your custom fields return data that are significant to the third decimal place, you can add new masking options by editing configuration files.
• You can't delete a custom field that is used in the report area. First remove the custom field from the report area and then delete it from the list of available fields.