Built-in Functions → descendantOf
descendantOf() is a function that uses a column of a hierarchical table as the first parameter and returns true for each row where the first parameter is a descendant of the second one, down to the specified depth level, otherwise, it returns false.
A hierarchical table with a self-join has two extra columns, LEVEL and IS_LEAF, in a physical schema.
LEVEL is an integer representing an item’s level of the hierarchy (0-based indexed), and IS_LEAF is a boolean indicating true if the item is at the bottom of a particular branch.
descendantOf(field, value, depth)
The following table illustrates the descendantOf function parameters:
|field||A column of a hierarchical table (e.g. Employee Name)|
|value||Can be a value (e.g. Joe), or a variable (e.g. $employee)|
|depth||An integer to specify the depth level within a hierarchical table|
When creating a variable for the second parameter to compare against the records of the first one, you can give it a default value, which users can change on the Insight level,
descendantOf(field, $sessionVariable, depth).
For example, you can create an internal variable,
query(3), name it Emp, and then use it in the descendantOf function as follows:
descendantOf(Employee_ID, $Emp, 2).
To learn more, review Concepts → Internal Session Variable.
Identify the employees that are descendants of John down to depth level 2.
descendantOf( HR.EMPLOYEES.FIRST_NAME, "John", 2 )
The following table illustrates the behavior of the descendantOf() function:
This example returns true for each record (in the “FIRST_NAME” column) down to depth level 2 that is a descendant of “John”. John is the manager of Paul, Paul is the manager of Mary, and Mary is the manager of Dan. Thus, the function will return true for Paul and Mary only, and false for Dan, since Dan is at depth level 3.
Use the following steps for detailed instructions on how to use the descendantOf() function:
In the Cluster Management Console (CMC), create a tenant that includes Sample Data. The Sample Data includes the HR schema.
- In the Navigation bar, select the Content tab, and then select + New → Add Dashboard.
- In the Add Dashboard dialog, for Name, enter Product Dashboard, and then select Add.
- In the Action bar, select + (add icon), or select + Add Insight.
- In the Insight panel, select Listing Table or V.
- In Tables, select Aggregated Table.
- In the Data panel, select Manage Dataset.
- In the Manage Data Sets panel, in Tables, select HR. Close the panel.
From the Data panel, drag and drop the following columns from the EMPLOYEES table to the respective tray:
- Employee First Name and Employee Id to the Grouping Dimension tray.
Employee Salary to the Measure tray.
- In the Properties panel, for Number Format, select Dollar Rounded.
From the Data panel, drag and drop Add Formula to the Grouping Dimension tray.
The Formula Builder automatically opens:
- In Search Functions, select the down arrow, and then select Miscellaneous Functions.
- Double-click the second descendantOf function,
descendantOf(field, value, depth), to add the formula to the editor.
In the Formula Editor,
fieldwith Employee First Name from the Data panel
- Select Validate & Save.