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.
- 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 - 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 - 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