Example: Custom CDS View using predefined Virtual Data Model and Extend View in S/4HANA

[Masaaki]

Purpose of this blog:

This blog intends to encourage you to enjoy high productivity of custom object development by using predefined Virtual Data Model (VDM) in S/4HANA by providing the sample of Custom CDS Views based on predefined VDM enhanced with Extend View. By using predefined VDM, you may not have to create custom CDS Views for all entities. It is always the case that predefined content is totally reusable but it is necessary to add some fields, which is possible with Extend View.

The example Consumption View in this blog is Analytic Query supposed to be used in Fiori Multidimensional Reporting, SAC and BusinessObjects, but predefined VDM and custom CDS View can be used for OData based Fiori app, ABAP program, data source to BW, etc.

But before considering Extend View, please check if the fields can be extended with Custom Fields and Logic, which could add fields with graphical Fiori app.

The source code in this blog works on S/4HANA 1709.

2020/9/19 added:

It would take long time, e.g. 2 hours, to activate Extend VIew for the CDS View which is used in many other view. In the case, firstly, it is expected to check if extension would be possible with Custom Fields and Logic as explained later. Even if it is not possible, then it might be better to create another new custom CDS view to add the fields. e.g. I have added a custom field in EKKO (Purchase doc header table) and want to add it in the custom CDS view using I_PurchaseOrder as a source. In the case, instead of creating Extend View for I_PurchaseOrder, Join EKKO again in the Custom CDS VIew. In the case, the activation time for the custom CDS view should be much shorter, and in the case, the join of EKKO would not happen twice as internally select statement is optimized. (Self-join-optimization). (Use the same definition for @ClientHandling.algorithm to make it work.).

This blog should help how to deal with long activation time.

2020/9/27

Use metadata data extension for the VDM which includes @Metadata.allowExtensions:true to change UI, Consumption, EndUserText annotations.

2021/2/6

How to activate Extend View in Background

1. Choose System → Services  → Table maintenance → Extended table maintenance.
   The initial screen of the Extended Table Maintenance appears.


2. Enter table name TACOBand choose Maintain.
   Specify the object to be activated in the next screen with the object type and object name.


3. Enter the object types and object names.
   
   
   
   • There is an input helpfor the Type field that displays the possible object types.
   
   
   
   


4. Save your entries and exit the Extended table maintenance.
   With the mass activation program, now schedule a background job for activating the entries in table TACOB (call report RADMASG0).

See help page: https://help.sap.com/viewer/12a2d87e6c531014bec0e63ea0208c21/7.0.37/en-US/cf21f232446011d189700000e8...

There is also a good blog about that: https://blogs.sap.com/2020/10/26/mass-activating-multiple-objects-in-background/

2023/5/16

By adding the following annotation, activation time can be shortened as checks are skipped, but it is not officially supported. For test purpose, it could be used.

 

My motivation to write the blog

Predefined VDM is one of the key in S/4HANA arechitecture, although it is not emphasized as is the object underneath, but in my understanding, not so many customers enjoy it. I think one of the main reason should be that how to use Predefined VDM might not be clear for them, and some would think it be useless as they have custom fields in the table or want to add the field from other table but it would be impossible to add field to Predefined VDM. To overcome those concerns, I provide the example code to use Predefined VDM including Extend View with which fields can be added to standard Predefined VDM.

 

Target audience:

This blog is for the ones who want to create report and analytical apps. It is not only for analytics experts but for all functional experts as it is expected to create report based on CDS View, instead of traditional functions like ALV or Report Painter, which has been used by functional experts. See SAP Note 2579584 and 2349297.

It is also for developers as CDS View can be used not only for analytical apps, but it can be used in normal ABAP program.

 

Summary:

• Concrete samples of the predefined VDMs are explained to understand what exactly the predefined VDM is.


• Samples of custom CDS View using predefined VDMs and Extend View to add fields to predefined VDM are provided, as the best way to understand is to see the source. The predefined VDM of Billing Document Item is associated to Sales Order Item in the sample.
  
  
  
  • In the sample Extend View, a CDS Wiew is associated, and a field is added from the view.
  
  
  • Considerations to use Extend View and limitations are included.
  
  
  • In the sample of Consumption View, some advanced settings are included (hoping it would help creating sophisticated Analytic Query, although it is not the main topic of this blog).
    
    
    
    • Add value help to a parameter using association.
    
    
    • Set start/end date as consumption filtering value automatically using lookup entry used in Date Function.
    
    
    • Exception aggregations are used to avoid value duplication when joining sales order and billing document.
    
    
    
    
  
  
  
  


