Building an Analytical List Page with CAP OData V4

[MioYasutake]

Introduction

In this blog post, I am going to demonstrate how to annotate CAP service to enable the display of an Analytical List Page (ALP). I discovered this solution in deepak_singh99's GitHub repository (currently not available), so I want to give credit to him.

Scenario

To build a very basic ALP app on top of CAP OData V4 service as shown below. The code is available at GitHub.

Analytical List Page

Steps

1. Create a data model
2. Add annotations
3. Create ALP 

1. Create a data model

I have created a very basic data model based on a Books entity generated by `cds add samples` command.

namespace my.bookshop;

entity Books {
  key ID : Integer @title: 'ID';
  title  : String @title: 'Title';
  stock  : Integer @title: 'Stock';
  category1: String @title: 'Category1';
  category2: String @title: 'Category2';
  publishedAt: Date @title: 'Published At';
}

The data model is exposed to the service as is.

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

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

 

2. Add annotations

I will explain the annotations added by dividing them into four blocks for clarity.
The first block focuses on enabling aggregate functions. These annotations are crucial for Fiori tools to properly recognize and support the Analytical List Page (ALP). Without these annotations, Fiori tools may raise error indicating the absence of a suitable entity for ALP.

annotate CatalogService.BooksAnalytics with @(

  Aggregation.ApplySupported: {
    Transformations: [
      'aggregate',
      'topcount',
      'bottomcount',
      'identity',
      'concat',
      'groupby',
      'filter',
      'expand',
      'search'      
    ],

    GroupableProperties: [
      ID,
      category1,
      category2,
      title,
      publishedAt
    ],

    AggregatableProperties: [{
      $Type : 'Aggregation.AggregatablePropertyType',
      Property: stock
    }]
  },

  Analytics.AggregatedProperty #totalStock: {
    $Type : 'Analytics.AggregatedPropertyType',
    AggregatableProperty : stock,
    AggregationMethod : 'sum',
    Name : 'totalStock',
    ![@Common.Label]: 'Total stock'
  },
);

The second block is for displaying a chart in the middle of the ALP. One point that was new to me was the use of DynamicMeasure. With OData V2, you may be familiar with "normal" Measure annotations. However, in the context of OData V4, normal Measure somehow does not work, so you need to use DynamicMeasure, which references the @analytics.AggregatedProparety defined in the first block.

annotate CatalogService.BooksAnalytics with @(
  UI.Chart: {
    $Type : 'UI.ChartDefinitionType',
    Title: 'Stock',
    ChartType : #Column,
    Dimensions: [
      category1,
      category2
    ],
    DimensionAttributes: [{
      $Type : 'UI.ChartDimensionAttributeType',
      Dimension: category1,
      Role: #Category
    },{
      $Type : 'UI.ChartDimensionAttributeType',
      Dimension: category2,
      Role: #Category2
    }],
    DynamicMeasures: [
      ![@Analytics.AggregatedProperty#totalStock]
    ],
    MeasureAttributes: [{
      $Type: 'UI.ChartMeasureAttributeType',
      DynamicMeasure: ![@Analytics.AggregatedProperty#totalStock],
      Role: #Axis1
    }]
  },
  UI.PresentationVariant: {
    $Type : 'UI.PresentationVariantType',
    Visualizations : [
        '@UI.Chart',
    ],
  }
);

The third block focuses on displaying visual filters. To achieve this, you will need three annotation blocks: chart, presentation variant and value list. The chart annotation block is similar to the one in the second block. However, please note that OData V4 currently only supports bar and line chart types for visual filters, whereas OData V2 also supports donut charts.

* You can also add these annotations with the guided development feature of Fiori tools, which will be much easier.

annotate CatalogService.BooksAnalytics with @(
  UI.Chart #category1: {
    $Type : 'UI.ChartDefinitionType',
    ChartType: #Bar,
    Dimensions: [
      category1
    ],
    DynamicMeasures: [
      ![@Analytics.AggregatedProperty#totalStock]
    ]
  },
  UI.PresentationVariant #prevCategory1: {
    $Type : 'UI.PresentationVariantType',
    Visualizations : [
        '@UI.Chart#category1',
    ],
  }
){
  category1 @Common.ValueList #vlCategory1: {
    $Type : 'Common.ValueListType',
    CollectionPath : 'BooksAnalytics',
    Parameters: [{
      $Type : 'Common.ValueListParameterInOut',
      ValueListProperty : 'category1',
      LocalDataProperty: category1
    }],
    PresentationVariantQualifier: 'prevCategory1'
  }
}

