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:

ParameterDescription
fieldA column of a hierarchical table (e.g. Employee Name)
valueCan 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](/5.1/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_NAMEManagerLEVELIS_LEAF
JohnNULL0False
PaulJohn1True
MaryPaul2False
DanMary3False

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.