OTBI Calculated Fields & Custom Expressions — Complete Guide with Examples

OTBI calculated fields are custom columns you create directly in Oracle Analytics/OTBI that compute their values from existing columns using expressions. Unlike table columns that come pre-built from the database, calculated fields let you transform, combine, and derive data at query time. This guide covers how to build them, 15 production-ready examples, and how to avoid common mistakes.

What Are OTBI Calculated Fields?

A calculated field is a virtual column built from an expression — a formula that references other columns, functions, and constants. When you add a calculated field to a report, OTBI evaluates the expression for each row of data and displays the result. The expression uses OBIEE Logical SQL, not standard SQL, which means it has some special functions and syntax.

Examples of calculated fields you might build:

• Tenure in years: How long an employee has been with the company (today minus hire date)
• FTE headcount: Full-time equivalent count (sum of employees * their FTE percentage)
• Salary band classification: Junior, Mid, Senior based on salary ranges (CASE/WHEN)
• Compensation ratio: Employee salary vs median for their role (avoid-divide-by-zero pattern)
• Turnover flag: 'Active' or 'Terminated' status (simple conditional)

When to Use Calculated Fields vs Standard Columns

Use a calculated field when you need a one-off computation for a specific report or rarely repeat the same logic across reports. Use a database view or table when you're computing the same thing in many reports or when the calculation is complex and performance-critical.

How to Create a Calculated Field in OTBI

Step 1: Open Your Report or Data Set

In Oracle Analytics/OTBI, open an existing report or create a new one. Add the base columns you want to work with (e.g., hire date, salary, department).

Step 2: Locate the "New Calculated Field" Option

In the Data panel or column list, look for a + button or "New Calculated Field" link. In some versions, it's under the Data Source menu. Click to open the Calculated Field editor.

Step 3: Name Your Field

Give it a descriptive name: Tenure Years, Compa Ratio, Status Flag. This name will appear as a column in your report.

Step 4: Write Your Expression

Click in the Expression box and type your OBIEE Logical SQL formula. Use double quotes around column names: "Employees"."Hire Date". The editor may offer autocomplete for column names and functions.

Step 5: Test and Save

Click "Validate" or "Test" to check for syntax errors. If it passes, click "OK" or "Save". The field will appear in your report immediately.

Essential Functions Reference Table

This table covers the most common OBIEE Logical SQL functions used in calculated fields. All examples use the double-quote syntax for columns.

15 Real-World Expression Examples

1. Tenure in Years (Simple Date Subtraction)

TIMESTAMPDIFF(SQL_TSI_YEAR, "Employees"."Hire Date", CURRENT_DATE)

Calculates years since hire date. Uses SQL_TSI_YEAR to get full years. Replace with SQL_TSI_MONTH for months, SQL_TSI_DAY for days.

2. Tenure in Years and Months (Combined)

CONCAT(
  TIMESTAMPDIFF(SQL_TSI_YEAR, "Employees"."Hire Date", CURRENT_DATE),
  ' years, ',
  MOD(TIMESTAMPDIFF(SQL_TSI_MONTH, "Employees"."Hire Date", CURRENT_DATE), 12),
  ' months'
)

Returns "5 years, 3 months". Combines TIMESTAMPDIFF for years, MOD to get months only (remainder after dividing total months by 12), and CONCAT to format the result.

3. Age Calculation from Date of Birth

TIMESTAMPDIFF(SQL_TSI_YEAR, "Employees"."Date of Birth", CURRENT_DATE)

Similar to tenure, but uses date of birth to calculate age. Always use CURRENT_DATE (not CURRENT_TIMESTAMP) for age calculations to avoid day-of-month mismatches.

4. FTE Headcount (Full-Time Equivalent)

SUM("Employees"."FTE Percentage") / 100

Sums up FTE percentages (e.g., 0.5 for half-time) and divides by 100 to convert from percentage to decimal. This is a measure, so it aggregates per dimension (e.g., per department).

5. Salary Band Classification (CASE/WHEN)

CASE
  WHEN "Compensation"."Salary" < 50000 THEN 'Band A: Entry'
  WHEN "Compensation"."Salary" < 75000 THEN 'Band B: Mid'
  WHEN "Compensation"."Salary" < 100000 THEN 'Band C: Senior'
  ELSE 'Band D: Executive'
END

Bucketing salary into tiers. Each WHEN is evaluated top-to-bottom; first match wins. Include an ELSE for any edge cases.

6. Turnover Rate Percentage

CASE
  WHEN SUM("Employees"."Starting Headcount") = 0 THEN 0
  ELSE ROUND(SUM("Employees"."Terminations") / SUM("Employees"."Starting Headcount") * 100, 2)
