Category Archives: John Minkjan
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
ISANCESTORfunction 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))
Till Next Time
OBIEE Hierarchy Navigation Functions {HNF} Part 1
- ISPARENT (Who is my Mother or Father?)
- ISANCESTOR (Who are the {great}(grant)-parent(s)?)
- ISCHILD (Who is my child?)
- ISDESCENDANT (who are the {great}(grant)-children?
- ISLEAF (are there children?)
- ISROOT (Who is the overall boss?)
- ISBROTHER (or sister) (You have to wait until the final part to see the solution)
member_identifier
The functions ISPARENT, ISANCESTER, ISCHILD and ISDENSCENDANT all depend on the member_identifier. This is the column Member Key you identify in your logical table source. 
ISPARENT
From the documentation:
The ISPARENT function enables you to find the parents of a member of a parent-child hierarchy, that is, all the members that are one hierarchical level above the specified member.
Presentation Layer Syntax:
ISPARENT(pc_presentation_hierarchy, member_identifier)
Example “Hardcoded Member Identifier”:
Case When ISPARENT("Sales Person"."H5 Sales Rep",'21') Then 'YES' else 'NO' END Example “Session Variable”: Case When ISPARENT("Sales Person"."H5 Sales Rep",VALUEOF(NQ_SESSION.HierarchyUser)) Then 'YES' else 'NO' END
Business Model and Mapping Layer Syntax:
ISPARENT(logical_dimension, member_identifier)
Example “Hardcoded Member Identifier”:
Case When ISPARENT("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 ISPARENT("13 - Hierarchy levels"."H5 Sales Rep", VALUEOF(NQ_SESSION."HierarchyUser")))
These functions are also available in the LTS and can be used as data access restriction.
Till Next Time
OBIEE mandatory description field
A lot of report builders make META DATA a Friday afternoon project. Recently there was a question on the OTN forum how you could make this mandatory.
You can do this editing the opensavedlg.js, saw.catalog.BrowserDialogViewer.prototype.updateModel part
saw.catalog.BrowserDialogViewer.prototype.updateModel = function () {
var b = this.model;
if (this.descEdit.value.length<6)
{
alert("Enter a valid Description");
this.descEdit.value = "DESC:";
}
else
{
b.description = this.descEdit.value;
};After the edit redeploy.
Till Next Time
OBIEE Adding documentation files to the Catalog
First go to the catalog manager in browser:
expand your shared folders:
Click on new > Folder
Give it a meaningful name:
Select the folder:
Press Upload:
Select a file:
Now your documentation is accessible from the catalog:
Put it for instances as a link on your dashboard:
The path is a bit tricky to get used to:
http://<<SERVER_NAME>>: <<port>>/analytics/saw.dll?downloadFile&path=%2FShared%2FDocumentation%2Fdocumentname.ext
Now this might not be the easiest way to publish your documents, but the great advantage is that you can use the OBIEE security model to control access:
Till Next Time
OBIEE Calendar Control
Question from the OTN Forum. (Works in 10 & 11g)
Select a YearMonth, YearWeek and Date from your Calendar dimension, select a measure from your facts.
Add a extra column called “Day Of the Week”
CAST(DAYOFWEEK("Time"."T00 Calendar Date") as varchar(2)) || ' - '||DAYNAME("Time"."T00 Calendar Date")
Alter your fact column to:
'<b>'||cast("Time"."T00 Calendar Date" as varchar(10))||'</b>'||'<br><P STYLE="text-align: right;">'||CAST("Base Facts"."1- Revenue" AS varchar(15))
It concats the date with the fact and adds some HTML Formatting
Alter the column properties [data format] to HTML:
Put it all in a pivot view:
Check the results:
Till Next Time
