OBIEE - Choose Function

1 - About

This function takes an arbitrary number of parameters and returns the first item in the list that the user has permission to see.

The Oracle BI Administrator must model the column permissions in the Administration Tool to enable this behavior. For a alternate method, refer to OBIEE - IndexCol Function - Handle hierarchy Level security.

3 - Syntax

CHOOSE (expression1, expression2, ..., expressionN)

4 - Example

For example, a single query can be written to return security-based revenue numbers for the entire organization. The function could look like the following:

choose(L1-Revenue, L2-Revenue, L3-Revenue, L4-Revenue)

If the user issuing this function has access to the column L1-Revenue, then that column value would be returned. If the user does not have visibility to the column L1-Revenue but does have visibility to L2-Revenue, then L2-Revenue is returned.

5 - Support

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27005]
 Unresolved column: "Presentation Table"."Column". (HY000) 

Set in the file OBIEE - NQSConfig.ini the parameter PROJECT_INACCESSIBLE_COLUMN_AS_NULL to yes.

6 - Documentation / Reference

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap