Skip to Content
Technical Articles
Author's profile photo Pavan Kumar Reddy

Step 2 – Define the Analytical Query CDS View

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

   Link   Content
Hierarchy evaluation using CDS Table Function  Hierarchy evaluation using CDS Table Function in S/4
Step 1 – Hierarchies in S/4 HANA  Analytics  Step 1 – Hierarchies in S/4 HANA  Analytics
This part  Step 2 – Define the Analytical Query CDS View
 Step 3. Smart TreeTable UI for Hierarchies  Step 3 – Smart Tree Table UI for Hierarchies

 

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.

 

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Pavan,

       

      Best Blog on CDS Hierarchy!

       

      One question – I am working on S/4HANA 1511, the below annotations (highlighted part) are coming in red and the text of Employee not showing up in RSRT. Any idea if it is due to the version?

      @AnalyticsDetails.query.hierarchySettings.hidePostedNodesValues: true

      @AnalyticsDetails.query.display: #KEY_TEXT 

       

      Thanks & Regards,

      Remya

      Author's profile photo Pavan Kumar Reddy
      Pavan Kumar Reddy
      Blog Post Author

      Hi Remya,

      Thanks for the compliment.

      if annotations are appearing red that means it is version problem, some times even after appearing red they might work and they automatically change to original color once version updated.

      Text of Employee not showing up in RSRT because in Dimension view of Step 1 I have used  @ObjectModel.text.element: [ 'EmployeeName' ] for Employee key field and explanation for this annotation is provided there. Please check in your code whether this was handled, if it is handled still it is not showing up it might be version issue since it is appearing red.

      Regards,

      Pavan

       

      Author's profile photo Former Member
      Former Member

      Hi Pavan,

       

      @ObjectModel.text.element: [ ‘EmployeeName’ ]  is there in my dimension view code, I suppose the issue might be with the system version.

       

      Thanks again!

       

      Regards,

      Remya

      Author's profile photo Vivek Ramasamy
      Vivek Ramasamy

      Hi,

      We tried to display hierarchy using Hierarchy CDS views and it is fine in "Analysis Office" but the expansion of nodes are not working if the same is consumed in Fiori Elements List report.

      The issue is detailed in (https://answers.sap.com/questions/581579/node-expand-not-working-in-hierarchy-cds-view-cons.html). Please can you let us know your comments.

      Regards,

      Vivek

       

      Author's profile photo Bhargava Tanguturi
      Bhargava Tanguturi

      Hello Pavan,

      Please look at my question regarding time hierarchy in Query report.

      https://answers.sap.com/questions/12939787/how-to-show-time-hierarchy-in-query-report-rsrt-tr.html

       

      Thanks,

      Bhargava

      Author's profile photo SHUBHANGI DESHMUKH
      SHUBHANGI DESHMUKH

      hello Pavan,

      In SAC or AFO total of average is not working correctly , please help me.

      Actual total is 708.205 but I am getting as 431.088

       

      Author's profile photo SAP HANA
      SAP HANA

      Hi Pavan,

      In Query View am facing below error on Employee key field for the Annotation @AnalyticsDetails.query.displayHierarchy: #ON

      Annotations of Employee use hierarchies, but Employee does not have any [Analytics]

      Because of this am unable to show hierarchy in RSRT and unable to consume in SAC or Fiori, Please assist

      AnnotationError