Page Comparison

You can create new fields in a report or measures in an Ad Hoc view by applying mathematical formulas to an Ad Hoc report's existing numeric fieldsa view’s existing fields and measures. For example, consider a report view 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 For example, you can divide the Profit 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:

...

in the previous example by the Revenue field to express each record’s margin as a percent.

The Calculated Field or Calculated Measures dialog box allows you to create a calculated field or measure and set its summary function. This section describes the functionality available in this dialog box.

To open the calculated fields dialog box for Ad Hoc views:

1. Create or open an Ad Hoc view.
2. Open the calculated fields dialog using one of these methods:

• Click at the top right of either the Fields section or the Measures section of the Data Source Selection panel and select "Create Calculated Field..." from the context menu.
• Right-click on an existing calculated field (shown by the icon Image Added) or calculated measure  and select Edit.

The dialog displays a text field for the name and two tabs, Formula Builder and Summary.

Creating a Calculated Field

To create a calculated field

Procedure

1. First, create the Ad Hoc view to use. To do this, select Create > Ad Hoc View from the menu. The Select Data wizard appears.
2. Click Image Added and navigate to Domains.
3. Select Supermart Domain. Click Choose Data. The Data Chooser window appears.
4. In the Data Chooser window, double-click Sales to select it and click OK. A new Ad Hoc view opens.
5. In the Ad Hoc view, click Image Added at the top right of the Fields section and select Create Calculated Field... from the context menu. The New Calculated Field dialog box appears, displaying the Formula Builder.

1. Enter Volume Tier for the Field Name.

Creating the formula

This section shows how to create a simple text formula that says Low when the Unit Sales amount is under 100 and High otherwise.

Formulas must use the following syntax:

1. Labels for fields and measures must be in double quotes ("): "Customer ID", "Date ordered".
2. Text must be in single quotes ('): '--'.
3. Levels must be in single quotes ('): 'ColumnGroup', 'Total'.

1. Make sure Show arguments in formula is selected.
2. Now create the formula. Double-click IF() in the Functions list.
   Because Show arguments in formula is selected, IF("BooleanFieldName", TrueCalc, FalseCalc) is entered in the Formula Builder.
3. Double-click BooleanFieldName to select it, then double-click Unit Sales 2013 in the Fields and Measures list.
   The Formula Builder displays IF("Unit Sales 2013", TrueCalc, FalseCalc).
4. Edit the expression in the Formula Builder to read as follows: IF("Unit Sales 2013" IN (0:100), 'Low', 'High').
5. Click Validate to verify that the formula does not have any syntax errors.

Creating a summary calculation

The Ad Hoc Editor creates a default summary calculation based on the type of formula you have entered. This section shows how to select a different summary function.

1. Click the Summary Calculation tab.

1. Select Mode from the Calculation menu.
2. Click Create Field.
   The calculated field appears in bold text at the bottom of the list of available fields. A special icon indicates it is a calculated field Image Added.

If you have installed the samples, the Ad Hoc view 10. Calculated Fields and Measures includes examples of calculated fields and measures (indicated by Image Added). You can explore their formulas by right-clicking the field or measure name and selecting Edit. You can also create tables, charts, or crosstabs to see how these calculations work in views.

•  The following are reserved words and cannot be used as field names: AND, And, and, IN, In, in, NOT, Not, not, OR, Or, or. Names containing these strings, such as "Not Available", can be used.

The Formula Builder Tab

The Formula Builder tab is where you create the formula for your calculated field or measure. This tab includes the following:

• Formula entry box — Shows the current formula for calculating your field or measure. You can edit the formula by typing directly in the panel. You can also add Fields, Measures, and Functions by double-clicking them. Click the buttons below the Formula field to add operators. Formulas must use the following syntax:
  •  Labels for fields and measures must be in double quotes ("): "Customer ID", "Date ordered".
  •  Text must be in single quotes ('): '--'.
  •  Levels must be in single quotes ('): 'ColumnGroup', 'Total'. 

• Operator buttons — Click these buttons to insert the operator in the Formula entry box.

• Fields and Measures — Lists all the fields and measures currently in your Ad Hoc view, including any calculated fields or measures you have already created.

• Functions — Lists all the available functions you can use in your formula. 

• Function Description panel — Gives a brief description of the function selected in the Functions list, if any. The sample inputs are intended to be as descriptive as possible. 

• Show arguments in formula checkbox — When this checkbox is selected, double-clicking a function name in the Functions list adds the full description to the Formula entry box; when the checkbox is not selected, double-clicking a function name adds only the function. For example, double-clicking Round adds Round("NumberFieldName", Integer) when the checkbox is selected, and adds Round() when the checkbox is not selected. If you select this checkbox, you can double-click on a string, such as NumberFieldName, and then replace it by double-clicking a name in the Fields and Measures list.

• Validate button — Checks the formula for syntax errors, such as missing parentheses or quotes. Your calculated field or measure must be valid before you can create it. Syntax validation does not guarantee that your formula will give the results you want.

The Summary Tab

Summaries show a result applied to all data values. For example, for a numeric fields such as Cost, the summary value might be the sum of all the costs; for a text field such as Customer Name, the summary value might be the count of all customers. The Summary tab lets you set the default summary function for your calculated field or measure.

• Calculation list — Displays allowed summary functions for your calculated field or measure. The available options depend on the data type of the calculation. See Summary Calculations for more information. Depending on your selection, you may see additional options:
  • Custom selection — Displays the same options available in the Formula Builder tab, including the Formula entry box, operator buttons, Fields and Measures list, Functions list, and Validate button. You use these options to build a formula for your custom summary. However, for summaries, you are limited to aggregate functions, that is, functions that operate on all the values in your field. For example, Sum and Mode are valid summary functions, because they use all available field values to get a result. Round is not a valid summary function, because it operates on a single value at a time. 
  • Weighted Average — Displays a Weighted On drop-down list, which allows you to choose another field or measure to use as the weight for the average.