In Part 1 we did ISPARENT, in part 2 ISANCHESTOR, in part 3 ISCHILD, in part 4 ISDESCENDANT, in part 5 ISLEAF, in part 6 ISROOT. This will be last in the series for the time being

ISBROTHER

From the documentation:

….. There is no documentation….Why? It’s my own solution . This is a trick to show data of people who have the same parent, starting from the child.

Let’s start by determining who is our daddy:

SELECT
     "Y - Hierarchy levels"."Sales Person"."E0  Sales Rep Number" s_1
FROM "Y - Hierarchy levels"
WHERE
(ISPARENT("SALES PERSON"."H5 Sales Rep", VALUEOF( NQ_SESSION.HierarchyUser)))

We don’t need the report, just the SQL from the advanced tab.

Next determine who our brothers and sisters are:

SELECT       

"Y - Hierarchy levels"."Sales Person"."E0  Sales Rep Number" s_1 FROM "Y - Hierarchy levels"

WHERE ("Sales Person"."E8  Manager Number"

IN (SELECT saw_0 FROM (SELECT "Sales Person"."E0  Sales Rep Number" saw_0 FROM "Y - Hierarchy levels" WHERE ISPARENT("SALES PERSON"."H5 Sales Rep", VALUEOF(NQ_SESSION.HierarchyUser))) nqw_1 ))

 

We can use this list as the base for an in statement:

case WHEN "Sales Person"."E0  Sales Rep Number" in

(SELECT        "Y - Hierarchy levels"."Sales Person"."E0  Sales Rep Number" s_1 FROM "Y - Hierarchy levels"

   WHERE ("Sales Person"."E8  Manager Number" IN

      (SELECT saw_0 FROM

         (SELECT "Sales Person"."E0  Sales Rep Number" saw_0

          FROM "Y - Hierarchy levels"

          WHERE ISPARENT("SALES PERSON"."H5 Sales Rep",

           VALUEOF(NQ_SESSION.HierarchyUser))) nqw_1 ))) THEN "Base Facts"."1- Revenue" else 0.0 end

Till Next Time

In Part 1 we did ISPARENT, in part 2 ISANCHESTOR, in part 3 ISCHILD, in part 4 ISDESCENDANT, in part 5 ISLEAF.

ISROOT

From the documentation:

A presentation hierarchy member is defined as a root member if it has no ancestors above it in a parent-child presentation hierarchy.

Presentation Layer Syntax

ISROOT(pc_presentation_hierarchy)

Example “Hardcoded”:

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

Business Model and Mapping Layer Syntax

ISROOT(logical_dimension)

Example “Hardcoded”:

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

Till Next Time

In Part 1 we did ISPARENT, in part 2 ISANCHESTOR, in part 3 ISCHILD, in part 4 ISDESCENDANT.

ISLEAF

From the documentation:

The ISLEAF function applies to both level-based and parent-child hierarchies. For both types of hierarchy, a leaf member is defined as a member that has no child members.

Presentation Layer Syntax

ISLEAF(presentation_hierarchy)

Example “Hardcoded”:

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

Business Model and Mapping Layer Syntax

ISLEAF(logical_dimension) Example “Hardcoded”:

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

Till Next Time

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

Till Next Time

In Part 1 we did ISPARENT, in part 2 ISANCHESTOR.

ISCHILD

From the documentation:

The ISCHILD function enables you to find the children of a member of a parent-child hierarchy, that is, all the members that are one hierarchical level below the specified member.

Presentation Layer Syntax:

ISCHILD(pc_presentation_hierarchy, member_identifier)

Example “hardcoded”:

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

Example “Session Variable”:

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

Business Model and Mapping Layer Syntax:

ISCHILD(logical_dimension, member_identifier)

Example “hardcoded” :

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

Example “SessionVariable”

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

Till Next Time