Calculations: EVALUATE, FILTER, and Level-Based Measures

Explain how EVALUATE, FILTER, and level-based (context-changing) formulas let analysts write calculations OBIEE's standard aggregation model can't express directly.

OBIEE's function library covers most business calculations natively, but interviewers specifically probe EVALUATE and FILTER because they represent the "escape hatches" senior developers reach for when the standard aggregation model hits its limits — and knowing when (and when not) to use them is a real differentiator.

FILTER is like asking "what would my grade be if only my math scores counted?" without retaking the whole semester. EVALUATE is like handing a translator a note in the destination language directly, bypassing your own broken grammar entirely. Level-based measures are like posting the country's total population next to every city's row in a table, even though the table itself is sorted city-by-city.

Key Concepts

FILTER lets you compute a measure using a different filter context than the surrounding analysis, without needing a second analysis — e.g., computing "US Revenue" as a column inside an analysis that's otherwise filtered to a different set of regions, by wrapping the base measure: FILTER("Sales Facts"."Revenue" USING "Region"."Country" = 'US'). This is invaluable for building "this vs. that" comparison columns (like showing total company revenue alongside a region-filtered view) in a single row of a report.

FILTERFILTER("Sales Facts"."Revenue" USING "Region"."Country" = 'US')

EVALUATE is the true escape hatch: it passes a literal database-specific function or expression straight through to the physical SQL, bypassing OBIEE's logical function library entirely — used for advanced statistical functions, database-specific analytics functions (like Oracle's REGR_SLOPE or PERCENTILE_CONT), or any calculation the BI Server doesn't natively support. Because EVALUATE sends raw syntax to the physical database, it's inherently non-portable across data sources and must be explicitly enabled (EVALUATE_SUPPORT_LEVEL in NQSConfig.INI) for security and portability reasons — this configuration detail is a strong interview signal when mentioned.

EVALUATEREGR_SLOPEPERCENTILE_CONTEVALUATE_SUPPORT_LEVELNQSConfig.INI

Level-based measures solve a subtler problem: showing a value "at a coarser grain than the current report" (e.g., showing "Category Total" as a repeated column alongside SKU-level detail rows) by explicitly setting the calculation's aggregation level in the BMM (or using AGO/TODATE-style time-series functions, which are really level-based calculations against a Time hierarchy). A well-rounded answer connects all three techniques back to the same underlying theme: standard aggregation assumes "aggregate at the report's current grain," and these tools let you deliberately break that assumption when the business question requires it.

Level-based measuresAGOTODATE