OBIEE Hierarchy Navigation Functions {HNF} Part 2

In Part 1 we did ISPARENT. ISPARENT is basically the ISANCHESTOR function with a distance of 1.

ISANCHESTOR

From the documentation:

The ISANCESTOR function enables you to find the ancestors of a member of a parent-child hierarchy, either all the ancestors of a member, or the ancestors at a specified hierarchical distance from the member.

Presentation Layer Syntax

ISANCESTOR(pc_presentation_hierarchy, member_identifier [, distance] )

Example “hardcoded” , no distance:

Case When ISANCESTOR("Sales Person"."H5 Sales Rep",'21' ) Then 'YES' else 'NO' END

This will show all anchestors!

Example “hardcoded” , distance = 2:

Case When ISANCESTOR("Sales Person"."H5 Sales Rep",'21',2 ) Then 'YES' else 'NO' END

Example “Session Variable” , distance =2:

Case When ISANCESTOR("Sales Person"."H5 Sales Rep",VALUEOF(NQ_SESSION.HierarchyUser),2 ) Then 'YES' else 'NO' END

Business Model and Mapping Layer Syntax

ISANCESTOR(logical_dimension, member_identifier [, distance])

Example “hardcoded”, distance =1:

Case When ISANCESTOR("13 - Hierarchy levels"."H5 Sales Rep",'22' ) Then 'YES' else 'NO' END

Example “hardcoded”, distance =2:

Case When ISANCESTOR("13 - Hierarchy levels"."H5 Sales Rep",'22' ,2) Then 'YES' else 'NO' END

Example “SessionVariable”:

FILTER("13 - Hierarchy levels"."F0 Sales Base Measures"."1- Revenue" USING  ISANCESTOR("13 - Hierarchy levels"."H5 Sales Rep",  VALUEOF(NQ_SESSION."HierarchyUser"),2))

Till Next Time