• How to deal with “Not released” predefined VDM is argued. “Not released” VDM is subject to change after version up of the system. But in my personal view, it should be better to use it than creating custom CDS Views for all business entities.

 

Predefined VDM and Extend View:

Predefined VDM:

Predefined VDM is the predefined CDS Views for business entities or for applications in S/4HANA. There are mainly 3 types of predefined VDM in S/4HANA.

• Consumption View: The view consumed by application (Tech name: C_*). e.g. Journal Entry Analyzer (C_GLLINEITEMSQ0001). It is created from Interface View in general.


• Interface View: The view is the foundation of Consumption View (Tech name: I_*). Interface View for business entities in S/4HANA is called Basic View, e.g. GL Account Line Item (I_GLAccountLineItem), Profit Center (I_ProfitCenter). Interface view created from Basic View is called Composite View, e.g. G/L Account Balance Cube (I_GLAcctBalanceCube).


• Private View: The view used as a part to create Interface View or Consumption View (Tech name: P_*). It is not mandatory to create.

See the document linked in this blog in detail about CDS View and VDM.

(In BW context, Consumption View is like BW Query and Interface View is like InfoProvider or InfoObject.)

You can find the predefined VDMs in S/4HANA with Fiori View Browser.

Example of predefined VDM:

• Transaction Data View: Sales Order
  
  
  
  • Consumption View: C_SalesOrderItemQry （Analytic Query – executable as BW Query）
  
  
  • Interface View：I_SalesOrderItemCube
  
  
  • Interface View: I_SalesDocumentItemAnalytics
  
  
  • Interface View: I_SalesDocumentItem (Use table VBAK, VBAP, VBKD, VEDA as sources)
  
  
  
  


• Master Data View:
  
  
  
  • Interface View (Simple): I_CompanyCode
  
  
  • Interface View (Complex): I_ProfitCenter
  
  
  
  

 

List of main predefined View:

Component	Status	CDS View	Text
SD	Released	I_SALESORDERITEMCUBE	Analytics - Incoming Sales Order Cube
SD	Released	I_BILLINGDOCUMENTITEMCUBE	Analytics - Sales Volume Cube
MM	Unreleased	I_PURCHASEORDERITEM	Purchase Order Item
MM	Unreleased	C_PURDOCLISTINVOICEDETAILS	Invoice Details
MM	Unreleased	I_PURCHASEREQUISITIONITEM	Purchase Requisition Item
MM	Released	I_MATERIALSTOCKTIMESERIES	Material stock for periods
MM	Released	I_GOODSMOVEMENTCUBE	Analytical Cube for Goods Movements
FI	Released	I_GLACCTBALANCECUBE	G/L Account Balance Cube
FI	Released	I_GLACCOUNTLINEITEMCUBE	G/L Line Items Cube
FI	Released	I_PROFITCENTER	Profit Center
FI	Released	I_COSTCENTER	Cost Center
FI	Released	I_GLACCOUNTINCHARTOFACCOUNTS	G/L Account In Chart Of Accounts
LO	Released	I_CUSTOMER	Customer
LO	Released	I_SUPPLIER	Supplier
MM	Released	I_PLANT	Plant
MM	Released	I_STORAGELOCATION	Storage Location
MM	Unreleased	I_MATERIAL	Material
BC	Released	I_CURRENCY	Currency
BC	Released	I_UNITOFMEASURE	Unit of Measure
CA	Unreleased	I_CALENDARDATE	Date
FI	Released	I_FISCALCALENDARDATE	Fiscal Calendar Date

• Release statuses is at S/4HANA 1709 FPS00.

 

Extend View:

Extend View is used to add fields to predefined VDM without changing predefined VDM itself. It is possible to associate the view and add it in the source view, and it is also possible to use the association for foreign key association on the added field. It is like APPEND for table in ABAP Dictionary. See SAP Help , Wiki or blog in detail.

 

Consideration to use Extend View:

Enhancement by Extend View is in a sense like modification, so would cause the following issues when upgrading the system.

• Conflict of the field name: conflict of the field name would happen if new fields are added on the predefined VDM in the newer version but the field names has already been used in the Extend View, e.g. Segment is added with Extend View to the source predefined VDM, but in the newer version of the predefined VDM, Segment is added by default. To avoid it, it is recommended to rename the fields added with Extend View with ZZ~.


• Field name change of the source view: fields in the source (select ~ from <Source>) can be added using Extend View. But if the source view is unreleased (see below), the field name might be changed (or the filed would be deleted) in the newer version. In this case, it cannot be avoided to adjust the Extend View and relevant CDS Views.

