OBIEE Hierarchy Navigation Functions {HNF} Part 7

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