The fourth block consists of SelectionFields and LineItem annotations that you may already be familiar with.

annotate CatalogService.BooksAnalytics with@(
    UI: {
        SelectionFields  : [
            category1,
            category2,
            publishedAt
        ],
        LineItem: [
            {  $Type : 'UI.DataField', Value : ID, },
            {  $Type : 'UI.DataField', Value : title, },
            {  $Type : 'UI.DataField', Value : category1, },
            {  $Type : 'UI.DataField', Value : category2, },
            {  $Type : 'UI.DataField', Value : stock, },
            {  $Type : 'UI.DataField', Value : publishedAt, },
        ],
    }
);

3. Create ALP

Let's proceed with creating an ALP with using the template.

Floorplan selection

Entity Selection

To configure ALP, you need to make some settings in the "options"."settings" seciton of the manifest.json file. The "views" section is used to display the main chart and table. This section is included by default when generating the app using the "Analytical List Page" template. If you wish to use different annotations than the default ones (without qualifier), you need to make the necessary adjustment here.

The "@com.sap.vocabularies.UI.v1.SelectionFields" section in the "controlConfiguration" is used to show the visual filters. It's important to note that the filterFields names must match the property names of the entity used.

"options": {
                        "settings": {
                            "entitySet": "BooksAnalytics",
                            ...,
                            "controlConfiguration": {
                                "@com.sap.vocabularies.UI.v1.LineItem": {
                                    "tableSettings": {
                                        "type": "ResponsiveTable",
                                        "selectionMode": "None"
                                    }
                                },                                "@com.sap.vocabularies.UI.v1.SelectionFields": {
                                    "layout": "CompactVisual",
                                    "initialLayout": "Visual",
                                    "filterFields": {
                                        "category1": {
                                            "visualFilter": {
                                                "valueList": "com.sap.vocabularies.Common.v1.ValueList#vlCategory1"
                                            }
                                        },
                                        "category2": {
                                            "visualFilter": {
                                                "valueList": "com.sap.vocabularies.Common.v1.ValueList#vlCategory2"
                                            }
                                        },
                                        "publishedAt": {
                                            "visualFilter": {
                                                "valueList": "com.sap.vocabularies.Common.v1.ValueList#vlPublishedAt"
                                            }
                                        }
                                    }
                                }
                            },
                            "views": {
                                "paths": [
                                    {
                                        "primary": [{"annotationPath": "com.sap.vocabularies.UI.v1.PresentationVariant"}],
                                        "secondary": [{"annotationPath": "com.sap.vocabularies.UI.v1.LineItem"}],
                                        "defaultPath": "both"
                                    }
                                ]
                            }
                        }
                    }

A note about the table type

By default, the table type for the Analytical List Page is set to "AnalyticalTable". However, with this setting, measures are not displayed in the table. You can observe that "Stock" column in the below image is empty.

AnalyticalTable does not show measures

To address this issue, additional annotations and configurations are required, as suggested by @victor_souza.

In the first block of annotations, the following lines were added:

annotate CatalogService.BooksAnalytics with @(
  Aggregation.ApplySupported: {
    ...,
  Aggregation.CustomAggregate #stock: 'Edm.Int32'
){
  stock @Analytics.Measure @Aggregation.default: #SUM
};

In the second block, the UI.PresentationVariant annotation (without a qualifier) was modified as follows, and the "Total" property was added.

  UI.PresentationVariant: {
    $Type : 'UI.PresentationVariantType',
    Total: [
      stock
    ],
    Visualizations : [
        '@UI.Chart',
    ],
  }

Last but not least, in manifest.json, the secondary annotationPath was changed to 

"com.sap.vocabularies.UI.v1.PresentationVariant".

                            "views": {
                                "paths": [
                                    {
                                        "primary": [{ "annotationPath": "com.sap.vocabularies.UI.v1.PresentationVariant"}],
                                        "secondary": [{"annotationPath": "com.sap.vocabularies.UI.v1.PresentationVariant"}],
                                        "defaultPath": "both"
                                    }
                                ]
                            }

With the above settings, the analytical table properly displays measures and totals. (Unfortunately, now the height of the table is too low, accommodating only two rows, and I am wondering how to fix it.) 

Closing

By utilizing the mentioned settings and configurations, you can successfully create an Analytical List Page (ALP) on top of CAP OData V4 service. I hope this article has provided valuable insights into the analytical capabilities of CAP and OData V4. Thanks for reading!

References

• How to Annotate Your Service as an Analytical Service | SAPUI5 SDK

• Visual Filters | SAPUI5 SDK