Skip to Content

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 😉

28 Comments
You must be Logged on to comment or reply to a post.
  • 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

    • 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

      • 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!

        • 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

  • 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

      • 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

        /
        • 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

  • 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

    /
  • 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

      • 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 !

        • 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.

          • 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 !

          • 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.

             

          • 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.

          • 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.

            /
      • 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.