Monday, September 12, 2011

OBIEE Hierarchy Navigation Functions {HNF} Part 4

In Part 1 we did ISPARENT, in part 2 ISANCHESTOR, in part 3 ISCHILD. Now it’s time for:

ISDESCENDANT

From the documentation:

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

Presentation Layer Syntax

ISDESCENDANT(pc_presentation_hierarchy, member_identifier [, distance])


Example “Hardcoded” distance = 1:

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


Example “Hardcoded” distance = 2:


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


Example “SessionVariable” distance = 2:


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

Note: If you leave the distance option out you will get all the children.

Business Model and Mapping Layer Syntax


ISDESCENDANT(logical_dimension, member_identifier [, distance])


Example “Hardcoded” distance = 1:


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


Example “Hardcoded” distance = 2:


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


Example “SessionVariable”:


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


image

image

Till Next Time

1 comment:

Anonymous said...

Have you tried IsDescendant or IsChild functions with OLAP dimension hierarchy as a phisical data source?