BI Catalog Builder - Using Oracle Functions in Calculated Fields

• BI Catalog Builder - Using Oracle Functions in Calculated Fields
  • Overview
  • Process
    • Step 1: Select Calculated Field
    • Step 2: Edit Calculation
  • Oracle Functions
    • Null Value Function
    • CASE Statement Function
    • Windows Function
    • Decode Function
    • CONCAT Function

Overview

This article describes how to add Oracle functions to calculated fields in BI Catalog Builder, as well as providing formulas and examples for commonly used functions.

Process

This process describes how to update calculated fields with Oracle functions. Functions can also be applied when creating new calculated fields.

Step 1: Select Calculated Field

Update Calculated Field pop-up launched for [Edit Calculated Field] button

In BI Catalog Builder, select a calculated field and press the Edit Calculated Field button to launch the Update Calculated Field pop-up, as shown in the screenshot above. For more information, please refer to CMiC Analytics: Catalog Builder - Data Sources.

Step 2: Edit Calculation

Update Calculated Field pop-up launched for [Edit Calculated Field] button

In the Calculation field, add/update the calculation with the Oracle function(s) and press the Validate Calculation button to validate the changes.

Oracle Functions

The following Oracle functions are described in detail with examples:

• Null Value Function

• CASE Statement Function

• Windows Function

• Decode Function

• CONCAT Function

For more information on Oracle functions, please visit Oracle - Functions.

Null Value Function

As null or blank values are not recognized as a defined data type, calculations involving null values will always return a null or blank value. The NVL function is used to substitute a value (e.g., zero) when a null value is encountered, which helps to avoid returning null calculated values.

Calculation

The syntax for the NVL function is:

NVL(field, replace_with)

with the following parameters:

• field: The field value to test for a null value

• replace_with: The value returned if "field" is null

Example

If an amount with a null value is used, the calculation will return a null value:

Profit = Bill_Amount - Cost_Amount= Null - 6,000 = Null

Applying the NVL function to the above, the null value will be assigned a substitute value (in this case, zero) such that

Profit = NVL(Bill_Amount,0) - NVL(Cost_Amount,0)

Profit = 0 - 6000 = - 6000

CASE Statement Function

The Oracle CASE statement has the functionality of an If-Then-Else statement.

Calculation

The syntax for the CASE statement function is:

CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result _2
...
WHEN condition_n THEN result_n
ELSE result
END

with the following parameters:

• condition_n: The conditions listed in order of evaluation.

• result_n: The value returned once a condition is found to be true.

Example

A user wants to view which invoices are overdue by 1 to 4 days and then apply a filter on the resulting values. The CASE statement function is applied to set the field value as "Yes" when the Due_Days field is between 1 and 4.

CASE
WHEN NVL(DUE_DAYS,0) BETWEEN 1 and 4 THEN 'YES'
ELSE 'NO'
END

Windows Function

The Oracle Windows function is an advanced tool used to perform calculations across a set of query rows. The Windows function performs aggregate operations or ranking functions on groups of rows and produces a result for each row. Some examples of window functions include: SUM(), AVG(), MIN(), etc.

For more information on the Oracle Windows function, please visit Window Function Concepts and Syntax.

Calculation

The syntax for the Windows function is:

Windows_Function(column_1) OVER (PARTITION BY column_2 ORDER BY column_3) AS windows_fuction_value

with the following clauses:

• OVER(): This clause will construct a window.

• PARTITION BY: This clause will form rows into groups of rows

Example

A user would like to sum over the job code to calculate the profit margin. They would use the SUM() Windows function, as shown in the screenshot below:

Decode Function

The Oracle Decode function is used to add the procedural If-Then-Else logic to queries.

Calculation

The syntax for the Decode function is:

DECODE(expression, search_1, result_1, search_2, result_2... , default )

with the following parameters:

• expression: The value to be compared.

• search_n: The value to be compared against the expression.

• result_n: The value to return if the expression is equal to the search.

• default: The value to be returned if no matches are found.

Example

A user would like to separate the amount by a different type. The following formula will display an amount that has the type code "C", with a default value of "0".

DECODE(JCDT_TYPE_CODE,'C',JCDT_AMT,0)

CONCAT Function

The Oracle CONCAT function is used to concatenate (i.e., put together in a connected series) two values and return a combined value.

Calculation

The syntax for the CONCAT function is:

CONCAT (string1, string2)

with the following parameters:

• string1: The first string value (to be concatenated with the second string value).

• string2: The second string value.

Example

A user would like to show calculated values as a percentage, with a percent sign. The following formula would put together the percentage calculation with a percent sign, while also rounding it to two decimal places.

CASE
WHEN NVL(JCAT_BUDG_AMT,0) = 0 then '0%' 
ELSE CONCAT(ROUND((NVL(JCAT_BILL_AMT,0) / NVL(JCAT_BUDG_AMT,0))*100,2),'%') 
END