END

Avoids divide-by-zero by checking if denominator is 0 first. Returns 0 if no starting headcount, otherwise calculates percentage. ROUND to 2 decimals for readability.

7. Time to Fill (Days Since Requisition Opened)

TIMESTAMPDIFF(SQL_TSI_DAY, "Recruitment"."Requisition Open Date", CURRENT_DATE)

Measures days a position has been open. Use this to track recruitment velocity and hiring delays. Filter to open requisitions only (status = 'OPEN') for current time-to-fill.

8. Absence Rate as Percentage

ROUND(
  SUM("Absence"."Absence Hours") /
  (SUM("Absence"."Absence Hours") + SUM("Timecard"."Hours Worked"))
  * 100,
  2
)

Ratio of absence hours to total hours. Wrap in NULL-safe CASE to avoid divide-by-zero if no hours are recorded.

9. Manager vs Individual Contributor Flag

CASE
  WHEN "Employees"."Manager ID" IS NOT NULL AND "Employees"."Reports Count" > 0 THEN 'Manager'
  ELSE 'Individual Contributor'
END

Identifies managers by checking if they have a manager ID and direct reports. Useful for org structure analysis and leadership dashboards.

10. Compa-Ratio (Actual Salary vs Midpoint)

CASE
  WHEN "Salary Grade"."Midpoint Salary" = 0 THEN NULL
  ELSE ROUND("Compensation"."Salary" / "Salary Grade"."Midpoint Salary", 3)
END

Shows how an employee's salary compares to the grade midpoint (1.0 = at midpoint, 0.9 = 10% below, 1.1 = 10% above). Essential for pay equity analysis. Returns NULL if midpoint is 0 to avoid errors.

11. Custom Date Formatting (ISO to Human-Readable)

CONCAT(
  SUBSTR(CAST("Employees"."Hire Date" AS VARCHAR), 6, 2),
  '/',
  SUBSTR(CAST("Employees"."Hire Date" AS VARCHAR), 9, 2),
  '/',
  SUBSTR(CAST("Employees"."Hire Date" AS VARCHAR), 1, 4)
)

Converts ISO date (2026-06-01) to MM/DD/YYYY format (06/01/2026). CAST to VARCHAR, then SUBSTR to extract parts. Not needed if OTBI date formatting options suffice.

12. Full Name Concatenation (NULL-Safe)

TRIM(CONCAT(
  COALESCE("Employees"."First Name", ''),
  ' ',
  COALESCE("Employees"."Last Name", '')
))

Combines first and last name, handling NULLs gracefully. COALESCE replaces NULL with empty string, CONCAT concatenates, TRIM removes extra spaces. Safe even if either name is missing.

13. Null-Safe Division (Avoid Divide by Zero)

CASE
  WHEN "Performance"."Target" = 0 OR "Performance"."Target" IS NULL THEN NULL
  ELSE ROUND("Performance"."Actual" / "Performance"."Target", 2)
END

Returns NULL (not an error or 0) if the denominator is zero or NULL. This prevents misleading values when data is incomplete. Use this pattern anytime you divide two metrics.

14. Period-over-Period Change (YoY Growth)

ROUND(
  ("Current Year Sales" - "Prior Year Sales") /
  "Prior Year Sales" * 100,
  2
)

Calculates year-over-year growth percentage. Assumes you have separate measures for current and prior year. Multiply by 100 to get percentage.

15. Active vs Terminated Status Filter

CASE
  WHEN "Employees"."Termination Date" IS NULL THEN 'Active'
  WHEN "Employees"."Termination Date" <= CURRENT_DATE THEN 'Terminated'
  ELSE 'Future Termination'
END

Classifies employees by termination status. Used to separate active headcount from separated employees. Handles future terminations (notice given) as a separate category.

Common Mistakes & Fixes

Mistake 1: Column Name Typo or Missing Quotes

Error: "Column Hire Date not found" or "Invalid column reference"

Fix: Always use double quotes around column names, and match the exact name from the subject area. Column names are case-sensitive in OBIEE Logical SQL. Use autocomplete in the expression editor to avoid typos.

-- WRONG: Missing quotes
TIMESTAMPDIFF(SQL_TSI_YEAR, Hire Date, CURRENT_DATE)

-- RIGHT: Double quotes
TIMESTAMPDIFF(SQL_TSI_YEAR, "Hire Date", CURRENT_DATE)

Mistake 2: Data Type Mismatch

Error: "Type mismatch: cannot mix DECIMAL and VARCHAR" or "Invalid conversion"

