IndexCol

One of the features I am using in my current project is IndexCol, which is a really powerful feature that can save lots of metadata work, and provide extraordinary flexibility to the content you provide users.

The syntax for IndexCol is simply:

IndexCol(<Constant>, <Expr0>, <Expr1>, <Expr2> …).

The constant above can be a session variable – and usually will be. And that is where the power of this construct comes from.

Functionally, IndexCol just picks the nth Column based on the value of the constant – and so is equivalent to a case statement:

case <constant>

when 0  then Expr0

when 1 then Expr1

when 2 then Expr2

end

What makes this different from the case statement though, is that the BI server will evaluate the case at the time it receives the query – not when it executes it. This allows for significant gains in the actual queries that are executed. In the next few posts, I will try and cover the various cases in which this may be useful.

Employee Level Hierarchy

It is a common requirement to start out a report at the level of the user. For example, consider the hierarchy below:

CEO

COO

         VP1

         VP2

CFO

          VP3

CMO

           VP4

 

which is stored as:

 

Manager Level 0	Manager Level 1	Employee	Level of Employee
CEO	CEO	CEO	0
CEO	COO	COO	1
CEO	COO	VP1	2
CEO	COO	VP2	2
CEO	CFO	CFO	1
CEO	CFO	VP3	2
CEO	CMO	CMO	1
CEO	CMO	VP4	0

 

In the above table, when the CEO logs to on to see the revenue for all his reports – he wants to see the report:

Select ManagerLevel0, Sales from SubjectArea

However, when the COO logs on to see the same report – He would like to start out at his level – namely:

Select ManagerLevel1, Sales from SubjectArea

This is accomplished rather simply using IndexCol. One needs to create a derived column which represents the level of the user.

The report can then be written as:

Select MyLevel, Sales from SubjectArea

This will then automatically switch to the correct semantics depending on who logs on.

Two things that are noteworthy:

1. Drills work correctly. When the CEO is logged on, and he drills from his MyLevel, he will be taken to Level1….
2. Aggregate Navigation works correctly. i.e., if you have an aggregate at ManagerLevel1, this report will hit the aggregate where possible.

Both of the above would not have been possible if one uses the case statement equivalent.