Fiori Analytical List Page using CDS Annotations to Display as Charts

[Arun_21]

Goal

Create an Analytical List Page like the one below.

Development environment

• Frontend:  BTP
• Backend: S/4HANA

Data model

The data model has a simple structure as shown below. The base has a table for recording expenditures (ZMY_EXPENSES) and an expenditure category master (ZEXCATEGORYTEXT). The views used in ALP are the same analytical views used in the query browser. The top view @analytics.query: trueis annotated with , and the base view below it @analytics.dataCategory: #CUBEis annotated with (or FACT, DIMENSION, HIERARCHY depending on the purpose).

ZMY_EXPENSES

The data is in the following format.

ZEXCATEGORYTEXT

For simplicity, we will only have Five Categories.

Steps

1. Creating a CDS view
2. Output charts and tables

1. Creating a CDS view

Expense Category View: ZI_Category_Text

Create a view to retrieve expenditure categories.

@AbapCatalog.sqlViewName: 'ZITEXTCAT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Text Category'


@ObjectModel.dataCategory: #TEXT
@ObjectModel.representativeKey: 'category'
define view ZI_Category_Text as select from zexcategorytext {
    key category,
    @Semantics.language: true
    key language,
    @Semantics.text: true
    text
}

Cube View for aggregation: ZI_Cube_MyExpenses

Create a Cube View for aggregation. I_CalendarDate is a standard view that allows you to convert a date to year, year month, etc. I want to be able to select the year and month with the ALP filter, so I have I_CalendarDate and Association.

ZI_Cube_MyExpenses

@AbapCatalog.sqlViewName: 'ZIMYEXP'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Cube for My Expenses'
@Analytics.dataCategory: #CUBE

define view ZI_Cube_MyExpenses as select from zmy_expenses 
association [0..1] to ZI_Category_Text as _Text
on $projection.Cateogry = _Text.category
association [0..1] to I_CalendarDate as _CalendarDate
on $projection.PostingDate = _CalendarDate.CalendarDate
{
    @ObjectModel.foreignKey.association: '_CalendarDate'
    posting_date as PostingDate,
    @ObjectModel.text.association: '_Text'
    category     as Cateogry,
    @Semantics.amount.currencyCode: 'Currency'
    @DefaultAggregation: #SUM
    amount       as Amount,
    @Semantics.currencyCode: true
    currency     as Currency,
    _CalendarDate._CalendarYear.CalendarYear,
    _CalendarDate._YearMonth.YearMonth,


    _Text,
    _CalendarDate
}

The annotations required for use with ALP are as follows.

@Analytics.dataCategorySet to make it a view for analysis . Category is a combined view of transaction data (expenditures) and master data (expenditure categories) #CUBE.

@Analytics.dataCategory: #CUBE

The analysis view must have items to be aggregated (measures). @DefaultAggregationItems marked with are subject to aggregation.

@DefaultAggregation: #SUM    amount       as Amount,

Consumption View：ZC_MY_Expenses

Finally, create the top view that will be the source of the OData.

ZC_MY_Expenses

@AbapCatalog.sqlViewName: 'ZCMYEXP'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'My Expenses'

@Analytics.query: true
@OData.publish: true
@Metadata.allowExtensions: true

define view ZC_MY_Expenses as select from ZI_Cube_MyExpenses 
{
    PostingDate,
    @AnalyticsDetails.query.display: #KEY_TEXT
    Cateogry,
    Amount,
    Currency,
    @Semantics.calendar.year: true
    CalendarYear,
    @Semantics.calendar.yearMonth: true
    YearMonth
}

The important thing here @Analytics.query: true is the annotation. This view does not have a key, but it @Analytics.query: true is acceptable without a key.

Another important point is the following annotation.

    @Semantics.calendar.year: true

    CalendarYear,

    @Semantics.calendar.yearMonth: true

    YearMonth

The OData type for CalendarYear (year) and YearMonth (year and month) is edm.string. If left as is, it will not be recognized as a time-related item, so @Semantics.calendarit is necessary to add .

2. Output charts and tables

Next, let's create an app by adding the minimum annotations to output charts and tables.
Create a Metadata Extension for ZC_MY_Expenses. First, define the selection field (selectionField) and the table column item (lineItem).

@Metadata.layer: #CORE

annotate view ZC_MY_Expenses

    with

{

    @UI:{

        lineItem: [{ position: 10 }]

    }

    PostingDate;

    @UI:{

        selectionField: [{position: 10 }],

        lineItem: [{ position: 20 }]

    }   

    Cateogry;

    @UI:{

        lineItem: [{ position: 30 }]

    }   

    Amount;

    @UI:{

        selectionField: [{position: 20 }]

    } 

    CalendarYear;

    @UI:{

        selectionField: [{position: 30 }]

    }    

    YearMonth;  

}

Next, set the annotation to display the chart. Add the following above annotate view ZC_MY_Expenses...

This is an annotation that determines the display variant (presentationVariant) and selection variant (selectionVariant) when the app is opened. A qualifier (ID) of 'Default' is specified for each.

@UI.selectionPresentationVariant: [{

    qualifier: 'Default',

    presentationVariantQualifier: 'Default',

    selectionVariantQualifier: 'Default'   

 }]

The contents of the actual variant are defined below.

