Saturday, September 10, 2011

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


image

image

Till Next Time

No comments: