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.
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
){
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';
};
Without these annotations, the analytical table for OData V4 will throw an error and will not display any records. The error occurs at the start of the application, before sending a $batch request.
On the other hand, the analytical table for OData V2 shows records, but does not display totals.
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"
}
}
}
}
}
},
When you execute the application, a $batch request is sent and a response is returned successfully, but totals is not displayed.
The response contains each record but not the totals.
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 that two requests are contained in a single $batch request. The total is displayed at the bottom of the table.
The first request retrieves the sum of the stock.
The second request retrieves each record.
Closing
While the analytical table for OData V2 retrieves totals and records in a $batch request, the analytical table for OData V4 does not seem to attempt to retrieve totals. This leads me to doubt whether the analytical table for OData V4 actually supports totals. If you have information on how to display totals in the analytical table for OData V4, please share it in the comment section below.