presentationVariant indicates how the data is presented, that is, it is displayed as a chart (type:

@UI.presentationVariant: [{

    qualifier: 'Default',

    visualizations: [{

        type: #AS_CHART,

        qualifier: 'ChartDefault'

    }]

 }]

@UI.selectionVariant: [{

    qualifier: 'Default',

    text: 'Default'

 }]

Finally, let's define the chart. Create a stacked bar graph for each year and month and expense category.

@UI.chart: [{

    qualifier: 'ChartDefault',

    chartType: #COLUMN_STACKED,

    dimensions: ['Cateogry', 'YearMonth'],

    measures: ['Amount'],

    dimensionAttributes: [{

        dimension: 'Cateogry',

        role: #SERIES

     },{

        dimension: 'YearMonth',

        role: #CATEGORY

     }],

    measureAttributes: [{

        measure: 'Amount',

        role: #AXIS_1

     }]

  }]

The meanings of the items are as follows.

project	explanation
qualifier	Chart ID. Same as qualifier specified in presentationVariant
chartType	Chart type. #COLUM_STACKED is a stacked bar graph
dimensions	Specify the analysis axis. Multiple specifications can be specified depending on the chart type.
measures	Specify the numeric item to be analyzed. Multiple specifications can be specified depending on the chart type.
dimensionAttributes	Specify the role (use) for each analysis axis (*)
measureAttributes	Specify the role (purpose) for each numerical item. Specify which axis

*Meaning of role in dimension

• category: Set for the dimension that is the X axis in a bar graph
• series: Use when you want to separate classifications (colors) within the same category
• category2: Second axis used in heatmap etc.

Creating  an ALP with BTP(SAP Business Technology Platform)

Let's make OData public. TR - > ( /IWFND/MAINT_SERVICE )

Select the Analytical List Page template.

Provide the OData Service URL

Specify the qualifier of the selection Presentation Variant set in step 2 in Qualifier.

Next we have to provide the Project Attributes

On the Project2 we have to choose Preview Application here we have to Click on the Start Fiori Run First option in Dropdown.

At this point, the pages section of manifest.json looks like this:-

manifest.json

        "pages": {

            "AnalyticalListPage|ZC_MYEXPENSES": {

                "entitySet": "ZC_MYEXPENSES",

                "component": {

                    "name": "sap.suite.ui.generic.template.AnalyticalListPage",

                    "list": true,

                    "settings": {

                        "condensedTableLayout": true,

                        "showGoButtonOnFilterBar": true,

                        "tableType": "ResponsiveTable",

                        "multiSelect": false,

                        "qualifier": "Default",

                        "autoHide": true,

                        "smartVariantManagement": true,

                        "keyPerformanceIndicators": {}

                    }

                },

                "pages": {

                    "ObjectPage|ZC_MYEXPENSES": {

                        "entitySet": "ZC_MYEXPENSES",

                        "component": {

                            "name": "sap.suite.ui.generic.template.ObjectPage"

                        }

                    }

                }

            }

        }

Execution result

Nothing is displayed when it starts up.

When you press Go, the chart and table are displayed.

 When you press the Compact Filter button on the top right, the filter items defined in selection Field will be displayed.

Arrange graphs in year/month order

Add sort Order to presentation Variant. Arrange them in ascending order by Year Month and descending order by Posting Date. The reason why I include Posting Date is because this sort order also affects how the table looks, so I want the latest records to be at the First.

@UI.presentationVariant: [{

    qualifier: 'Default',

    sortOrder: [{

        by: 'PostingDate',

        direction: #DESC

     },{

        by: 'YearMonth',

        direction: #ASC

     }],   

    visualizations: [{

        type: #AS_CHART,

        qualifier: 'ChartDefault'

    }]

 }]

Also add YearMonth to line Item. This is because an error will occur if the item specified in sort Order is not included in line Item.

    @UI:{

        selectionField: [{position: 30 }],

        lineItem: [ { position: 40 } ]

    }    

    YearMonth;   

Conclusion.

My impressions after creating ALP for the first time were that it was relatively easy to do in general, but I had a hard time with the small details (default filter, criticality settings, etc.). This time, I envisioned what I wanted to create and researched how to make it happen, so it was good that I realized what ALP can do and its limitations.

Reference

blog

• Create an Analytical List Page using ABAP CDS views and annotations
• Create an analytical model based on ABAP CDS views
• Configuring filters area of an Analytical List Page (ALP) application

Document

• Analytical List Page
• Chart Cards Used in Overview Pages

Appendix

Metadata Extension:-

@Metadata.layer: #CORE
@UI.presentationVariant: [{
    qualifier: 'Default',
    sortOrder: [{
        by: 'PostingDate',
        direction: #DESC
     },{
        by: 'YearMonth',
        direction: #ASC
     }],
    visualizations: [{
        type: #AS_CHART,
        qualifier: 'ChartDefault'
    }]
 }]
 
@UI.chart: [{
   qualifier: 'ChartDefault',
   chartType: #COLUMN_STACKED,
   dimensions: ['Cateogry', 'YearMonth'],
   measures: ['Amount'],
   dimensionAttributes: [{
       dimension: 'Cateogry',
       role: #SERIES
    },{
       dimension: 'YearMonth',
       role: #CATEGORY
    }],
   measureAttributes: [{
       measure: 'Amount',
       role: #AXIS_1
    }]
 }]
annotate view ZC_MY_Expenses with 
{
   @UI:{
      lineItem: [{ position: 10 }]
  }
  PostingDate;
  @UI:{
      selectionField: [{position: 10 }],
      lineItem: [{ position: 20 }]
  }
  Cateogry;
    @UI:{
      lineItem: [{ position: 30 }],
      dataPoint: {
          criticalityCalculation: {
              improvementDirection: #MINIMIZE,
              toleranceRangeHighValue: 100000,
              deviationRangeHighValue: 150000
          }
      }
  }
  Amount;
  @UI:{
      selectionField: [{position: 20 }]
  }
  CalendarYear;
  @UI:{
      selectionField: [{position: 30 }],
      lineItem: [ { position: 40 } ]
  }
  YearMonth;
    
}