In Oracle Fusion OTBI (Oracle Transactional Business Intelligence), Dynamic Columns are not a native term or feature in the way we might refer to them in programming (like dynamic pivoting). However, there are ways to simulate or implement dynamic-like behavior in OTBI through workarounds and design patterns.
Here’s what’s commonly referred to as dynamic columns in OTBI, and how you can achieve similar flexibility:
🔹 What Are "Dynamic Columns" in OTBI?
In OTBI, dynamic columns refer to the ability to change the set of columns displayed in a report based on a user’s selection or context. This allows for a more flexible report layout where users don't always see all possible columns, but only the relevant ones.
🔸 Techniques to Simulate Dynamic Columns
1. View Selectors
-
Use Case: Show different sets of columns based on user choice.
-
How it works: Create multiple views (like different tables or pivot tables) each with different columns, and use a View Selector to allow the user to choose which view to display.
-
Example: A user selects "Summary View" or "Detail View" from a dropdown.
2. Column Selectors
-
Use Case: Let users change which column is shown (e.g., Revenue vs. Margin).
-
How it works: Add a Column Selector that allows users to toggle between different columns in a single column position.
-
Example: A column that switches between "Invoice Amount", "Tax Amount", and "Total Amount".
3. Filters and Prompts
-
Use Case: Show/hide data based on business units, roles, or categories.
-
How it helps: Use dashboard prompts or inline filters to restrict which data (and effectively which columns) are relevant.
-
Limitation: It doesn’t truly hide columns, but it can filter the data scope.
4. Conditional Formatting (with Hide Columns)
-
You can sometimes use conditional formatting to hide values (e.g., show null or blank) in a column when a condition is met — effectively hiding the column for some users or criteria.
5. BI Publisher as Alternative
-
If OTBI doesn’t provide the level of dynamic layout you need, BI Publisher (BIP) reports can be designed with XSL templates or RTF templates with conditional logic to dynamically show or hide sections or columns.
🔹 Limitations
-
OTBI doesn’t support truly dynamic SQL or dynamic metadata (like dynamic pivot column names).
-
Complex dynamic behavior often requires RPD customization (in OBIEE layer) or using BI Publisher.
✅ Use Cases of Dynamic Columns in OTBI
| Use Case | Technique |
|---|---|
| Show different KPIs by department | View Selector |
| Toggle between Time Period columns (e.g., Month vs Quarter) | Column Selector |
| User-specific views (e.g., Finance vs Sales) | Role-based Dashboards |
| Hide/show sensitive data (like salary) | Conditional Formatting or Data Security |
