Oracle12c与OBIEE中报表自定义列匹配百分比值可行性问询

Hey there! Let’s walk through exactly how to build this custom column you need. From your example about Emilian's customer revenue percentages, I’m guessing you want to map values in the Percent column to either 1 or 2 based on a specific condition (like "if percent is above X, show 1; else show 2"—if that’s not your exact rule, you can tweak the logic easily).

Option 1: Build the Custom Column Directly in OBIEE #

This is the quickest way if you want to handle the logic right in your report:

• Open your analysis in OBIEE’s Analysis Editor, head to the Columns tab.
• Click New Calculated Column, name it something clear like Status_Flag.
• In the Expression Editor, use a CASE statement to define your matching rule. For example, if you want 1 for percentages over 50% and 2 for everything else:

  CASE WHEN "Percent" > 50 THEN 1 ELSE 2 END
  

  Adjust the condition to fit your needs—like WHEN "Percent" BETWEEN 0 AND 30 THEN 2 WHEN "Percent" > 30 THEN 1 END if you’re splitting into two specific ranges.
• Save the calculated column, then drag it into your report layout. It’ll automatically align with your existing Percent column rows.

Quick Fix for String-Based Percentages #

If your Percent column is stored as a string (e.g., with a "%" symbol), convert it to a number first in the expression:

CASE WHEN TO_NUMBER(REPLACE("Percent", '%', '')) > 50 THEN 1 ELSE 2 END

Option 2: Pre-Compute the Column in Oracle 12c (Data Source Level) #

If you want this logic to be reusable across multiple reports, build it into your Oracle backend:

• Create a view that includes the calculated flag alongside your existing columns:

  CREATE OR REPLACE VIEW customer_revenue_summary AS
  SELECT
      customer_name,
      annual_revenue,
      percent_of_total AS "Percent",
      -- Adjust the CASE condition to match your exact rule
      CASE WHEN percent_of_total > 50 THEN 1 ELSE 2 END AS status_flag
  FROM your_customer_revenue_table;
  

• Refresh your OBIEE Repository (RPD) to import this new view, then add the status_flag column to your subject area. Now you can use it in any report without re-writing the logic.

Either approach will give you a custom column that perfectly maps to your Percent column values. Feel free to adjust the CASE conditions to fit your exact business rules!

内容的提问来源于stack exchange，提问作者Wozalex