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.

Content

Signature

Returns

Example