Skip to Content
Author's profile photo Joseph BERTHE

Fiori Elements: Analytical table with SADL

Introduction

The purpose of this blog is to show you how to create a simple analytical table mapped to an ABAP view/table without CDS implementation.

Why that? Still I have some customers which are not ready for HANA or even using CDS view from their systems. So the requirements was to use some powerful feature from Fiori to expose table as an Analytical table like ALV component.

I based my work on this document How to Use OData Analytics in SADLBased Services

Here is the result we are going to produce together 🙂

Prerequisite

To do this step-by-step, you need knowledge on the following areas:

  • Transaction SEGW
    • Data mapping concept
  • SAP Fiori Elements – List Report
  • OData annotations
  • ABAP.. of course 🙂

Let’s start

Procedure

We are going to expose for our example the MARC’s content table.

Step 01: SEGW

  • Create a Project
  • Create an entityType such as MaterialDivision
    1. In this entity determine the relevant properties
    2. For SADL purpose you will have to create manually a property called GENERATED_ID, which will be the only key of your entity type

  • In service implementation map your entity set to a data source from DDIC structure MARC. and generate the mapping.

Save and generate your project, then deploy your service into the gateway.

Step 02: Implementation

Let’s go to the implementation part. There are two areas we have to code something. The first is in the MPC_EXT and the second in the DPC_EXT.

In MPC_EXT we have to specify the semantic attribute of our entity type. As explained in this wiki page we have to set the attribut to aggregate :

 sap:semantics=“aggregate“

And for the properties of the entity we have to identify which will be a dimension and wich will be a measure attribute.

sap:aggregation-role="dimension"
or
sap:aggregation-role="measure"

Look at the metatadata you have to have:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:sap="http://www.sap.com/Protocols/SAPData">
    <edmx:DataServices m:DataServiceVersion="2.0">
        <Schema Namespace="ZJBE_SADL_DEMO_SRV" xml:lang="fr" sap:schema-version="1" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
            <EntityType Name="MaterialDivision" sap:semantics="aggregate" sap:content-version="1">
                <Key>
                    <PropertyRef Name="GENERATED_ID" />
                </Key>
                <Property Name="Matnr" Type="Edm.String" Nullable="false" MaxLength="18" sap:aggregation-role="dimension" sap:unicode="false" sap:label="Article" sap:creatable="false" sap:updatable="false" />
                <Property Name="Minbe" Type="Edm.Decimal" Nullable="false" Precision="13" Scale="3" sap:aggregation-role="measure" sap:unicode="false" sap:label="Point commande" sap:creatable="false" sap:updatable="false" />
                <Property Name="Bstfe" Type="Edm.Decimal" Nullable="false" Precision="13" Scale="3" sap:aggregation-role="measure" sap:unicode="false" sap:label="Lot fixe" sap:creatable="false" sap:updatable="false" />
                <Property Name="Werks" Type="Edm.String" Nullable="false" MaxLength="4" sap:aggregation-role="dimension" sap:unicode="false" sap:label="Division" sap:creatable="false" sap:updatable="false" />
                <Property Name="GENERATED_ID" Type="Edm.String" Nullable="false" sap:aggregation-role="dimension" sap:unicode="false" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
            </EntityType>
            <EntityContainer Name="ZJBE_SADL_DEMO_SRV_Entities" m:IsDefaultEntityContainer="true" sap:supported-formats="atom json xlsx">
                <EntitySet Name="MaterialDivisions" EntityType="ZJBE_SADL_DEMO_SRV.MaterialDivision" sap:creatable="false" sap:updatable="false" sap:deletable="false" sap:requires-filter="true" sap:content-version="1" />
            </EntityContainer>
            <atom:link rel="self" href="http://<server>:8000/sap/opu/odata/SAP/ZJBE_SADL_DEMO_SRV/$metadata" xmlns:atom="http://www.w3.org/2005/Atom" />
            <atom:link rel="latest-version" href="http://<server>:8000/sap/opu/odata/SAP/ZJBE_SADL_DEMO_SRV/$metadata" xmlns:atom="http://www.w3.org/2005/Atom" />
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

Now let’s go to the implementation of the data provider class. Because you have mapped your data to a DDIC~MARC, the SEGW code generator has implemented your DPC class to the SADL API.

Redefine the method : IF_SADL_GW_QUERY_CONTROL~SET_QUERY_OPTIONS to specify which of our properties are a measure attributes and what we want to do (SUM, Average,…):

 io_query_options->set_aggregation( VALUE #(
          ( element = 'MINBE' alias = 'MINBE' type = if_sadl_gw_query_options=>co_aggregation_type-sum )
          ( element = 'BSTFE' alias = 'BSTFE' type = if_sadl_gw_query_options=>co_aggregation_type-sum )
          ) ).

Step 03: Testing OData service

When you test your service you will notice that the result is strange :