Fix: Ensure all operands in an expression are compatible types. Use CAST to convert: CAST("Salary" AS DECIMAL) or CAST("Hire Date" AS VARCHAR). OTBI is strict about types.

-- WRONG: Trying to concatenate a number with text (no CAST)
CONCAT("Salary", " dollars")

-- RIGHT: CAST salary to VARCHAR first
CONCAT(CAST("Salary" AS VARCHAR), " dollars")

Mistake 3: Using Non-Aggregate Functions in Aggregate Context

Error: "Cannot mix aggregate and non-aggregate functions" or "Column must be in GROUP BY clause"

Fix: If you're using an aggregate (SUM, COUNT, AVG), all non-aggregated columns must be in the GROUP BY. Alternatively, nest non-aggregated columns inside an aggregate if they're the same across the group.

Mistake 4: NULL Handling

Error: Reports show NULL or blank values unexpectedly

Fix: Wrap expressions that might return NULL with COALESCE, IFNULL, or CASE. NULL propagates through most operations (NULL + 5 = NULL). Always check the source columns for missing values.

-- WRONG: If Bonus is NULL, entire result is NULL
"Salary" + "Bonus"

-- RIGHT: Treat NULL as 0
"Salary" + COALESCE("Bonus", 0)

Mistake 5: Date Format Issues

Error: TIMESTAMPDIFF returns 0 or wrong values; date comparisons don't work

Fix: Ensure the column is actually a DATE or TIMESTAMP type in the database. If it's stored as VARCHAR, CAST it first: CAST("Hire Date Str" AS DATE). Always use ISO format (YYYY-MM-DD) when comparing dates manually.

Performance Tips & Optimization

Tip 1: Avoid Expensive Functions in Large Data Sets

SUBSTR, REPLACE, and CONCAT are slow on millions of rows. If possible, use the database view to pre-compute these, then reference the view column in OTBI.

Tip 2: Use Aggregates Wisely

Calculated measures with SUM, COUNT, or AVG can slow down large queries. If the same aggregate is used in many reports, create a pre-aggregated table in the database and reference it directly.

Tip 3: Filter Early, Not in Calculated Fields

If you're filtering to active employees, do it with a report filter, not a CASE statement inside a measure. Filters reduce the data volume before calculation; CASE statements run on all rows, then filter results.

Tip 4: Test with Sample Data

Create your calculated field against a small date range or subset of users first. Once it works, expand to full data. This helps catch logic errors before they hit large data sets.

Tip 5: Monitor Query Time

If a report with calculated fields suddenly gets slower, the calculated field might be the culprit. Profile the query using OTBI's "Query Builder" or database logs to identify bottlenecks.

Frequently Asked Questions

Q: Can I use parameters or prompts in calculated fields?

No. Calculated fields are evaluated per-row at query time; they don't have access to report parameters. However, you can use calculated fields to compute a column, then filter that column by a parameter. Example: Create a "Salary Band" calculated field, then add a report filter "Salary Band = @SelectedBand".

Q: How do I debug a calculated field that returns unexpected values?

Add the expression components as separate calculated fields first to isolate the problem. For example, if TIMESTAMPDIFF isn't working, add a field that just returns "Hire Date", another that returns CURRENT_DATE, then combine them. This helps pinpoint which part is broken.

Q: Can calculated fields reference other calculated fields?

Yes, in most OTBI versions. Create Field A, then in Field B's expression, reference Field A by name (in quotes). Test this in your environment first, as older OTBI versions may not support it.

Q: What's the maximum complexity of an expression?

OBIEE Logical SQL can handle deeply nested expressions (CASE inside CASE inside CONCAT, etc.), but readability and performance suffer. Aim for 3-4 levels of nesting max. For very complex logic (10+ CASE statements), use a database view instead.

Q: Can I use window functions (ROW_NUMBER, RANK, LAG) in calculated fields?

Depends on your OTBI version and underlying database. Modern versions support window functions, but they're typically slower than database-native views. Test in a non-production environment first.

Q: How do I export calculated field definitions for reuse?

Calculated fields are stored with the report, not globally. To reuse an expression across multiple reports, either (a) document the expression and manually recreate it, or (b) create a database view and reference it as a standard column in all reports. Option B is cleaner and more maintainable.

Quick Checklist for Building Calculated Fields

1. Identify the columns and functions you need
2. Use double quotes around column names
3. Use OBIEE Logical SQL syntax (not standard SQL)
4. Test with a simple expression first (single CONCAT or CASE)
5. Add NULL-handling (COALESCE, CASE) for edge cases
6. Validate the expression in the OTBI editor
7. Run a report with sample data to verify correctness
8. Document the expression for team reference
9. If reusing in 3+ reports, move to a database view instead