Using Formula Columns in Business Views

[RichC]

Introduction

A formula column contains an expression that, depending on the expression and the expression context, returns a scalar or an array of values of a specific data type. An expression can consist of built-in functions, built-in system variables, and other referenceable objects valid for the expression context.

What you should know before reading this article

We recommend that you be familiar with these concepts in Incorta Docs.

• Formula Column
• Formula Expression
• Incorta View Formula Column
• Business Schema View Formula Column

Applies to

Applies to all Incorta versions, however as a growing feature, there are additional options as well as syntax enforcement in release 4.8 and up.

Let's Go

Business Schema Formula Columns

Using the Formula Builder, there are several ways that you can create a formula column.  You can add a Formula to an Incorta Insight directly, however this will only be calculated at run time and will have several limitations.  These limitations can be addressed by adding the formula upstream of the insights in either a Business or Physical Schema.  It is best done in an insight to quickly validate a data value or for a case where the actual formula will be specific to the Insight and not be reusable across multiple Insights.

Here are some use cases for moving formulas into a Business Schema:

• Reuse a specific formula across multiple Insights
• Need to change the data type.  Implicit casting works between Long and Integer only.  Examples:
• String to Integer or Long
• Long or Integer to String
• Timestamp to Date
• String process, such as substring, concatenation
• Date or Timestamp to String by taking components and form a meaningful string, such as period name
• Parse a string as a date
• Change a number, such as epoch or a JDE date to a Date column
• Incorta by default disables sorting based on a formula in a list report. You must  define a formula column to be used in a formula as a sorting attribute.   
• Resolve the dependencies between formulas. A Schema formula column cannot be used in another Schema formula column but can be used in a Business Schema formula column.