Problem Statement

Oracle Analytics Cloud (OAC) lacks the ability to apply custom format patterns to measures while preserving their numeric properties for aggregation. When users need specialized formatting patterns that aren't available in the standard OAC formatting options, they're forced to use TO_CHAR() or other string conversion functions, which convert the measure to a string and break aggregation capabilities.

Missing Custom Format Patterns in OAC

Currently in OAC, there's no way to implement the following custom formats while preserving measure properties:

1. Custom unit suffixes - Displaying values with specific suffixes like "pp" for percentage points (e.g., "3.5pp" instead of "3.5%")
2. Custom prefix combinations - Adding specialized prefixes like "+/-" or "Δ" to values
3. Domain-specific notations - Financial formats like "bps" (basis points), scientific notations with custom exponents
4. Mixed alphanumeric formats - Formats like "Q2: 45.3M" that combine text and numeric elements

Example Use Case

A performance dashboard tracking KPI changes needs to display:

• Market Share Change: +2.7pp (percentage points)
• Customer Satisfaction: +0.8pts (points on a scale)
• Response Time: -15.3ms (milliseconds)

Currently in OAC:

• Using TO_CHAR(Market_Share_Change, '0.0') || 'pp' creates a string value
• This formatted string can't be used in pivot tables' value areas
• The measure loses its ability to be aggregated or used in calculations
• Users must maintain duplicate measures (one raw, one formatted)

Proposed Solution

Introduce a FORMAT_MEASURE() function in OAC that:

• Accepts custom format patterns including text elements
• Preserves the numeric measure properties for aggregations
• Supports custom suffixes like "pp", "pts", "bps", etc.
• Works consistently across visualizations

Implementation could follow this syntax:

FORMAT_MEASURE(SUM("Market_Share_Change"), '0.0"pp"')FORMAT_MEASURE(AVG("Customer_Satisfaction"), '+0.0"pts"')FORMAT_MEASURE("Response_Time_Diff", '-0.0"ms"')

Benefits

1. Enables domain-specific reporting with proper terminology and notation
2. Eliminates the need for duplicate measures (one for calculation, one for display)
3. Improves dashboard clarity with specialized formatting
4. Maintains calculation capabilities while adding custom display formats
5. Reduces complexity in dashboard and RPD development
6. Provides consistency with how other BI tools handle custom formatting

This enhancement addresses a significant gap in OAC's formatting capabilities, particularly for specialized business domains that require custom measurement units and notations while maintaining the analytical capabilities of the measures.