{
  "d" : {
    "results" : [
      {
        "__metadata" : {
          "id" : "http://SAPHIR101.saphir.local:8000/sap/opu/odata/SAP/ZJBE_SADL_DEMO_SRV/MaterialDivisions('.1~000000000000000002.2~20')",
          "uri" : "http://SAPHIR101.saphir.local:8000/sap/opu/odata/SAP/ZJBE_SADL_DEMO_SRV/MaterialDivisions('.1~000000000000000002.2~20')",
          "type" : "ZJBE_SADL_DEMO_SRV.MaterialDivision"
        },
        "Matnr" : "000000000000000002",
        "Minbe" : "0.000",
        "Bstfe" : "0.000",
        "Werks" : "20",
        "GENERATED_ID" : ".1~000000000000000002.2~20"
      }
    ]
  }
}

Automatically the SADL API has generated an unique key. Now the question is how to retreive data from the entity with that key?

Let’s redefine the method : /IWBEP/IF_MGW_APPL_SRV_RUNTIME~GET_ENTITY and in it insert this peace of code which will help you to retreive the business key :

    if_sadl_gw_dpc_util~get_dpc( )->get_keys_from_analytical_id(
 EXPORTING io_tech_request_context = io_tech_request_context
 IMPORTING et_keys = DATA(lt_keys) ).

In the LT_KEYS you have the key :

Step 04: Create a Fiori Element

For this example, we are going to create a List report Fiori elements. If you follow the wizard from the Web IDE, you will have only to add an annotation file and add annotations like this :

The selectionFields is not mandatory for our example. And modify the manifest.json to specify we are using an Analytical table:

You just need to test the application and you will see the miracle 🙂

Enjoy 😉

