Step 2 – Define the Analytical Query CDS View

For space requirements this blog has been split in 4 parts:

In continuation to previous blog Step by Step Hierarchies in S/4 HANA Analytics here I will show creation of analytical consumption query View which is used for generation of hierarchy.

4. Analytical Query Creation: View becomes query view if annotation @Analytics.query: true is used, this annotation makes this view visible in query browser.

@EndUserText.label: 'Display a Branch of the Employee Hierarchy with Utilization Rate'
@AbapCatalog.sqlViewName: 'ZEMH_IEQ3'
@Analytics.query: true
@OData.publish: true

define view ZEMH_C_EmployeeQuery3

  as

  select from ZEMH_I_EmployeeHours1

{


  @AnalyticsDetails.query.displayHierarchy: #ON
  @AnalyticsDetails.query.hierarchySettings.hidePostedNodesValues: true
  @AnalyticsDetails.query.hierarchyInitialLevel: 10
  @AnalyticsDetails.query.axis: #ROWS
  @AnalyticsDetails.query.totals: #HIDE
  @AnalyticsDetails.query.display: #KEY_TEXT
  Employee,

  CostCenter,
  PlannedHours,

  ActualHours,
  @AnalyticsDetails.query.formula: 'ActualHours / PlannedHours * 100'
  @EndUserText.label: 'Utilization Rate'
  0 as UtilizationRate
}

Query view uses cube view(ZEMH_I_EmployeeHours1) created previously as data source.

Annotation  @AnalyticsDetails.query.displayHierarchy: #ON  is used for displaying the hierarchy for employee.

Annotation  @AnalyticsDetails.query.hierarchyInitialLevel: 10  is used for default expand level.

Testing the Analytical query:

Tcode RSRT is used for testing analytical query, give in the Query field 2CZEMH_IEQ3 (2C must be added to every analytical query while testing) and execute as show below:

Result of the Query is shown below:

Annotation @AnalyticsDetails.query.axis: #ROWS  is used for defaulting attribute to row characteristics in query browser. if you give axis to COLUMN the it defaults attribute to column in query browser.

Annotation @AnalyticsDetails.query.display: #KEY_TEXT  is used for displaying text of the key. i.e EmployeeName for Employee.

Annotation @AnalyticsDetails.query.totals is used for displaying totals. Here totals are hidden.

Annotation @AnalyticsDetails.query.hierarchySettings.hidePostedNodesValues: true will hide the node values. if you give this as false or remove this annotation then result will have node values as shown below:

Annotation @AnalyticsDetails.query.formula  is used for calculating utilization rate of employee based on planned and actual working hours. So, in this annotation you can write any mathematical formula which is executed dynamically in analytical engine.

Note: The results of analytical query, cube, fact and dimension are recommended to execute from RSRT* transactions as shown, you wont get expected results if you go with normal F8 execution of CDS because execution happens at analytical engine

From above steps hierarchy is achieved.

Annotation @OData.publish: true is used for publishing the service. See my previous blog expose cds view as OData service which shows how to activate the service.

Once service is activated then you can open the service as below :

Click on Odata service link to open the service:ZEMH_C_EMPLOYEEQUERY3_CDS  with entity set: ZEMH_C_EMPLOYEEQUERY3 as shown below with properties:

In the above Odata service ID, TotaledProperties, Employee_NodeID, Employee_NodeIDExt, Employee_NodeText, Employee_ParentID, Employee_Level, Employee_Drillstate, Employee_Nodecount, Employee_T(Employee Text) , PlannedHours_F(PlannedHours Formatted), ActualHours_F(ActualHours Formatted), UtilizationRate_F(Utilization Formatted) attribute properties are generated automatically because of annotation define in the analytical query.

Now backend task of evaluating Employee Manager hierarchy using analytical query CDS view and publishing the Odata service is done. Now we go to next step i.e creating the hierarchy application UI in the WebIDE.

5. SAPUI5 application for Hierarchy:

Open WebIDE and create a new project form template:

Choose SAPUI5 application from the template

Enter Project Name:

Select View Type as XML view and give view name as HierarchyView and finish the project wizard:

Now local Annotations needs to be created for OData service before that create a folder annotations :

In this new folder “annotations”, create a file “annotations.xml”:

copy the local annotations into annotations.xml file which describes the UI properties that cannot be derived from CDS annotations

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx" Version="4.0">
  <edmx:Reference Uri="/sap/bc/ui5_ui5/ui2/ushell/resources/sap/ushell/components/factsheet/vocabularies/UI.xml">
    <edmx:Include Alias="UI" Namespace="com.sap.vocabularies.UI.v1"/>
  </edmx:Reference>
  <edmx:Reference Uri="/sap/opu/odata/sap/ZEMH_C_EMPLOYEEQUERY3_CDS/$metadata">
    <edmx:Include Alias="ZEMH_C_EMPLOYEEQUERY3_CDS" Namespace="ZEMH_C_EMPLOYEEQUERY3_CDS"/>
  </edmx:Reference>
  <edmx:Reference Uri="/sap/bc/ui5_ui5/ui2/ushell/resources/sap/ushell/components/factsheet/vocabularies/Common.xml">
    <edmx:Include Alias="Common" Namespace="com.sap.vocabularies.Common.v1"/>
  </edmx:Reference>
  <edmx:DataServices>
    <Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="ZHierarchy.ZEMH_C_EMPLOYEEQUERY3_CDS">
      <!--===============================================================================
          Line Items
          ================================================================================-->
      <Annotations Target="ZEMH_C_EMPLOYEEQUERY3_CDS.ZEMH_C_EMPLOYEEQUERY3Type">
        <Annotation Term="UI.LineItem">
          <Collection>
            <Record Type="UI.DataField">
              <PropertyValue Property="Value" Path="Employee_NodeText"/>
              <Annotation Term="UI.Importance" EnumMember="UI.ImportanceType/High"/>
            </Record>
            <Record Type="UI.DataField">
              <PropertyValue Property="Value" Path="UtilizationRate_F"/>
              <Annotation Term="UI.Importance" EnumMember="UI.ImportanceType/High"/>
            </Record>
            <Record Type="UI.DataField">
              <PropertyValue Property="Value" Path="CostCenter"/>
              <Annotation Term="UI.Importance" EnumMember="UI.ImportanceType/High"/>
            </Record>
          </Collection>
        </Annotation>
      </Annotations>
      <!--===============================================================================
          Value Helps
          ================================================================================-->
      <Annotations Target="ZEMH_C_EMPLOYEEQUERY3_CDS.ZEMH_C_EMPLOYEEQUERY3Type/CostCenter" xmlns="http://docs.oasis-open.org/odata/ns/edm">
        <Annotation Term="Common.ValueList">
          <Record>
            <PropertyValue Property="Label" String="CostCenter"/>
            <PropertyValue Property="CollectionPath" String="ZHierarchy"/>
            <PropertyValue Bool="false" Property="SearchSupported"/>
            <PropertyValue Property="Parameters">
              <Collection>
                <Record Type="com.sap.vocabularies.Common.v1.ValueListParameterInOut">
                  <PropertyValue Property="LocalDataProperty" PropertyPath="CostCenter"/>
                  <PropertyValue Property="ValueListProperty" String="CostCenter"/>
                </Record>
              </Collection>
            </PropertyValue>
          </Record>
        </Annotation>
      </Annotations>
    </Schema>
  </edmx:DataServices>
</edmx:Edmx>

local annotaion above defines which UI properties such as UI.LineItem, Common.ValueList(filter or valuehelp) visible default in the UI. Here in our example attributes Employee_NodeText,  UtilizationRate_F, CostCenter are lineitems and CostCenter is defined as Valuehelp or filter

Now register the service as a datasource to the application for this open manifest.json with the Descriptor Editor, switch to the “Data Sources” tab, and enter the service under name “ZEMH_C_EMPLOYEEQUERY3_CDS“. Afterwards, add an entry to the “Annotations” section, with name “localAnnotations“, URI “annotations/annotations.xml” pointing to the file just created, and the same URI for the local URI. With this, the form should look like below:

Click on + button as shown above to add the service

Now define a model with which the UI view can access the data source. For this, save changes and switch to the Code Editor for manifest.json, locate the section “sap.ui5.models”, and add this new model with an empty identifier (“”). Save the changes:

		"models": {
			"i18n": {
				"type": "sap.ui.model.resource.ResourceModel",
				"settings": {
					"bundleName": "ZHierarchy.i18n.i18n"
				}
			},
			 "": {
        "dataSource": "ZEMH_C_EMPLOYEEQUERY3_CDS"
    }
		},

Now View and Controller needs to be modified which displays hierarchy in the UI. I will continue this in the next blog Step 3. Smart TreeTable UI for Hierarchies

Your suggestions, feedback, comments on this blog are most welcome.