For those reasons, it is recommended to add fields using Key User Extensibility Tools “Custom Fields and Logic” to add fields as much as possible.

 

Other limitations in Extend View:

• [ min .. *] cannot be used in Extend View. See the blog for [min..*] association.


• The field of the association cannot be added when path expression is used in ON condition of the association, _FiscalYearCalenderDate.FiscalYear cannot be added when _FiscalYearCalenderDate is associated in the Extend View and SDIA._ControllingArea.FiscalYearVariant is used in the ON condition.


• It is not possible to set annotation only to existing field.
  
  
  
  • For doing that ANNOTATE VIEW is used. But it is available only for the predefined VDM in which Metadata.allowExtensions is set to true.
  
  
  
  

 

 

Example of Custom CDS View using Predefined VDM and Extend View

There are some ways to use predefined VDM as below.



In the example of this blog, Interface View of predefined VDM I_SalesOrderItemCube is enhanced by Extend View, and Another Custom CDS View is created from it, and Consumption View is created on top.

• The Interface View I_SalesOrderItemCube is enhanced by Extend View ZE_SalesOrderItemCube to add FiscalYearVariant from I_ControllingArea associated in I_SalesOrderItemCube.


• Custom Interface View is created on top of predefind VDM and I_ProfitCenter, I_BillingDocumentItemBasic and I_FiscalCalendarDate are associated in it, and fields and associations are added from those views.


• Consumption View ZC_Sales is created from I_SalesOrderItemCube.

 

Extend View: ZE_SalesOrderItemCube

@AbapCatalog.sqlViewAppendName: 'ZESSOITMCB'

@EndUserText.label: 'Extend SalesOrderItemCube'

extend view I_SalesOrderItemCube with ZE_SalesOrderItemCube

association [0..1] to I_FiscalYearVariant as _FiscalYearVariant

on  $projection.ZZFiscalYearVariant = _FiscalYearVariant.FiscalYearVariant

{

@ObjectModel.foreignKey.association: '_FiscalYearVariant'

SDIA._ControllingArea.FiscalYearVariant as ZZFiscalYearVariant,

--expose

_FiscalYearVariant

}

 

Detail:

• Add fields from associated Views: FiscalYearVariant is added from I_ControllingArea associated in the source I_SalesOrderItemCube, and master view I_FiscalYearVariant is associated and it is set as the foreign key association on the added field FiscalYearVariant.


• Renamed as ZZ~: All fields added in the Extend View are renamed to ZZ~. it is to avoid the possible conflict of the name when upgrading the system. Now I_SalesOrderItemCube doesn’t have the field FiscalYearVariant so it is possible to add the field named “FiscalYearVariant” with Extend View. However, if the field FiscalYearVariant is added in the next version of I_SalesOrderItemCube, the conflict would happen.


• Field and foreign key association added from associated View: The field Segment is added from I_ProfitCenter, and _Segment (_ProfitCenter. _ProfitCenter) is also added from I_ProfitCenter, and is used in the foreign key association on the field. Although I_Segment has already been used in the foreign key association in I_ProfitCenter, foreign key association setting is necessary again in the Extend View (Transaction Interface View).


• Not possible to use Path Expression for ON condition for the association: I tried to associate _FiscalCalendarDate.FiscalYear but found it is not possible. It is because _ControllingArea. FiscalYearVariant cannot be used for ON condition for association to add fields from the associated view, although it is possible to add association only, e.g. _FiscalCalendarDate.FiscalYear cannot be added, but _FiscalCalendarDate can be added. For doing that, the only way is to add only association in Extend View and add the field from the associated view in custom CDS View created from the source view extended by Extend View.

 

Interface View: ZI_SALESBILL:

@AbapCatalog.sqlViewName: 'ZISALESBILL'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'SalesOrder+BillingDoc'

@Analytics.dataCategory: #CUBE

@VDM.viewType: #COMPOSITE

@ClientHandling.algorithm: #SESSION_VARIABLE

define view ZI_SALESBILL

with parameters

P_ExchangeRateType : kurst,

P_DisplayCurrency  : vdm_v_display_currency

as select from I_SalesOrderItemCube

( P_ExchangeRateType: $parameters.P_ExchangeRateType, P_DisplayCurrency: $parameters.P_DisplayCurrency )

 

association [0..1] to I_ProfitCenter as _ProfitCenter

on  $projection.ControllingArea = _ProfitCenter.ControllingArea

