Hierarchies require multiple configuration objects, including:
- Entity table—The table of type
Mainthat holds the values that you are trying to relate to each other, such as people, accounts, or items. - Relationship table—The table of type
Mainthat creates the connections between records that are stored in the entity table. - Hierarchy table—The table of type
Mainwhich has table lookup fields that point to the parent and child entity, as well as additional fields that you reference in the hierarchy definition. This table is referenced in a join table to link it to your interactive report. - Join table—The table of type
Jointhat connects the table the interactive report is built over with the hierarchy table you configure. - Hierarchy definition—The definition that links the relationship table, interactive report table, and hierarchy. This enables users to select the Apply a hierarchy definition
button in an interactive report to display records in the hierarchy you configure.
Prerequisites
You must have a a relationship table built over the entity table which you want the hierarchy configured on. Learn more in Relationship application structure.
Table Definitions - Hierarchy
In the Table Definitions application, create your hierarchy table. You must:
- Build a table of type
Main. - Reference the same Child Field and Parent Field as the relationship table.
- Include the following data items:
ParentHierarchyDepthChildHierarchyDepthHasChildren- A table lookup data item which points to the parent entity.
- A table lookup data item which points to the child entity.
Table Definitions - Join
In the Table Definitions application, create your join table. You must:
- Build a table of type
Join. - In the Join Type field, select Left Outer Join.
- In the Join Type subtable, configure your join with the following fields:
- In the Primary Join Table field, select the hierarchy table you configured.
- In the Primary Join Field field, select the table lookup field from your hierarchy table which uniquely identifies the child records (nwId).
- In the Secondary Table field, select the table your interactive report is built over.
- In the Secondary Join Field field, select the table lookup field from your interactive report table which you are applying the hierarchy to.
Hierarchy Definitions
In the Hierarchy Definitions application, you must:
- Create a new record.
- On the General page, in the Relationship Table field, enter the name of your relationship table.
- In the Relationship Mappings subtable, map the table and fields from the parent and child records you want included in the hierarchy.
- On the Flat Table Configuration page, in the Hierarchy Table field, enter the name of your hierarchy table.
- Optionally, in the Entity Related Fields subtable, map related fields from the entity table to your hierarchy table. Entities in the hierarchy table are nwIds that point to the entity table. Related fields are other fields from the entity table that must also be included in the hierarchy table.
- On the Join Table Mappings page, in the Interactive Report Mappings subtable, enter the name of your interactive report data source and the name of the join table you created. For each table you want to apply this hierarchy definition to, you must include an entry in the subtable.
Hierarchy Definitions application
Use the Hierarchy Definitions application to define hierarchies that can be used in interactive reports. Hierarchies display records in a hierarchal format within a single column of the report based on existing relationships between records.
For example, you may want to include a hierarchy in your report to view aggregate data from different levels within a company structure. Learn more in How to use hierarchies in interactive reports.
Before you configure a hierarchy definition, you must configure a hierarchy table and join table. Learn more in Configure hierarchies for interactive reports.
General page
Select the relationship table which the hierarchy is built over, and map the parent and child relationships from the table.
Flat Table Configuration page
Select the hierarchy table which links the relationship table to this definition. Map the fields from the hierarchy table which identify the parent and child records, as well as any related fields from the interactive report table that you want included.
Join Table Mappings page
Select the interactive report tables which you want this hierarchy available in.