Choose and Indexcol functions in OBIEE

You can use the choose statement to dynamically choose different columns in a request  depending on the group a user belongs to:

Let us look at  the following request:

Image-0001

Based on the USER logging in we need to choose different columns from the EMPLOYEE_HIER table.

We can do this using the CHOOSE function.

The first step is to create groups in the RPD  and assign USERS to their respective groups.

Image-0003

Next in the Presentation layer of the RPD select  on of the columns on which you need to apply the CHOOSE function.

Image-0005 Image-0004

Click on the Permissions button and give rRead permission to the group whose memebers have Read access to that column.

In the aboive example I have chosen the REGION_MANAGER_NAME column and assigned tread permission for this column to REGIONAL MGR group.

In answers, edit the formula of the column on which you want to apply the CHOOSE function.

Image-0006

Now when a USER logs into the application, the appropriate column is chosen from the EMPLOYEE_HIER table.

 

IndexCol Function

We can get a similar functionality using  the Indexcol function. The syntax for this function is:

IndexCol( integer value , column1, column2, … )

Where
integervalue can take integral values like 0,1,2 etc and
column1,column2 etc can be any column in the Business Layer

The column is chosen depending on the integer value that gets assigned when a user logs in.  If integer value is 0, then column1 is chose, if it is1 then column2 is chosen and so on.

So in order to implement this function let I create a database table that will contain all the user login ID and a LEVEL column that will will have the integer value associated with that user. Now using  an initialization block and an associated session variable, I can get the integer value associated with the user.

username              level

EMP1                        0

EMP2                        0

MGR1                       1

AM1                           2

RM1                          3

Image-0007Image-0008

I will use the LEVEL session variable to assign the integer value to the indexcol function.

Now I will choose the column in the BM layer on which I want set the indexcol function, and use the following expression in the expression builder:

Indexcol(VALUEOF(NQ_SESSION.LEVEL),
“DATA_DUP”.”EMPLOYEE_HIER”.”LOGIN”, “DATA_DUP”.”EMPLOYEE_HIER”.”SALES_MGR_NAME”, “DATA_DUP”.”EMPLOYEE_HIER”.”AREA_MANAGER_NAME”, “DATA_DUP”.”EMPLOYEE_HIER”.”REGION_MANAGER_NAME”

 

Image-0010

Image-0011

Basically what this  does is,

when EMP1 logs in the value of session variable LEVEL is zero and the column LOGIN is chosen

When MGR1 logs in the value of LEVEL is 1 and the column SALES_MGR_NAME is chosen and so on.

Advertisements

5 Responses

  1. How do you assure that session variable LEVEL is an integer and doesn’t appear as a DOUBLE in the admin tool? Suppose it’s coming from Oracle and is defined as a Number in Oracle (which doesn’t have a true Integer data type).

  2. you could use a cast as integer in the init block

  3. Hi carpediemconsulting

    Good Artical, Thanks

    We have a Similar req at my client, but little bit different.

    we have a Dimension, in that we have a hierarchy H1 with Levels, Level1, Level 2 , Level 3 we are implimenting this product for different companies, Lets say comp A, & Comp B,
    In Comp ‘A’ we call level 1 as ‘Market’ and in comp ‘B’ we call it as “Location”
    can we change these Level1 label dynamically when the user login. FYI . these labels are stored in TABLE

    Thanks
    Vik

  4. fine

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: