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, otherwise, it returns false.

Note

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.

Note

To return all descendants down to a specified depth level within a hierarchical table, use this descendantOf function.

Signature

descendantOf(field, value)

The following table illustrates the descendantOf function parameters:

Parameter Description
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)
Note

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).
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).
To learn more, review Concepts → Internal Session Variable

Returns

boolean

Example

Identify the employee that is a descendant of John.

descendantOf(
    HR.EMPLOYEES.FIRST_NAME,
   	"John"
)

The following table illustrates the behavior of the descendantOf() function:

FIRST_NAME Manager LEVEL IS_LEAF
John NULL 0 False
Paul John 1 True
Mary Paul 2 False
Dan Mary 3 False

This example returns true for each record (in the “FIRST_NAME” column) that is a descendant of “John”, i.e. whose manager is “John”.


Use the following steps for detailed instructions on how to use the descendantOf() function:

Note

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 first descendantOf function, descendantOf(field, value), to add the formula to the editor.
    • In the Formula Editor,

      • Replace field with Employee First Name from the Data panel
      • Replace value with “John”
    • Select Validate & Save.