and $projection.ProfitCenter = _ProfitCenter.ProfitCenter

 

association [0..*] to I_BillingDocumentItemBasic as _BillingDocumentItemBasic

on $projection.SalesOrder = _BillingDocumentItemBasic.SalesDocument

and $projection.SalesOrderItem = _BillingDocumentItemBasic.SalesDocumentItem

 

association [0..1] to I_FiscalCalendarDate as _FiscalCalendarDate

on $projection.ZZFiscalYearVariant = _FiscalCalendarDate.FiscalYearVariant

and $projection.SalesOrderDate = _FiscalCalendarDate.CalendarDate

 

{

SalesOrder,

SalesOrderItem,

SalesOrderType,

SalesOrderDate,

SalesOrganization,

DistributionChannel,

OrganizationDivision,

ControllingArea,

SalesOrderDateYearMonth,

DisplayCurrency,

@ObjectModel.foreignKey.association: '_ProfitCenter'

ProfitCenter,

ZZFiscalYearVariant,

NetAmountInDisplayCurrency,

 

@ObjectModel.foreignKey.association: '_Segment'

_ProfitCenter.Segment,

 

_BillingDocumentItemBasic.BillingDocument,

_BillingDocumentItemBasic.BillingDocumentItem,

 

_FiscalCalendarDate.FiscalYear as ZZFiscalYear,

_FiscalCalendarDate.FiscalPeriod as ZZFiscalPeriod,

_FiscalCalendarDate.FiscalYearQuarter,

 

/* Associations */

//I_SalesOrderItemCube

 

_ControllingArea,

_DistributionChannel,

_FiscalYearVariant,

_OrganizationDivision,

_SalesOrder,

_SalesOrderType,

_SalesOrganization,

_ScheduleLine,

 

_ProfitCenter,

_ProfitCenter._Segment

}

 

Detail:

• Add fields from associated Views: In this Interface View, I_ProfitCenter are associated and Segment are added from it, and this view is set as Foreign Key association on ProfitCenter. BillingDocument and BillingDocumentItem are added from associated I_BillingDocumentItemBasic, and FiscalYear, FiscalPeriod, and FiscalQuarter are added from associated I_FiscalCalendarDate.

Most of them could be added with Extend View and it might be possible to go without creating custom Interface View. However, it is created in this sample as it might be better to avoid using Extend View too much because of the considerations and limitations as mentioned above.

 

Consumption View: ZC_Sales

@AbapCatalog.sqlViewName: 'ZCSALES'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Sample Sales'

@Analytics.query: true

define view ZC_Sales

with parameters

@AnalyticsDetails.query.variableSequence: 20

@Consumption.defaultValue: 'EUR'

P_DisplayCurrency  : vdm_v_display_currency,

@AnalyticsDetails.query.variableSequence: 30

@Consumption: { valueHelp: '_ExchangeRateType.ExchangeRateType', defaultValue: 'M'}

P_ExchangeRateType : kurst,

@Consumption.hidden: true

@Environment.systemField: #SYSTEM_DATE

P_KeyDate          : sydate

as select from ZI_SALESBILL

( P_ExchangeRateType: $parameters.P_ExchangeRateType, P_DisplayCurrency: $parameters.P_DisplayCurrency )

association [1] to I_ExchangeRateType as _ExchangeRateType on _ExchangeRateType.ExchangeRateType <> ''

 

