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](/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_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.

Content

Signature

Returns

Example