# Logical Views ## Overview A **logical view** is a queryable object whose fields come from more than one underlying data object, composed inside the Semantic Model rather than stored in a single physical table. Defined as a `SemanticLogicalView`, it bundles its member data objects and how they combine, then exposes their columns as one flat set of fields. You query a logical view exactly like a plain data object: reference its columns with `table_field`, using the logical view's `apiName` as `table_name`. There are four types of logical view, distinguished by how the view composes its members: - **Standard** — joins its member data objects horizontally on matching keys, using join relationships defined inside the view. This is the default type. - **Union** — appends the rows of its member objects vertically (a `UNION ALL`), aligning their columns through mapped fields. - **Custom SQL** — exposes the output columns of a SQL statement defined in the model. - **Organizational hierarchy** — derives a transitive parent-child table from a parent-key relationship, so a query can roll values up the hierarchy and scope a result to a subtree. Whichever type a view is, you never express the composition at query time — no join clauses, no `UNION`, no raw SQL travel in the request. The composition lives in the model, and a query only names the view's columns. The single most important thing to know: a logical view is addressed by its own `apiName`, and its columns carry composite names (`_`) that keep same-named fields from different members distinct. ## Metadata in the model A logical view is defined in the Semantic Data Model as a `SemanticLogicalView` under the model's `semanticLogicalViews`. Its type is set by `semanticViewTypeEnum`, which takes one of these values: - **HardJoin** (the standard default) — carries member data objects and their join relationships. - **Union** — carries a `semanticUnions` block. - **CustomSQL** — carries the SQL in `customSQLV2`. - **Hierarchy** — derives a transitive parent-child table. A query references a view's fields by name; it never redefines the view. For the authoring shape, see [Semantic Logical View](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-logical-view-semanticlogicalview) and [Semantic CustomSql Logical View](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-customsql-logical-view-semanticlogicalview) in the Authoring API. At query time every logical view behaves like any other queryable object: select its columns through `table_field` (by physical column and table name) or reference model-defined calculated fields over it through `semantic_field`. ## Query usage ### Standard logical views A standard logical view joins its member data objects internally and exposes their columns as one flat set of fields. Each output column is named `_` — the member object's API name and the field's API name joined by an underscore — which is how two same-named fields from different member objects stay distinct. Query it exactly like a plain data object: reference a column with `table_field`, using the logical view's `apiName` as `table_name`. Model-defined calculated fields can read the view's columns through the same `.` path, and you select those calcs through `semantic_field` as usual. The example below queries a logical view named `logicalTable` that joins an Account and a Contact data object: it selects two of the view's columns and a calculated measurement whose expression reads one of them. ```json { "structured_semantic_query": { "fields": [ { "expression": { "tableField": { "name": "SemanticAccount__dlm_semantic__Id__c", "tableName": "logicalTable" } }, "alias": "name", "rowGrouping": true }, { "expression": { "tableField": { "name": "SemanticContact__dlm_semantic__FirstName__c", "tableName": "logicalTable" } }, "alias": "first_name", "rowGrouping": true }, { "expression": { "semanticField": { "name": "formula_summary_measurement_example" } }, "semanticAggregationMethod": "SEMANTIC_AGGREGATION_METHOD_SUM", "alias": "formula_summary_measurement_example" } ] }, "semantic_model": { "apiName": "Sales", "semanticCalculatedMeasurements": [ { "apiName": "formula_summary_measurement_example", "dataType": "Number", "expression": "10 * (logicalTable.SemanticAccount__dlm_semantic__Number__c)", "syntax": "Salesforce" } ], "semanticLogicalViews": [ { "apiName": "logicalTable", "label": "logical table", "semanticDataObjects": [ { "apiName": "SemanticAccount__dlm", "dataObjectName": "ssot__Account__dlm", "dataObjectType": "Dmo", "semanticDimensions": [ { "apiName": "SemanticAccount__dlm_semantic__Id__c", "dataType": "Text", "dataObjectFieldName": "ssot__Id__c" } // ... ], "semanticMeasurements": [ { "apiName": "SemanticAccount__dlm_semantic__Number__c", "dataType": "Number", "dataObjectFieldName": "ssot__Number__c" } ] }, { "apiName": "SemanticContact__dlm", "dataObjectName": "ssot__Contact__dlm", "dataObjectType": "Dmo", "semanticDimensions": [ { "apiName": "SemanticContact__dlm_semantic__FirstName__c", "dataType": "Text", "dataObjectFieldName": "ssot__FirstName__c" } // ... ] } ], "semanticRelationships": [ { "apiName": "relationship", "leftSemanticDefinitionApiName": "SemanticAccount__dlm", "rightSemanticDefinitionApiName": "SemanticContact__dlm", "joinType": "Left", "criteria": [ { "leftSemanticFieldApiName": "SemanticAccount__dlm_Semantic_KQ_Id__c", "rightSemanticFieldApiName": "SemanticContact__dlm_Semantic_KQ_Id__c" } ] } ] } ] // ... } } ``` Joins *inside* a logical view are defined by the view itself, with explicit join types, not through the model's top-level `semanticRelationships`. To connect a whole logical view to another data object, use a top-level relationship — see [Relationships & Joins](/docs/data/semantic-layer/guide/query-api-in-depth-relationships.md). ### Union logical views A union logical view appends the rows of several member data objects that share a schema — a vertical combination (`UNION ALL`), unlike a standard view's horizontal join. The members live under a `semanticUnions` block, and their columns are aligned by `semanticMappedFields`: each mapped field declares a unified column and the member field it draws from in each source object. At query time you select the unified column from the outer view; the engine emits the `UNION ALL` and returns one row per source record, with a column that a given source object does not map returning `NULL`. The example below queries an `outerLogicalView` whose `semanticUnions` block unions three member objects. It selects two unified columns, `unifiedName` and `unifiedDate`. ```json { "structured_semantic_query": { "fields": [ { "expression": { "tableField": { "name": "unifiedName", "tableName": "outerLogicalView" } } }, { "expression": { "tableField": { "name": "unifiedDate", "tableName": "outerLogicalView" } } } ], "options": { "detailed_rows": true } }, "semantic_model": { "apiName": "Sales", "semanticLogicalViews": [ { "apiName": "outerLogicalView", "label": "Outer Logical View", "semanticDataObjects": [], "semanticUnions": [ { "apiName": "innerLogicalView", "label": "inner logical view", "semanticDataObjects": [ { "apiName": "SemanticAccount__dlm", "dataObjectName": "ssot__Account__dlm", "dataObjectType": "Dmo", "semanticDimensions": [ // ... ] }, { "apiName": "SemanticContact__dlm", "dataObjectName": "ssot__Contact__dlm", "dataObjectType": "Dmo", "semanticDimensions": [ // ... ] }, { "apiName": "SemanticOrder__dlm", "dataObjectName": "ssot__Account__dlm", "dataObjectType": "Dmo", "semanticDimensions": [ // ... ] } ], "semanticMappedFields": [ { "apiName": "unifiedName", "label": "Unified Product", "fields": [ { "tableApiName": "SemanticAccount__dlm", "fieldApiName": "SemanticAccount__dlm_semantic__Name__c" }, { "tableApiName": "SemanticContact__dlm", "fieldApiName": "SemanticContact__dlm_semantic__FirstName__c" }, { "tableApiName": "SemanticOrder__dlm", "fieldApiName": "SemanticOrder__dlm_semantic__Name__c" } ], "semanticDimension": { "label": "Unified Name", "dataType": "Text" } }, { "apiName": "unifiedDate", "label": "Unified Date", "fields": [ { "tableApiName": "SemanticAccount__dlm", "fieldApiName": "SemanticAccount__dlm_date__c" }, { "tableApiName": "SemanticOrder__dlm", "fieldApiName": "SemanticOrder__dlm__date__c" } ], "semanticDimension": { "label": "Unified Date", "dataType": "Date" } } ] } ] } ], "semanticDataObjects": [] } } ``` Note that `unifiedDate` maps only `SemanticAccount__dlm` and `SemanticOrder__dlm` — it omits `SemanticContact__dlm`, so rows contributed by the Contact object return `NULL` for that column. ### Custom SQL logical views A custom SQL logical view is a `SemanticLogicalView` with `semanticViewTypeEnum: "CustomSQL"` whose fields are the output columns of a SQL statement defined in the model. Two arrays drive it: - **`semanticDataObjects`** declares the view's queryable output fields, and **`customSQLV2`** holds the SQL statement that produces them. Each output field's `dataObjectFieldName` must match, case included, a column returned by the statement's **outermost** `SELECT` (see [Write the SQL](#write-the-sql-alias-the-outermost-columns-quote-every-identifier) below). - **`referenceIntegritySemanticDataObjects`** declares the underlying data objects the statement reads from (see [Reference integrity](#reference-integrity) below). There is no query-time SQL construct: you never send raw SQL in a request. The SQL lives in the model, and a query references the resulting view's columns by name exactly as it would any other data object — with `table_field`, using each output field's `apiName` as the column `name` and the logical view's `apiName` as the `table_name`. #### Write the SQL: alias the outermost columns, quote every identifier Two rules make the statement resolve correctly: 1. **Alias each column of the outermost `SELECT`; leave inner subqueries unaliased.** Nested subqueries just pass their columns up — don't rename them there. Apply the final output name once, on the outermost `SELECT`, with `AS ""`. Those outermost aliases are the only names the view exposes, and each must equal the `dataObjectFieldName` of a `semanticDataObjects` field. 2. **Double-quote every table and column identifier.** Data 360 identifiers are case-sensitive and can contain mixed case or special characters (`ssot__Id__c`). Unquoted identifiers are case-folded and won't bind; quoting (`"ssot__Account__dlm"`, `"ssot__Id__c"`, `AS "AccountId"`) matches them exactly. Every table and column the statement reads must be declared in `referenceIntegritySemanticDataObjects` (see [Reference integrity](#reference-integrity)). For readability, the SQL used below (the escaped one-line value of `customSQLV2` in the request) is: ```sql SELECT "ssot__Account__dlm"."ssot__Id__c" AS "AccountId", "ssot__Account__dlm"."ssot__Name__c" AS "AccountName" FROM "ssot__Account__dlm" ``` The statement reads from the DMO `ssot__Account__dlm` and returns two columns. The outermost `SELECT` aliases each output column (`AS "AccountId"`, `AS "AccountName"`); those two aliases are exactly the `dataObjectFieldName` values in `semanticDataObjects`. ```json { "structured_semantic_query": { "fields": [ { "expression": { "table_field": { "name": "OrderCostBookSDO_AccountId", "table_name": "OrderCostBook" } }, "alias": "OrderCostBook.OrderCostBookSDO_AccountId" }, { "expression": { "table_field": { "name": "OrderCostBookSDO_AccountName", "table_name": "OrderCostBook" } }, "alias": "OrderCostBook.OrderCostBookSDO_AccountName" } ], "options": { "limit_options": { "limit": 100 }, "detailed_rows": true } }, "semantic_model": { "apiName": "Sales", "label": "Sales", "semanticLogicalViews": [ { "apiName": "OrderCostBook", "label": "Order Cost Book", "customSQLV2": "SELECT \"ssot__Account__dlm\".\"ssot__Id__c\" AS \"AccountId\", \"ssot__Account__dlm\".\"ssot__Name__c\" AS \"AccountName\" FROM \"ssot__Account__dlm\"", "semanticDataObjects": [ { "apiName": "OrderCostBookSDO", "label": "Order Cost Book Output", "dataObjectName": "OrderCostBookSDO", "dataObjectType": "CustomSQL", "semanticDimensions": [ { "apiName": "OrderCostBookSDO_AccountId", "label": "Account Id", "dataType": "Text", "dataObjectFieldName": "AccountId" }, { "apiName": "OrderCostBookSDO_AccountName", "label": "Account Name", "dataType": "Text", "dataObjectFieldName": "AccountName" } ] } ], "referenceIntegritySemanticDataObjects": [ { "apiName": "OrderCostBook_Account", "label": "ssot__Account__dlm", "dataObjectName": "ssot__Account__dlm", "dataObjectType": "Dmo", "semanticDimensions": [ { "apiName": "OrderCostBook_Account_ssot__Id__c", "label": "ssot__Id__c", "dataObjectFieldName": "ssot__Id__c", "dataType": "Text" }, { "apiName": "OrderCostBook_Account_ssot__Name__c", "label": "ssot__Name__c", "dataObjectFieldName": "ssot__Name__c", "dataType": "Text" } ] } ] } ] // ... } } ``` Trace the two ends against the SQL: - **Output side** — the outermost `SELECT` returns `AccountId` and `AccountName`. Each is the `dataObjectFieldName` of a `semanticDataObjects` field, whose `apiName` (`OrderCostBookSDO_AccountId`, `OrderCostBookSDO_AccountName`) is what the query references as the column `name`. - **Source side** — the SQL reads from the DMO `ssot__Account__dlm`, which gets a `referenceIntegritySemanticDataObjects` entry whose `label` is that DMO and whose fields list the columns the SQL touches (`ssot__Id__c` and `ssot__Name__c`). The engine uses these mappings to bind the statement to the real DMO and columns before running it. #### Reference integrity `referenceIntegritySemanticDataObjects` records which underlying data objects — and which of their fields — a custom SQL statement depends on. It serves two purposes: - **Resolution.** It binds the tables and columns named in `customSQLV2` to physical DMOs and their fields so the statement can run against real tables — each entry's `label` names the DMO, and each field's `label` names the column. - **Dependency tracking.** It is the record that a DMO is *in use* by this custom SQL view. Because the SQL text is opaque, without this declaration nothing else in the model would know the view depends on those objects — a referenced DMO could be deleted out from under the statement, silently breaking it. Listing every table and field the SQL touches here is what lets the platform recognize the dependency and prevent deleting an object while a custom SQL view still references it. For that protection to hold, list **every** table and field the statement reads in `referenceIntegritySemanticDataObjects`, even ones that never surface as output columns. > **Performance:** A custom SQL logical view is treated as an opaque subquery. Outer filters and limits are not pushed into it, so the inner statement materializes in full before they apply. > **Performance:** Selecting fewer columns does not reduce work inside the custom SQL — every column, join, and aggregation in the statement is computed regardless of what the outer query selects. > **Performance:** For an expensive custom SQL view, consider precomputing it upstream to cut query-time cost. ### Organizational hierarchies An organizational hierarchy — such as a role, manager, or territory hierarchy — is a `SemanticLogicalView` with `semanticViewTypeEnum: "Hierarchy"` that derives a transitive parent-child table from an underlying parent-key relationship. It lets a query aggregate values that roll up through parent-child relationships and exposes generated fields such as `level` and `path`. Most of the hierarchy is transparent at query time: you reference the hierarchical view's fields by name, and the engine performs the recursive walk. The one construct you set directly in a query is the `HierarchyScope` filter operator, which limits a result to the subtree rooted at a specific node. > **Performance:** On-the-fly hierarchy resolution uses a recursive traversal whose cost grows with hierarchy depth and node count; consider a persisted hierarchy for large trees. #### Query a hierarchy Reference the hierarchical logical view's fields — such as the generated `level` and `path` fields — by `table_field`, using the view's `apiName` as the `table_name`. Set `row_grouping: true` on the fields you want to group by. ```json { "structured_semantic_query": { "fields": [ { "expression": { "table_field": { "name": "level", "table_name": "UserManagementHierarchy" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_NONE", "semantic_dimension_type": "SEMANTIC_DIMENSION_TYPE_NUMBER", "alias": "level", "row_grouping": true }, { "expression": { "table_field": { "name": "path", "table_name": "UserManagementHierarchy" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_NONE", "semantic_dimension_type": "SEMANTIC_DIMENSION_TYPE_TEXT", "alias": "path", "row_grouping": true } ], "options": {} }, "semantic_model": { "apiName": "Sales", "label": "Sales", "semanticLogicalViews": [ { "apiName": "UserManagementHierarchy", "label": "User Management Hierarchy", "semanticViewTypeEnum": "Hierarchy", "semanticUnions": [ // ... ] } ] // ... } } ``` > **Performance:** A persisted (materialized) hierarchy is read from a precomputed table instead of being resolved with a recursive traversal at query time, which substantially reduces query cost. #### Scope a hierarchy to a subtree Use the `HierarchyScope` operator to limit a hierarchy result to the subtree rooted at a specific node. In a `flatten_filter` (or a model filter), set `"operator": "HierarchyScope"` with the root node identifier as `value`, referencing the hierarchy's parent-key field. ```json { "structured_semantic_query": { "fields": [ // ... ], "flatten_filter": { "filterLogic": "1", "filters": [ { "fieldName": "[UserRoleObject].[ParentRoleId]", "operator": "HierarchyScope", "value": "ROLE_VP_SALES" } ] }, "options": {} } // ... } ``` The same scope can be expressed as a structured predicate with the `BINARY_OPERATOR_HIERARCHY_SCOPE` operator, using a `table_field` left expression and a `string_expression` right operand for the root node. ```json { "structured_semantic_query": { "filter": { "binary_predicate": { "left_expression": { "table_field": { "name": "ParentRoleId", "table_name": "UserRoleObject" } }, "binary_operator": "BINARY_OPERATOR_HIERARCHY_SCOPE", "right_expression": { "string_expression": "ROLE_VP_SALES" } } }, "options": {} } // ... } ``` > **Performance:** Scope a hierarchy query to a subtree with `HierarchyScope` to prune the traversal to the nodes under a chosen root. #### Combine a hierarchy with a measure from another object A hierarchy is usually related to a fact object — sales, opportunities, usage — through a top-level relationship (see [Relationships & Joins](/docs/data/semantic-layer/guide/query-api-in-depth-relationships.md)). Once related, a measure from the fact object rolls up through the hierarchy: the value shown for a node includes the values of everything beneath it. You can use the hierarchy to filter that measure in two ways, depending on whether the hierarchy's own fields appear in the result. ##### Filter by a hierarchy without selecting hierarchy fields To restrict a fact object's rows to a subtree without showing any hierarchy column, select only the fact object's fields and apply `BINARY_OPERATOR_HIERARCHY_SCOPE` to a field of the hierarchy. The hierarchy acts purely as a filter: none of its fields — such as `level` or `path` — are returned, and the result is limited to rows whose owner falls in the subtree rooted at the value, including the root itself. The example below selects four fields from an `OpportunitiesWithHier` object and scopes the result to the subtree under `ROLE_VP_SALES` in the `Role_oh` hierarchy. It also requests subtotals and a grand total. ```json { "structured_semantic_query": { "fields": [ { "expression": { "table_field": { "name": "AccountName", "table_name": "OpportunitiesWithHier" } }, "alias": "account_name" }, { "expression": { "table_field": { "name": "OwnerId", "table_name": "OpportunitiesWithHier" } }, "alias": "owner_id" }, { "expression": { "table_field": { "name": "StageName", "table_name": "OpportunitiesWithHier" } }, "alias": "stage_name" }, { "expression": { "table_field": { "name": "Amount", "table_name": "OpportunitiesWithHier" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM", "alias": "amount" } ], "filter": { "binary_predicate": { "left_expression": { "table_field": { "name": "Role_Id1", "table_name": "Role_oh" } }, "binary_operator": "BINARY_OPERATOR_HIERARCHY_SCOPE", "right_expression": { "string_expression": "ROLE_VP_SALES" } } }, "options": { "subtotals": true, "grand_total": true } } // ... } ``` The scope can sit alongside ordinary filters on the fact object by joining the predicates with a `logical_binary_predicate`. ##### Filter by a hierarchy while selecting hierarchy fields When the hierarchy's fields are part of the result, the scope still limits the rows to the subtree, and the measure is reported for each node that remains. The example below groups by the hierarchy's `level` and `RoleId` and sums `Sales__c` from a related `Sales_multirows__dlm` object, scoped to the subtree under `ROLE_VP_SALES`. ```json { "structured_semantic_query": { "fields": [ { "expression": { "table_field": { "name": "level", "table_name": "UserManagementHierarchy" } }, "alias": "level", "row_grouping": true }, { "expression": { "table_field": { "name": "RoleId", "table_name": "UserManagementHierarchy" } }, "alias": "RoleId", "row_grouping": true }, { "expression": { "table_field": { "name": "Sales__c", "table_name": "Sales_multirows__dlm" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM", "alias": "Sales__c" } ], "filter": { "binary_predicate": { "left_expression": { "table_field": { "name": "RoleId", "table_name": "UserRoleObject" } }, "binary_operator": "BINARY_OPERATOR_HIERARCHY_SCOPE", "right_expression": { "string_expression": "ROLE_VP_SALES" } } } } // ... } ``` The result contains the root and every role beneath it. Each role's sales include the sales of its descendants, so a node's value is never smaller than the sum of its children: | level | RoleId | Sales__c | | ----- | ----------------- | --------- | | 2 | `ROLE_VP_SALES` | 312004.00 | | 3 | `ROLE_SALES_MGR` | 192003.00 | | 4 | `ROLE_SALES_REP1` | 45001.00 | | 4 | `ROLE_SALES_REP2` | 52001.00 | #### Group by ancestors with ANCESTOR_AT_LEVEL `ANCESTOR_AT_LEVEL(level, field)` returns the value of a hierarchy field for the ancestor of the current node at the given level, where level 1 is the top of the tree. Use one calculation per level you want to show to lay a hierarchy out as columns — for example CEO, VP, and manager — instead of as a `level`/`path` pair. Define each one as an on-the-fly `calculated_field` (see [Calculated Fields](/docs/data/semantic-layer/guide/query-api-in-depth-calculated-fields.md#define-an-on-the-fly-calculated-field)) with `row_grouping: true`. A node that is shallower than the requested level has no ancestor at that level, so the calculation returns `NULL` for it. In the result, `John CEO` has no level 2 or level 3 ancestor, and `Diana VP Marketing` has no level 3 ancestor. The field you pass must belong to the hierarchy, and the table in the reference must be an organizational hierarchy logical view. The `level` must be a positive integer. ##### Add the individual value for each node A plain `SUM` of a related measure rolls up: a node includes everything beneath it. To report the value that belongs to the node itself, with nothing added from its descendants, sum a calculation that keeps only the node's own row by testing the hierarchy's level field. The example below groups by the first three ancestors and returns each node's individual sales. ```json { "structured_semantic_query": { "fields": [ { "expression": { "calculated_field": { "name": "hierarchylevel:1", "expression": "ANCESTOR_AT_LEVEL(1, [Role_oh].[User9_Full_Name])", "calculated_dimension_expression": { "dimension_output_type": "SEMANTIC_DIMENSION_TYPE_TEXT" }, "syntax": "Tua" } }, "alias": "level_1", "row_grouping": true }, { "expression": { "calculated_field": { "name": "hierarchylevel:2", "expression": "ANCESTOR_AT_LEVEL(2, [Role_oh].[User9_Full_Name])", "calculated_dimension_expression": { "dimension_output_type": "SEMANTIC_DIMENSION_TYPE_TEXT" }, "syntax": "Tua" } }, "alias": "level_2", "row_grouping": true }, { "expression": { "calculated_field": { "name": "hierarchylevel:3", "expression": "ANCESTOR_AT_LEVEL(3, [Role_oh].[User9_Full_Name])", "calculated_dimension_expression": { "dimension_output_type": "SEMANTIC_DIMENSION_TYPE_TEXT" }, "syntax": "Tua" } }, "alias": "level_3", "row_grouping": true }, { "expression": { "calculated_field": { "name": "individual_sales", "expression": "IF [Role_oh].[Role_Level] = 1 THEN [Role_Hierarchy_06_09].[Sales] ELSE 0 END", "calculated_measure_expression": { "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER" }, "syntax": "Tua" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM", "alias": "individual_sales" } ], "options": { "sort_orders": [ { "simple_sort_order": { "sort_by_field_alias": "level_1", "sorting_order": "ASC" } }, { "simple_sort_order": { "sort_by_field_alias": "level_2", "sorting_order": "ASC" } }, { "simple_sort_order": { "sort_by_field_alias": "level_3", "sorting_order": "ASC" } } ] } } // ... } ``` Each row is one node, identified by its ancestors from the top down. The sales are the node's own, not the total for its team: | level_1 | level_2 | level_3 | individual_sales | | -------- | ------------------ | ------------------------ | ---------------- | | John CEO | `NULL` | `NULL` | 50000.00 | | John CEO | Diana VP Marketing | `NULL` | 41000.00 | | John CEO | Diana VP Marketing | Edward Marketing Manager | 50000.00 | | John CEO | Jane VP Sales | `NULL` | 45000.00 | | John CEO | Jane VP Sales | Bob Sales Manager | 88000.00 | To get the rolled-up value alongside, add a second field that sums the measure directly (`table_field` with `SEMANTIC_AGGREGATION_METHOD_SUM`), as in the previous examples. #### Totals with a hierarchy Subtotals and grand totals work on hierarchical results the same way they do on any grouped result: set `subtotals` and `grand_total` in `options` (see [Aggregation & Totals](/docs/data/semantic-layer/guide/query-api-in-depth-aggregation.md)). With ancestor columns as the grouping fields, a subtotal row is produced for every level of the tree, and the grand total row spans the whole dataset. The example below lays a manager hierarchy out across four `ANCESTOR_AT_LEVEL` columns and sums `Sales22` from a related sales object, with both totals on. ```json { "structured_semantic_query": { "fields": [ { "expression": { "calculated_field": { "name": "hierarchylevel:1", "expression": "ANCESTOR_AT_LEVEL(1, [Manager_oh].[Full_Name20])", "calculated_dimension_expression": { "dimension_output_type": "SEMANTIC_DIMENSION_TYPE_TEXT" }, "syntax": "Tua" } }, "alias": "level_1", "row_grouping": true } // ... the same calculation for levels 2, 3, and 4 , { "expression": { "table_field": { "name": "Sales22", "table_name": "Hierarchy_Sales26" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM", "alias": "sales" } ], "options": { "subtotals": true, "grand_total": true } } // ... } ``` The first row of the result is the grand total, with `NULL` in every grouping column. Each subtotal row repeats the ancestor columns it covers and leaves the deeper ones `NULL`: | level_1 | level_2 | level_3 | level_4 | sales | | -------- | ------------------ | ------------------------ | --------------------- | --------- | | `NULL` | `NULL` | `NULL` | `NULL` | 705000.00 | | John CEO | `NULL` | `NULL` | `NULL` | 274000.00 | | John CEO | Diana VP Marketing | `NULL` | `NULL` | 91000.00 | | John CEO | Diana VP Marketing | Edward Marketing Manager | `NULL` | 50000.00 | | John CEO | Diana VP Marketing | Edward Marketing Manager | Frank Marketing Rep 1 | 19000.00 | ## Reference Only the fields introduced on this page are listed. For the full request schema, see [Request Reference](/docs/data/semantic-layer/guide/query-api-in-depth-fields.md). | Field (wire name) | Type | Required | Description | | ----------------------------------------------------- | --------------------- | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `semanticLogicalViews` | SemanticLogicalView[] | N | Logical views on the model. Each bundles member data objects and how they combine (join, union, or custom SQL) and exposes their columns as one queryable object. Defined at authoring time — see Related. | | `table_field` | TableField | N | References a column of a logical view by `name` + `table_name`, where `table_name` is the logical view's `apiName`. | | `semanticViewTypeEnum` | String (enum) | N | The composition type of a logical view: `HardJoin` (standard join, the default), `Union`, `CustomSQL`, or `Hierarchy`. | | `semanticUnions` | SemanticUnion[] | N | On a union logical view, the block that appends member data objects vertically (`UNION ALL`). | | `semanticMappedFields` | SemanticMappedField[] | N | Within a `semanticUnions` block, aligns member columns into a unified column; each declares the source field per member object. | | `customSQLV2` | String | N | On a custom SQL view, the SQL statement whose outermost `SELECT` output columns become the view's fields. Requires `semanticViewTypeEnum: "CustomSQL"`. Alias every outermost column and double-quote every identifier. | | `referenceIntegritySemanticDataObjects` | SemanticDataObject[] | N | On a custom SQL view, the data objects the SQL statement reads from. Binds the SQL to physical DMOs and records the dependency so a referenced object can't be deleted while the view uses it. List every table and field the SQL references. | | `operator` (`"HierarchyScope"`) | String (PascalCase) | N | In `flatten_filter`/model-filter form, limits an organizational-hierarchy result to the subtree rooted at `value`. | | `binary_operator` (`BINARY_OPERATOR_HIERARCHY_SCOPE`) | String (enum) | N | Structured-predicate form of the hierarchy-scope operator; the root node is supplied as the right `string_expression`. | | `ANCESTOR_AT_LEVEL(level, field)` | Function | N | In an on-the-fly `calculated_field`, returns a hierarchy field's value for the ancestor at `level` (1 is the top of the tree), or `NULL` when the node is shallower than `level`. `field` must belong to an organizational-hierarchy logical view. | For the relationship fields that define joins inside a standard logical view, see [Relationships & Joins](/docs/data/semantic-layer/guide/query-api-in-depth-relationships.md) and the authoring entity in Related. ## Limitations - **A logical view exposes composite column names, referenced by the view's own `apiName`.** Use `_` as the column `name` and the logical view's `apiName` as `table_name` — this is the form the view exposes, distinct from a member object's original field `apiName`. - **A union spans one logical view only.** A union lives inside a logical view, cannot coexist with joins in the same logical view, and only one union is supported per logical view. - **Unmapped union columns return `NULL`.** When a union member does not map a given unified column, rows contributed by that member return `NULL` for it. - **Custom SQL logical views have no query-time request construct.** You cannot pass raw SQL in a query; the SQL is defined in the model and referenced by field. - **A custom SQL output field is keyed to the outermost `SELECT`'s column name.** Each `semanticDataObjects` field's `dataObjectFieldName` must exactly match a column the statement's outermost `SELECT` returns, so that `SELECT` must alias every output column and every identifier should be double-quoted; inner subqueries should leave their columns unaliased. - **The `HierarchyScope` value is a single root node.** It scopes an organizational-hierarchy result to that node's subtree; pair it with an `IsNull` condition on the parent-key field to also include entry-level rows. - **`ANCESTOR_AT_LEVEL` applies to hierarchy fields only.** The referenced table must be an organizational-hierarchy logical view and the field must be defined in it; otherwise the query is rejected. The level must be a positive integer. - **Hierarchy measures roll up by default.** A measure from a related object includes the values of a node's descendants. To report a node's own value, use a calculation that tests the hierarchy's level field, as shown above. ## Related - [Dimensions, Measurements & Semantic Data Objects](/docs/data/semantic-layer/guide/query-api-in-depth-data-objects.md) — selecting fields from data objects, including logical views. - [Relationships & Joins](/docs/data/semantic-layer/guide/query-api-in-depth-relationships.md) — joins inside a standard logical view, and connecting a logical view to another data object. - [Filtering](/docs/data/semantic-layer/guide/query-api-in-depth-filtering.md) — filter operators, `flatten_filter`, and structured predicates used by the `HierarchyScope` operator. - [Grouping](/docs/data/semantic-layer/guide/query-api-in-depth-grouping.md) — grouping rows by a hierarchy's `level` and `path` fields. - [Calculated Fields](/docs/data/semantic-layer/guide/query-api-in-depth-calculated-fields.md) — reference model calculated fields over a logical view via `semantic_field`. - [Semantic Logical View](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-logical-view-semanticlogicalview) — Authoring API for defining logical views and their member objects. - [Semantic CustomSql Logical View](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-customsql-logical-view-semanticlogicalview) — Authoring API for custom SQL logical views. - [Request Reference](/docs/data/semantic-layer/guide/query-api-in-depth-fields.md) — Complete request schema.