How to pick the values of an Another Row when you have two Row Header objects in the Crosstab?

Picking the Values of an Another Row and displaying it in the current row is possible with the help of ‘Where‘ condition and you can assign it to particular row only by using ‘if‘ Statement. This can be achieved when you have Single object in the Row Header section of Crosstab.

For Example:

=If([Hierarchy_obj]=”L”) Then([Var_Revenue] Where([Hierarchy_obj]=”Z”)) Else([Var_Revenue])

Here,

[Hierarchy_obj] is Hierarchy object(Bex Structure) used as Row Header in Crosstab.

[Var_Revenue] is Variable which has Measure [GlobalRevenue].

If you try the same approach(same formula) in Crosstabs that has two objects in Row Header section, it won’t pick the values.

In this Case, you need to use ‘And‘ operator in both the ‘if‘ and ‘Where‘ Condition to pick the values.

For Example:

=If([Hierarchy_obj]=”L” And [Key]=”LEGACY”) Then([Var_Revenue] Where([Hierarchy_obj]=”Z” And [Key]=”ASSETS”)) Else([Var_Revenue])

Here,

[Key] is Detail Object used as Second object(Vertical Axis) in Row Header Section in the Crosstab.

Thanks,

Parthiban 😀