Skip to Content
Technical Articles
Author's profile photo Mio Yasutake

Fiori Elements Analytical Table with CAP: Does it work with V2 and V4?

Introduction

In this blog post, I will show you how to create an analytical table using a CAP OData service. Please note that this blog post is focused on List Report, and not Analytical List Report (ALP).

Recently, I was tasked with displaying an analytical table based on an OData V4 service built with CAP. While I was able to display the data on the table using some annotations, I encountered an issue with displaying totals at the bottom. Unfortunately, this issue is still unresolved.

Nevertheless, I would like to share what I have accomplished so far and would appreciate any advice on how to address this issue.

Update on June 2023 – With the help of the OSS support team, I figured out how to display the total.

Analytical table with OData V4 (total is displayed)

Analytical table with OData V2 (total is displayed)

 

CAP Project

Below is the CAP project with minimum data models and annotations for showing an analytical table. The code is available at GitHub.

Data model

The data model is created with `cap add samples` command and an entity `BooksAggregate` was created on top of it. This entity will be used for displaying analytical tables.

namespace my.bookshop;

entity Books {
  key ID : Integer @title: 'ID';
  title  : String @title: 'Title';
  category: String @title: 'Category';
  stock  : Integer @title: 'Stock';
}

entity BooksAggregate as projection on Books {
  ID,
  title,
  category,
  stock
}

Service

The service simply exposes the two entities in read-only mode.

using my.bookshop as my from '../db/data-model';

service CatalogService {
    @readonly entity Books as projection on my.Books;
    @readonly entity BooksAggregate as projection on my.BooksAggregate;
}

Annotations

The following annotations were added to make the analytical table work. The first block is for OData V4, and the second is for OData V2.

using CatalogService from './cat-service';

//aggregation annotations
// v4
annotate CatalogService.BooksAggregate with @(
  Aggregation.ApplySupported: {
    GroupableProperties: [
      ID,
      title,
      category
    ]
  },
  Aggregation.CustomAggregate #stock: 'Edm.Int32'
){
  stock @Analytics.Measure @Aggregation.default: #SUM
}

// v2
annotate CatalogService.BooksAggregate with @(
  sap.semantics: 'aggregate'
){
  ID @sap.aggregation.role: 'dimension';
  category @sap.aggregation.role: 'dimension';
  title @sap.aggregation.role: 'dimension';
  stock @sap.aggregation.role: 'measure';

};

 

List Report (V4)

A List report was created on OData V4 service with the table type set to `AnalyticalTable`.

      "targets": {
        "BooksAggregateList": {
          "type": "Component",
          "id": "BooksAggregateList",
          "name": "sap.fe.templates.ListReport",
          "options": {
            "settings": {
              "entitySet": "BooksAggregate",
             ...
              "controlConfiguration" : {
                "@com.sap.vocabularies.UI.v1.LineItem" : {
                     "tableSettings": {
                          "type": "AnalyticalTable"
                      }
                }
           }
            }
          }
        },

With above settings, an analytical table will be displayed but without the total.

Analytical Table without the total

However, if you click on the header of the “Stock” column, you will find the “Totals For: Stock” button. This is a good indication that the aggregate feature is working properly.

Totals%20for%20button

Totals for button

To display the total by default, the final step is to add the UI.PresentationVariant annotations:

annotate CatalogService.BooksAggregate with@(
    UI: {
          PresentationVariant: {
            Total: [
              stock
            ],
            Visualizations: [
              '@UI.LineItem'
            ]
          },

Finally, the analytical table is displayed with the total at the bottom.

Analytical%20Table%20with%20the%20total

Analytical Table (V4) with the total

List Report (V2)

Next, another List report was created on OData V2 service with the table type set to `AnalyticalTable`.

    "pages": {
      "ListReport|BooksAggregate": {
        "entitySet": "BooksAggregate",
        "component": {
          "name": "sap.suite.ui.generic.template.ListReport",
          "list": true,
          "settings": {
            ...
            "tableSettings": {
							"type": "AnalyticalTable"
						},
            "dataLoadSettings": {
              "loadDataOnAppLaunch": "always"
            }
          }
        },

When you execute the application, you will see the total is displayed at the bottom.

Analytical%20Table%20with%20the%20total

Analytical Table (V2) with the total

 

Closing

In order to display the total for an analytical table served by OData V4, the important annotation is UI.PresentationVariant. However, it is not yet documented in SAPUI5 SDK. I hope this post will be helpful to anyone looking to achieve similar functionalities as the analytical tables in OData V2 using OData V4.

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Park Woongki
      Park Woongki

      Really good

      Author's profile photo Gonzalo Meana Balseiro
      Gonzalo Meana Balseiro

      Amazing post !!

      For everyone who is trying this blog with CAP using the Node.js version instead of the Java version will possibly experience errors when doing groupings. This will fail because 'concat' is not available in the Node.js version.

      image.png

      GET BooksAggregate?$apply=concat(groupby((ID,category,title))/aggregate($count%20as%20UI5__leaves),groupby((category))/concat(aggregate($count%20as%20UI5__count), top(72))) HTTP/1.1.

      You can check it in the documentation, there is also an experimental feature that enabling it in the package.json of your application will make it work correctly

      Documentation

      ... 
      "i18n": {
            "folders": [
              "i18n"
            ]
          },
          "features": {
            "odata_new_parser": true
          }
        },...

       

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      I encountered the same issue, but forgot to mention it in my blog post. Thanks for bringing it up!

      Author's profile photo Monika Juyal
      Monika Juyal

      Hi Mio Yasutake,

      I am working on the same requirement analytical table using a CAP OData V4 service.

      Does @Aggregation.default: #FORMULA work for odata v4?
      I am trying to apply formula for total AmountPerUnit(Amount/Quantity) feature shown below:

        Quantity Amount AmountPerUnit
        100 10000 100
        200 15000 75
      Total 300 25000 83.33333333

       

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Monika Juyal,

      I tried to annotate my service with: stock @Analytics.Measure @Aggregation.default: #FORMULA

      Then I got the following error.

      [cds] - [Error: SQLITE_ERROR: no such function: formula in: 
      SELECT formula ( stock ) AS "stock" FROM CatalogService_BooksAggregate ALIAS_1 LIMIT 1000] {
        errno: 1,
        code: 'SQLITE_ERROR',
        query: 'SELECT formula ( stock ) AS "stock" FROM CatalogService_BooksAggregate ALIAS_1 LIMIT 1000',
        values: [],
        id: '1140312',
        level: 'ERROR',
        timestamp: 1694723819052
      }

      It looks that CAP interprets aggregation default value to corresponding SQL function, and since there is no such function called 'formula', the error is occurring.

      Regards,

      Mio