{

SalesOrder,

SalesOrderItem,

@AnalyticsDetails.query.totals: #SHOW

SalesOrderType,

@AnalyticsDetails.query.variableSequence : 10

@Consumption: {

filter:     {selectionType:     #INTERVAL},

derivation: {

lookupEntity: 'C_SglGregorianCalDateFunction',

resultElement: 'DateFunctionStartDate', binding: [

{ targetParameter: 'P_DateFunction', type: #CONSTANT, value : 'YEARTODATE' },

{ targetParameter : 'P_Language' , type : #SYSTEM_FIELD, value : '#SYSTEM_LANGUAGE'  } ],

resultElementHigh: 'DateFunctionEndDate' }

}

SalesOrderDate,

 

@AnalyticsDetails.query.axis: #ROWS

@AnalyticsDetails.query.display: #TEXT

@AnalyticsDetails.query.totals: #SHOW

SalesOrganization,

@AnalyticsDetails.query.display: #TEXT

@AnalyticsDetails.query.totals: #SHOW

DistributionChannel,

@AnalyticsDetails.query.display: #TEXT

@AnalyticsDetails.query.totals: #SHOW

OrganizationDivision,

@AnalyticsDetails.query.display: #TEXT

@AnalyticsDetails.query.totals: #SHOW

ControllingArea,

@EndUserText.label: 'Calender YearMonth'

@AnalyticsDetails.query.axis: #COLUMNS

@AnalyticsDetails.query.totals: #SHOW

SalesOrderDateYearMonth,

DisplayCurrency,

ZZFiscalYearVariant,

 

BillingDocument,

@EndUserText.label: 'Billing Document Item'

BillingDocumentItem,

@AnalyticsDetails.query.axis: #ROWS

@AnalyticsDetails.query.display: #TEXT

@AnalyticsDetails.query.totals: #SHOW

ProfitCenter,

@AnalyticsDetails.query.display: #TEXT

@AnalyticsDetails.query.totals: #SHOW

Segment,

ZZFiscalYear,

ZZFiscalPeriod,

FiscalYearQuarter,

 

@Consumption.hidden: true

@DefaultAggregation: #FORMULA

@AnalyticsDetails: {

exceptionAggregationSteps: [{ exceptionAggregationBehavior : #MAX,

exceptionAggregationElements: ['BillingDocument','BillingDocumentItem']

}]

}

@AnalyticsDetails.query.formula : 'NetAmountInDisplayCurrency'

1 as SalesOrderAmtTmp,

 

@AnalyticsDetails.query.axis: #COLUMNS

@EndUserText.label: 'SalesOdrAmt_D'

@DefaultAggregation: #FORMULA

@AnalyticsDetails: {

exceptionAggregationSteps: [{ exceptionAggregationBehavior : #SUM,

exceptionAggregationElements: ['SalesOrder','SalesOrderItem']

}],query.decimals: 0

}

@AnalyticsDetails.query.formula : '$projection.SalesOrderAmtTmp'

1 as SalesOrderAmt

 

}

 

Results of the Consumption View:

Detail:

• Analytic Query: This Consumption View is Analytic Query, which works as the source of Fiori Multidimensional Reporting. It can be used with SAC and BusinessObjects.


• Value help for the parameter: I_ExchangeRateType is associated practically without on condition (dummy association) to use it for the value help for the parameter P_ExchangeRateType. See the blog in detail.


• Derive start and end date automatically using lookup entry for Date Function: The start and end date are derived automatically from C_SglGregorianCalDateFunction as Lookup entry. C_SglGregorianCalDateFunction is the CDS View used in Date Function. As YEARTODATE is set as Date Function parameter Value, when running the view on 8/19/2018, 1/1/2018 is derived as start date and 8/19/2018 as end date.


• Exception Aggregation to avoid value duplicate value: The record of I_SalesOrderItemCube would be duplicated when JOIN happens as relationship between I_SalesOrderItemCube and I_BillingDocumentItemBasic is 1 : N. To avoid it, 2 calculated measures including Exception Aggregation are created (SalesOrderAmtTmp and SalesOrderAmt). In SalesOrderAmtTmp, ZBillingDocument and ZBillingDocumentItem are used as Exception Aggregation Element, and Aggregation is set to MAX, so the aggregation of ZBillingDocument and ZBillingDocumentItem is MAX, not SUM. In SalesOrderAmt, ZSalesOrder and ZSalesOrderItem are used as Exception Aggregation Element, and Aggregation is set to SUM, SalesOrderAmtTmp is aggregated with SUM. (For this reason, [0..*] in the association to I_BillingDocumentItemBasic in Interface View (Extend View) is no problem in this case).

When drilled down with Billing Document, SalesOdrAmt_D for Sales Order 388 is displayed twice.

Even when Billing Document is removed and records are aggregated, SalesOdrAmt_D for Sales Order 388 is displayed as expected without duplication.

As high granular attributes ZBillingDocument, ZBillingDocumentItem, and ZSalesOrder, ZSalesOrderItem are used in Exception Aggregations, they have to be included in the select list of internal SELECT statement. It would lead to longer runtime as larger amount of data would have to be retrieved.

 

“Not released” predefined VDM:

Predefined VDM has the status of validity (Released/Not Released). In the unreleased predefined VDM, the design is subject to change in the future release and current design is not guaranteed by SAP, e.g. field name and the keys would be changed, fields would be removed, the view would be removed. It is like unreleased Function Module. In addition, Only the released CDS Views can be used for Key User Extensibility Tools Custom CDS View and Custom Analytical Queries.

See the blog in detail.

 

Hope you enjoy high productivity by using predefined VDM and Extend View!