IndexCol Function

In Oracle Analytics, Case statements are often used when a calculation "branches" based on a variable value. When a variable is referenced in a Case statement, it's preferable to use the IndexCol function instead to improve the efficiency of the generated SQL code.This topic describes the IndexCol function and when to use it.

IndexCol Function

You use the IndexCol function when the columns or values in a calculation vary depending on the value of a session, repository, or presentation variable.

The syntax of the IndexCol function is

INDEXCOL(<<integer_literal>>, <<expr_list>>)

Where the first argument resolves to an integer and the items that comprise the <<expr_list>> correspond to the number of possible values of the first argument. One of these items is then used in the SQL statement based on the value of the first argument.

For example, if the <<integer_literal>> argument has three possible values, then there must be three arguments in the <<expr_list>> argument, one for each possible value of <<integer_literal>>.

The first argument is often based on the value of a session variable or a Case statement in reference to variables. You can model the IndexCol function in the repository (.rpd) file or directly in a report column. You can nest multiple IndexCol functions to form a single statement.

Benefits of the Function

A calculation using a <<case when>> statement is pushed to the physical SQL code in its entirety. By comparison, the IndexCol function pushes down only the required column or expression to the database. This is because the IndexCol function is evaluated before the physical SQL code is generated.

When combined with variable prompts, which allow selection in a list of values, you can significantly modify the report structure without any increased cost on performance.

One drawback with the IndexCol function is that you can't use it with "like" in integer calculations, although you can use "like" in the list of expressions. If an integer calculation requires a "like," you must use a Case statement instead.

Example:

Assume there's a session variable called PREFERRED_CURRENCY that sets the preferred currency for a user, then based upon the value of the session variable, Revenue is displayed in the currency specified by the user.

Two calculations have been created to return the correct currency based upon the value of the session variable.

The first uses a Case statement, as in this example:

CASE

WHEN VALUEOF(NQ_SESSION."PREFERRED_CURRENCY") = 'USD' THEN "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Usd" 

WHEN VALUEOF(NQ_SESSION."PREFERRED_CURRENCY") = 'EUR' THEN  "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Eur" 

WHEN VALUEOF(NQ_SESSION."PREFERRED_CURRENCY") = 'AUD' THEN  "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Aud" 

ELSE NULL

END

The second uses the IndexCol function as in this example.

INDEXCOL(

CASE VALUEOF(NQ_SESSION."PREFERRED_CURRENCY")

WHEN 'USD' THEN 0

WHEN 'EUR' THEN 1

WHEN 'AUD' THEN 2

END ,

"01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Usd", "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Eur", "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Aud")

Because the first argument of the IndexCol function must resolve to an integer, a Case statement is used for the resolution.

When a query is run using the Case statement calculation, the entirety of the Case statement is pushed down to the database, because the Case statement is evaluated at runtime. In some cases, this causes issues with the optimizer.

WITH

SAWITH0 AS (select sum(case  when 'USD' = 'USD' then T42437.Revenue_Usd when 'EUR' = 'USD' then T42437.Revenue_Eur when 'AUD' = 'USD' then T42437.Revenue_Aud else NULL end ) as c1,

     T42412.Office_Dsc as c2,

     T42412.Office_Key as c3

from

     BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ ,

     BISAMPLE.SAMP_REVENUE_CURR_F T42437 /* F19 Rev. (Converted) */

where  ( T42412.Office_Key = T42437.Office_Key )

group by T42412.Office_Dsc, T42412.Office_Key),

SAWITH1 AS (select 0 as c1,

     D1.c2 as c2,

     D1.c1 as c3,

     D1.c3 as c4

from

     SAWITH0 D1)

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,

     D1.c2 as c2,

     D1.c3 as c3

from

     SAWITH1 D1

order by c2 ) D1

The same query run using the IndexCol function pushes down only the expression needed to satisfy the query, because the IndexCol function is resolved prior to SQL generation.  This helps avoid issues with the Optimizer.

WITH

SAWITH0 AS (select sum(T42437.Revenue_Usd) as c1,

     T42412.Office_Dsc as c2,

     T42412.Office_Key as c3

from

     BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ ,

     BISAMPLE.SAMP_REVENUE_CURR_F T42437 /* F19 Rev. (Converted) */

where  ( T42412.Office_Key = T42437.Office_Key )

group by T42412.Office_Dsc, T42412.Office_Key),

SAWITH1 AS (select 0 as c1,

     D1.c2 as c2,

     D1.c1 as c3,

     D1.c3 as c4

from

     SAWITH0 D1)

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,

     D1.c2 as c2,

     D1.c3 as c3

from

     SAWITH1 D1

order by c2 ) D1

Workbooks and INDEXCOL

With the advent of parameters, the IndexCol function can now be used in workbooks.

In this example, the IndexCol function is used to change the period granularity in a visualization.

1. Create a parameter to be used as the column selector to select the period grain, in this case, either 'Month' or 'Quarter'.

   
   Description of the illustration ceal_time_selector_value_param.jpg

2. Next, create a custom calculation to perform the IndexCol function. Here the calculation is:

   indexcol(case when @parameter("Time Selector Value")('Month')='Month' then 0 else 1 end, "HCM - Workforce Core"."Time"."Month Name", "HCM - Workforce Core"."Time"."Quarter")

   
   Description of the illustration ceal_time_selector_indexcol_calc.jpg

3. Put the parameter in the filter bar of a workbook. Users can change the granularity of a report by selecting either 'Month' or 'Quarter' from the column selector filter.

   
   Description of the illustration ceal_time_selector_value_workbook_filter_bar.jpg