OBIEE’s CHOOSE function is one of the handiest tools to cut back on the overall number of reports needed. Yet for all its handiness it’s not very well known or used despite the fact that there are abundant examples in the vanilla product. Here is not only how to use it, but some tips and tricks on using it effectively.
What is the CHOOSE function?
In essence it takes in a set of logical columns and will show the first column that a user has access to. The syntax is:
CHOOSE(<logical column 1>,<logical column 2>, <logical column 3>,<logical column 4>)
Neat. . . So what?
While that doesn’t look all useful the applications certainly can be. For example, let’s say that you have a distribution group, an external group, and an internal group. Distributors only get to see a certain revenue number, external users another and internal users yet another. Many would set up three reports for each group. However using the CHOOSE function you can do all three in one report by denying column level access to the appropriate groups and using the CHOOSE function to show only the column the user has access to.
Avoiding Errors using the PROJECT_INACCESSIBLE_COLUMN_AS_NULL
If you use a CHOOSE and a user doesn’t have access to any of the columns in the CHOOSE OBIEE will, by default, show an ugly error message saying that the user doesn’t have access to the column. Luckily, this is easily fixed by changing the NQSConfig.ini (located in the OracleBI\Server\Config folder).
In the NQSConfig.Ini set the PROJECT_INACCESSIBLE_COLUMN_AS_NULL to YES. Instead of throwing an error the system will simply not show the column on the report.
NOTE: Setting PROJECT_INACCESSIBLE_COLUMN_AS_NULL to YES will incur a performance hit. It should be a minimal, but it is a hit nonetheless.
An example: Using CHOOSE with Organizational levels
A great example of using CHOOSE to the fullest is the idea of “employee level” and “organizational level”. Let’s say for example your company has five levels of hierarchy: Worldwide, Continent, Country, Province/State, and City levels. A great way to setup reports would be to use the CHOOSE to ensure everyone sees the same reports according to their level.
To facilitate this, first set up a dimensional hierarchy for each of organizational element. Set up five different security groups and after assigning their data security go to the each of the logical columns in the hierarchy and setup the security accordingly. So for example, the Continent logical column security should be like this:
– Worldwide group = Yes
– Continent Group = Yes
– Country Group = No
– Province/State Group = No
– City Rep Group = No
Then create a logical column that uses the CHOOSE function with the following syntax:
CHOOSE(Worldwide, Continent, Country, Province/State, City)
Then use this new logical column on reports instead of creating a report for each different level.
