Compute Non-Agg Before/After OLAP Functions (For Example, Rank) Calculated in Analytical Engine

Compute Non-Agg Before/After OLAP Functions/Rank is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

When reports contain calculations based on non-aggregation metrics, this property controls the order in which the non-aggregation and calculations are computed.

Levels at Which You Can Set This

Database instance, report, and template

Examples

Calculate Non-Aggregation Before Analytical (default)

Copy

select a12.YEAR_ID YEAR_ID,
 sum(a11.TOT_SLS_QTY) WJXBFS1
from HARI_REGION_DIVISION a11
 join HARI_LOOKUP_DAY a12
 on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
where a11.CUR_TRN_DT)
 in (select min(a11.CUR_TRN_DT)
 from HARI_LOOKUP_DAY a11
 group by a11.YEAR_ID))
group by a12.YEAR_ID
create table #ZZTIS00H5J7MQ000(
 YEAR_ID DECIMAL(10, 0))
[Placeholder for an analytical SQL]
select a12.YEAR_ID YEAR_ID,
 max(a13.YEAR_DESC) YEAR_DESC,
 sum(a11.TOT_SLS_QTY) TSQDIMYEARNA
from HARI_REGION_DIVISION a11
 join HARI_LOOKUP_DAY a12
 on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
 join #ZZTIS00H5J7MQ000 pa1
 on (a12.YEAR_ID = pa1.YEAR_ID)
 join HARI_LOOKUP_YEAR a13
 on (a12.YEAR_ID = a13.YEAR_ID)
where ((a11.CUR_TRN_DT)
 in (select min(a15.CUR_TRN_DT)
 from #ZZTIS00H5J7MQ000 pa1
 join HARI_LOOKUP_DAY a15
 on (pa1.YEAR_ID = a15.YEAR_ID)
 group by pa1.YEAR_ID))
group by a12.YEAR_ID

Calculate Non-Aggregation After Analytical

Copy

select a11.CUR_TRN_DT CUR_TRN_DT,
 a12.YEAR_ID YEAR_ID,
 sum(a11.TOT_SLS_QTY) WJXBFS1
from HARI_REGION_DIVISION a11
 join HARI_LOOKUP_DAY a12
 on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
group by a11.CUR_TRN_DT,
 a12.YEAR_ID
create table #ZZTIS00H5J8NB000(
 CUR_TRN_DT DATETIME, 
 YEAR_ID DECIMAL(10, 0), 
 WJXBFS1 FLOAT)
[Placeholder for an analytical SQL]
insert into #ZZTIS00H5J8NB000 values (CONVERT
(datetime, '1993-12-01 00:00:00', 120), 1993,
44)

[The rest of the INSERT statements have been omitted from display].

Copy

select distinct pa1.YEAR_ID YEAR_ID,
 pa1.WJXBFS1 WJXBFS1
from #ZZTIS00H5J8NB000 pa1
where ((pa1.CUR_TRN_DT)
 in (select min(c11.CUR_TRN_DT)
 from HARI_LOOKUP_DAY c11
 group by c11.YEAR_ID))
create table #ZZTIS00H5J8MQ001(
 YEAR_ID DECIMAL(10, 0), 
 WJXBFS1 FLOAT)
[Placeholder for an analytical SQL]
select a12.YEAR_ID YEAR_ID,
 max(a13.YEAR_DESC) YEAR_DESC,
 sum(a11.TOT_SLS_QTY) TSQDIMYEARNA
from HARI_REGION_DIVISION a11
 join HARI_LOOKUP_DAY a12
 on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
 join #ZZTIS00H5J8MQ001 pa2
 on (a12.YEAR_ID = pa2.YEAR_ID)
 join HARI_LOOKUP_YEAR a13
 on (a12.YEAR_ID = a13.YEAR_ID)
where ((a11.CUR_TRN_DT)
 in (select min(a15.CUR_TRN_DT)
 from #ZZTIS00H5J8MQ001 pa2
 join HARI_LOOKUP_DAY a15
 on (pa2.YEAR_ID = a15.YEAR_ID)
 group by pa2.YEAR_ID))
group by a12.YEAR_ID