Creating Formula Fields in SuiteAnalytics Workbook

You can customize formula fields by using SQL functions and context values. After you create formula fields, you can use them in your datasets and workbook visualizations. When you are working with custom formula fields, consider the following:

• Creating a Custom Formula Field in SuiteAnalytics Workbook

• Creating Criteria Filters Using Formula Fields

• Using Context Values in Formula Fields

• Adding Formula Fields to a Workbook Visualization

Creating a Custom Formula Field in SuiteAnalytics Workbook

In the following example, you create a formula field that converts date values to a different format. Remember that the Formula Field window includes only fields from the root record type on the dataset and any fields from related record types that have been added to the Data Grid.

Important:

To perform arithmetic calculations or view totals for fields that contain values in multiple currencies, you must first convert or consolidate the values to a specific currency. In the Dataset Builder, you can only do this using custom formula fields. For more information, see Currency Conversion Using Custom Formula Fields.

To create a custom formula field in SuiteAnalytics Workbook:

1. Click Formulas above the Records list on the Dataset Builder.

2. In the Fields list, click New Formula.

   The Formula Field window appears.

   

3. Enter a Formula Field Name.

   For this example, enter Formatted Date.

4. Select an Output Type for the formula field values.

   For the purposes of this procedure, select String.

   
   Note:

   Each output type only works with certain fields and formula functions. If you select an incompatible output type for the formula, the formula is invalidated and the Formula Builder highlights the error in the definition. To view details about the validation error and for information about how to correct the definition, click the error icon .

5. In the Formula field, enter the field IDs and SQL formula functions to use in the formula expression. Alternatively, double-click field IDs or formula functions from the Functions and Fields subtabs to add them to the expression.

   For the purposes of this procedure, select the TO_CHAR function and replace the content in the brackets with {trandate}, 'Month DD, YYYY'.

   

6. To validate the formula, click Validate.

   Note:

   If there are errors with the formula, click the error icon to view the details.

7. Click Apply to add the formula field to the dataset.

   The field appears in the Field lists on the Dataset Builder when you click Formulas. If you add the field to the Data Grid, it is also available for use in any workbook visualizations that are based on the dataset.

Creating Criteria Filters Using Formula Fields

In the following example, you use the Criteria Builder to create a filter using a formula field.

To create criteria filters using formula fields:

1. Create a valid formula field on the Dataset Builder.

2. Double-click or drag the field from the Fields list or the Data Grid to the Criteria Builder.

3. Set your filter conditions in the Filter window. For details, see Filter Types.

Using Context Values in Formula Fields

You can use different context values in some fields to define how values are displayed. For example, you can specify that a field displays results by showing the name of the field values instead of the ID.

The following example shows a formula definition where type is the field and display is the context value that defines how results are displayed. Context values are placed after the number sign # within the same parentheses containing the field name in a formula definition.