Assigned Tags

      29 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Javier Caceres
      Javier Caceres

      Good tip

      Author's profile photo Joseph BERTHE
      Joseph BERTHE
      Blog Post Author

      It is a pleasure 🙂

      Author's profile photo Mahesh Kumar Palavalli
      Mahesh Kumar Palavalli

      Good Tip!!, will try this out probably using the CDS view annotations 🙂

      Author's profile photo Joseph BERTHE
      Joseph BERTHE
      Blog Post Author

      Thanks 🙂

      Author's profile photo Simon Roloff
      Simon Roloff

      Hi Joseph,

      thanks for this nice blog. I also implemented a solution like you described in your blog and it works fine!! Have you ever tried Edm.DateTime Fields mapped to ABAP DATS DataElements. I could not get these working with ODATA services generated like that.

      Thanks!

      Simon

      Author's profile photo Joseph BERTHE
      Joseph BERTHE

      Hi,

      What is your issue ? With date, there is a trick 🙂 set the attribute date as nullable otherwise, if the date is null an exception is raised.

      Regards,

      Joseph

      Author's profile photo Simon Roloff
      Simon Roloff

      I get this error:

      my Service looks like

      and I generated the structure using Import-> from ABAP Dict. Structure (choosing the DB Table). My DB Table.

      Thanks!

      Author's profile photo Joseph BERTHE
      Joseph BERTHE

      Hi,

      Sorry for the delay of my answer.

      Can you click on the little icon "ABAP type editor" and see if there is something strange. Usaly with date field, I prefer DATUM as a type (but this is not the problem here).

      Regards,

      Joseph

      Author's profile photo Jay Malla
      Jay Malla

      Hi Joseph,

      I am following your blog.  I am able to add the dimensions and measures.  I also added a GENERATED_ID as the key, but I do not see any option to redefine IF_SADL_GW_QUERY_CONTROL~SET_QUERY_OPTIONS.

      I do not see this method under the methods for my DPC_EXT class.  Any ideas what I may be missing?

      Thanks,

      Jay

      Author's profile photo Joseph BERTHE
      Joseph BERTHE
      Blog Post Author

      Hi,

      You have to map a data source to your entity, such as a CDS or a deux object.

      Regards,

      Joseph

      Author's profile photo Jay Malla
      Jay Malla

      Hi Joseph,

       

      Thanks for your quick response.  I had generated the entity from an RFC.  

      So in order to do this, am I not able to create the Data source through an RFC?

      If I create a CDS view instead and then do the logic in the DPC_EXT class to call the RFC, it seems like a lot of work.  Not sure how to proceed.

       

      Thanks,

      Jay

      Author's profile photo Joseph BERTHE
      Joseph BERTHE
      Blog Post Author

      Hello,

      Go to the service implementation folder, there you will have your entity set and by a right click, you will have the feature : Map to data source.

      Regards,

      Joseph

      Author's profile photo Former Member
      Former Member

      hi Joseph,

      thanks for the blog.

      my requirement is almost simlilar to add 3 columns from REGUP table to a analytical smart table in fiori, which are dimensions. I need to make them as sap-aggregate : dimension. but, service implementation, i dont see anything to Map to data source. how do i achieve this?

      in MPC extension define method, how do i set this aggregate property to 3 attributes?

      please help me as i am trying from very long time. i have attached the screenshot. its coming for standard attributes. but not to custom fields.

      Thanks,

      Soumya

      Author's profile photo Santhosini K
      Santhosini K

      Hi,

       

      I am able to achieve the aggregation and subtotals.

      But I m not able to see the collapsible button which used to come in the extreme left corner.

      more over , i m not able to see the dynamic "aggregate" , "sort" button when i click on the coloumn.

      Please help

      Author's profile photo Kseniya Klitsunova
      Kseniya Klitsunova

      The SADL API has not generated an unique key. How to fix it?

      Author's profile photo Joseph BERTHE
      Joseph BERTHE
      Blog Post Author

      Hello, did you re-generate your segw project ?

      Author's profile photo Kseniya Klitsunova
      Kseniya Klitsunova

      Yes, it didn't help

      Author's profile photo Rohan Malik
      Rohan Malik

      hi,

       

      I was able to do it as suggested via MARC mapping to data source but in my actual app, it's a

      custom oData Service, so how is the GENERATED_ID added to my custom entityset ? I suppose

      that I have to implement this on my own, but when i do it, the GENERATED_ID is empty !

      Author's profile photo Anil Paladugu
      Anil Paladugu

      Hi Rohan,

      I am facing same issue, not able to populate GENERATED_ID in IF_SADL_GW_QUERY_CONTROL~SET_QUERY_OPTIONS method, let me know if you are able to populate/generate GENERATED_ID.

      Author's profile photo Rohan Malik
      Rohan Malik

      yes, let me know which data source are you using ? use SADL only when the data source is SAP defined data source or CDS / BOPF. Do not use custom structure or Internal table as data source .Generated_ID would never work !

      Author's profile photo Anil Paladugu
      Anil Paladugu

      Hi Rohan,

      I have used custom structure(Complete custom solution/app) on BW system.

      I have developed custom solution with which i am able to do summation of rows when ever a field(item field) is drooped from the smarttable.

       

      Author's profile photo Rohan Malik
      Rohan Malik

      Geneated_Id got filled without writing any custom code even with custom structure ? i am intrigued as SAP themselves replied that SADL would fail if datasource is not SAP generated like MARC in this and we have to write entire logic in our own for making total /sub total work ! can you give more details how you achieved it as we had to write a code of 500-600 lines to finally achieve it.

      Author's profile photo Anil Paladugu
      Anil Paladugu

      You are right, I dint use SADL methods and Analytical  table in the final solution.

      I have used Smarttable of type "Table"

      I have done following in OnBeforeRebindTable function.

      1. I am Sending list of fields(selected for display) to backend using setHeader method.
      2. When ever a field is drooped from table rebindTable method will not execute, so I have done following to explicitly execute rebindTable method.
      • Added Eventlistener to Setting Button on smarttable
      • Added Eventlistener to OK Button of setting popup to do "rebindTable(true)"

      3. In backend I am reading list of fields selected for display and using AMDP i am performing the summation and sending data back to UI

      Note: I am not able to display Summation in the table end because I am not using Annotation table, I am displaying summation details it in header section of the view.

      The reason for following this approach is when ever user has drooped some fields from table they want to see the adjusted/recalculated values(Analytical table behavior) in the table.

      Author's profile photo Anil Paladugu
      Anil Paladugu

      Hi Joseph,

      I am facing same issue, not able to populate GENERATED_ID in IF_SADL_GW_QUERY_CONTROL~SET_QUERY_OPTIONS method, let me know if you are able to populate/generate GENERATED_ID.

      I have done mapping, added dimensions in MPC_EXT class. on debugging 'set_query_options' I can see all dimension which i have added in MPC_EXT class.

      Let me know if I have missed some steps.

      Author's profile photo Kshitij Saxena
      Kshitij Saxena

      Hi Joseph -  I am working on system which is on 730, so I am not using SADL framework. Will I have to write my own code to populate GENERATED_ID?

      Author's profile photo Victor Hmilev
      Victor Hmilev

      Hi Joseph.

      How does an entity update work? Do you have an example? Please help

       

      Author's profile photo Joseph BERTHE
      Joseph BERTHE
      Blog Post Author

      Hello,

      If by saying "update" you mean create a CRUD application, then you will need to redefine CREATE_ENTITY and UPDATE_ENTITY to put your code. SADL cannot do CRUD unless you use BOPF object.

      Regards,

      Joseph

       

      Author's profile photo Vlad K
      Vlad K
      Thanks for the answer
      Author's profile photo Saswata Chandra
      Saswata Chandra

      Hi Joseph,

      I was trying to develop an Analytical List Page Application and got to know that the entities used in creating such application should be updated with "aggregate" semantic and most importantly the properties have to be updated with "dimension" and "measure".

      Through your "How to Use OData Analytics in SADL-Based Services" I managed to update the properties with dimension and measure. My question is, is it necessary to work with a "Generated ID" or the other query options that you used in your previous blog?

      In what specific cases we should adapt this SADL based model?