About formula expressions

Use formula expressions to calculate the values of calculated columns. You can use any combination of:

• Basic mathematical operators ( +, -, /)
• Common SQL functions (such as Round ( ) )
• SQL language expressions (such as CASE)
• Any primary or related column name (as a variable).

For example, if you want to add a New Cost Change calculated column, you might specify the following formula expression:

CASE
            WHEN [Current Cost] is null
            or [Current Cost] = 0 THEN 0
            ELSE ([New Cost] - [Current Cost]) / [Current Cost]
END

where Current Cost and New Cost are columns in the same worksheet and their values are variables in the expression.

Following is a complete list of tested and supported SQL functions and language expressions.

Supported SQL language expressions

CASE

Supported SQL functions

• AVG()
• CAST()
• CEILING()
• COALESCE()
• CONVERT()
• COUNT()
• COUNT(DISTINCT)
• DATEDIFF()
• DATEADD()
• FORMAT()
• GETDATE()
• ISNULL()
• MAX()
• NULLIF()
• ROUND()
• SUM()

Different field data types to use in the Column definition

The Column settings dialog has a Fields list that contains every column from the primary and related data sources in that worksheet. Each column listed has a symbol next to its label. These symbols indicate the value type of each column, which is the conceptual type of data contained in the field.
You can use these symbols to better understand whether a specific field might be useful in the Column Definition formula for a calculated field.

List of symbols

• String
• Integer Number Percentage
• Currency
• Date
• DateTime
• Boolean
• Calculated