Inzicht in uw bedrijfsgegevens en processen
Contact   |   Vacatures   |   Inloggen
#1 in inzichten en expertise
menu_header





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

Datum: 04-10-2011 Auteur: John Minkjan
verplicht

verplicht

 
 BI Technologie
 Interactive Dashboards
 Answers
 Publishing
 Delivers
 Office integratie
 Server
 BI Applicaties
 Financial Analytics
 Project Analytics
 Human Resource Analytics
 Supply Chain Analytics
 Customer Sales Analytics
 Procurement and Spend Analytics
 Manufacturing Analytics
 BI Projecten
 Project management
 Datawarehousing
 Advisering / BICC
 Consultancy
 Over ons
 Contact
 Netwerkorganisatie
 Portfolio
 Laatste nieuws
 Inzetbare consultants
 Vacatures
 Blog
 FacturatieOffice.nl facturatie software
 Gratis urenregistratie
 Een factuur maken
 Een creditnota maken
 Een offerte maken
 Een pakbon maken
 Een betalingsherinnering maken
 Een factuur voorbeeld

Volg Insight Experts via jouw Facebook page


Deel deze blog met jouw sociale netwerken

Sitemap voor deze pagina

BI