Example: {type#display}

Before you start working with context values, consider the following:

• Supported Context Values

• Editing Context Values

Supported Context Values

SuiteAnalytics Workbook supports the following context values:

Context Value	Description
#currency_consolidated	Displays consolidated currency amounts in the currency of the lowest level subsidiary that is a common parent to all the subsidiaries that you have access to in your account. The exchange rate used for the consolidation is the rate that is in effect on the date when the query or workbook visualization is run. For more information, see Currency in Datasets and Workbooks. Formula example: ID of the field used for this example: amount Formula output type: FLOAT SQL function required: TO_NUMBER() Formula definition: TO_NUMBER({amount#currency_consolidated})
#converted	Displays converted currency amounts in the base currency of your account. The exchange rate used for the conversion is the rate that is in effect on the date when the query or workbook visualization is run. For more information, see Currency in Datasets and Workbooks. Formula example: ID of the field used for this example: amount Formula output type: FLOAT SQL function required: TO_NUMBER() Formula definition: TO_NUMBER({amount#converted})
#converted[1]	Displays the currency amounts in the currency ID specified in the square brackets [1]. The exchange rate used for the conversion is the rate that is in effect on the date when the query or workbook visualization is run. For more information, see Currency in Datasets and Workbooks. Note: The currency ID 1 corresponds to the base currency. Formula example: ID of the field used this example: amount Formula output type: FLOAT SQL function required: TO_NUMBER() Formula definition: TO_NUMBER({amount#converted[1]})
#converted[1;yyyy-mm-dd]	Displays the currency amounts in the currency specified in the square brackets [1], using the exchange rate that is in effect on a specific date. You must enter the date using the YYYY-MM-DD format. By default, the currency ID is set to 1. For more information, see Currency in Datasets and Workbooks. Note: The currency ID 1 corresponds to the base currency. Formula example: ID of the field used for this example: amount Formula output type: FLOAT SQL function required: TO_NUMBER() Formula definition: TO_NUMBER({amount#converted[1;2020-03-10]})
#display	Displays more user-friendly field values. For example, for the type field on the transaction record type, the transaction type is displayed, such as bill, cash refund, and inventory adjustment. Formula example: ID of the field used for this example: type Formula output type: INTEGER SQL function required: none Formula definition: {type#display}
#raw	Displays raw field values (such as internal IDs). For example, for the type field on the transaction record type, using the raw value displays the transaction type ID. Note: The ID is a unique identifier that cannot be changed. If the name of a field changes, you can identify the field by getting the ID instead of the name. For example, the result is CashRfnd instead of cash refund. Formula example: ID of the field used for this example: type Formula output type: INTEGER SQL function required: none Formula definition: {type#raw}
#hierarchy	Displays more user-friendly field values for hierarchical fields. This context value is similar to #display but applies to hierarchical fields. Formula example: ID of the field used for this example: subsidiary Formula output type: STRING SQL function required: none Formula definition: {subsidiary#hierarchy}
#hierarchy_identifier	Displays more user-friendly field values for hierarchical fields. This value is similar to #display but applies to hierarchical fields Formula example: ID of the field used for this example: subsidiary Formula output type: FLOAT SQL function required: TO_NUMBER() Formula definition: TO_NUMBER({subsidiary#hierarchy_identifier})

Editing Context Values

In the following example, you can change the context value of the type field. By default, the type field displays results by showing the name. If you change the context value, the field displays results by showing the ID.

Note:

This example is applicable for any record type that includes the type field. The following example is the transaction record type, available in the Transaction Detail dataset template.

To edit the context value in a formula

1. Click Formulas above the Records list on the Dataset Builder.

2. In the Fields list, click New Formula.

   

   The Formula Field window appears.

3. Enter a Formula Field Name.

   For example, enter Type (ID).

4. Select an Output Type for the formula field values.

   For the purposes of this procedure, select String.

5. Double-click fields from the Fields subtab to add them to the FORMULA text box.

   For the purposes of this procedure, double-click Type.

   The formula appears in the Formula field. In this example, {type#display}.

6. In the Formula field, replace the content after the number sign with the value that you want to add.

   For the purposes of this procedure, remove #display and enter #raw.

7. To validate the formula, click Validate.

   Note:

   If there are errors with the formula, click the error icon (warning) to view the details.

8. Click Apply to add the formula field to the dataset.

   The field appears in the Field lists on the Dataset Builder when you click Formulas. If you add the field to the Data Grid, it is also available for use in any workbook visualizations based on the dataset. When you use this field in workbook visualizations, the type field displays the ID of the values.

Adding Formula Fields to a Workbook Visualization

In the following example, you add a formula field to a workbook and then place it on a table, pivot, or chart of the workbook.

To add formula fields to a workbook visualization:

1. Create a valid formula field on the Dataset Builder.

2. Add the field to the Data Grid so that it is included in any workbook visualizations based on the dataset.

3. On the Table, Pivot, or Chart tabs of the workbook, drag the field from the Dataset Panel to a section of the Layout panel.

Related Topics

Formula Fields

Advanced Sample Formula Fields

General Notices