One of the interesting features of BI EE is its ability to bring up different Business Model columns for a single Presentation Layer column based on different user logins. For example, say if you are creating the following report (all columns point to presentation layer columns),
GEOGRAPHY SALES
you can specify which column in the BM/Physical layer should be used for the query in the place of GEOGRAPHY Presentation column (CEO’s can see region level data. Manager’s can see country level data etc), based on a user login. This can be done by using a simple function called as IndexCol. Before moving on to the syntax of this function lets try to understand what we are trying to do in the example below. Our aim is to create a report like the one below.
Now, whenever the user Region logs in, he must get a report containing the AMOUNT for all the regions (GEOGRAPHY_LEVEL will get the data from the column REGION_NAME) like the one below.
And, whenever the user Country logs in, for the same report he must get the data containing the AMOUNT for all the countries( GEOGRAPHY_LEVEL will get the data from the column COUNTRY_NAME ) like the one below.
Now lets look at the IndexCol function which forms the basis of what we are trying to do. Syntax of this function is
SYNTAX: IndexCol( integer literal, expr1, expr2, … )
Where
Integer Literal can take integral values like 0,1,2 etc and
expr can have different BM Columns.
Integer Literal can take integral values like 0,1,2 etc and
expr can have different BM Columns.
What this function basically does is, it dynamically chooses different columns based on the Integer Literal value. Lets consider the example below
INDEXCOL( VALUEOF(NQ_SESSION.”LEVEL”), BISE1_TUTORIALWH.GEOGRAPHY.REGION_NAME, BISE1_TUTORIALWH.GEOGRAPHY.COUNTRY_NAME)
In the above function, the INDEXCOL function looks for the LEVEL session variable. If the value of this variable is 0, it will choose the REGION_NAME column. If the value is 1 then it will choose the COUNTRY_NAME column. So, to implement the above example, we shall create a simple table in the database which would have 2 columns as shown below
And lets populate it with the username values and the corresponding integer literal values. So, our idea is to populate the LEVEL variable from a init block. So, the user Region, when he logs in will set LEVEL to 0 and hence the REGION_NAME column will be used in the report. Similarly for user Country, when he logs, the variable will be set to 1 and hence the COUNTRY_NAME column will be chosen.
The LevelInit init block will set the LEVEL variable as shown below.
Once this is done, your report should reflect the 2 columns based on the user who has logged in.
As user Region
As user Country
The above is not the best of examples. But this should atleast give an idea of what IndexCol can do.
Posts
