Core Data Services (CDS) ABAP

Beispiele & Demos

(C) Brandeis Consulting

Daten erzeugen

Transaktionen und Reports

  • SNWD: Report SEPM_DG_EPM_STD_CHANNEL
(C) Brandeis Consulting

zcds_first_view

@AbapCatalog.sqlViewName: 'ZV_FIRST_VIEW'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'First CDS View'
define view zcds_first_view
  as

  select from       snwd_so_i as soi

    left outer join snwd_pd   as pd on soi.product_guid = pd.node_key
  // Blockmodus: Alt + Shift + A      <=>
  // Pretty Printer: Shift + F1
{
  key soi.node_key        as NodeKey,
      soi.parent_key      as ParentKey,
      soi.so_item_pos     as SoItemPos,
      soi.currency_code   as CurrencyCode,
      soi.gross_amount    as GrossAmount,
      soi.net_amount      as NetAmount,
      soi.tax_amount      as TaxAmount,
      pd.category ,
      3.1415 as pi
}
(C) Brandeis Consulting

Parameter

@AbapCatalog.sqlViewName: 'ZV_PARAMETERS'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Parameters in Views'
define view ZCDS_parameters
with parameters 
      vat : abap.int1,
      threshold_B : abap.int4,
      threshold_C : abap.int4
  as

  select from       snwd_so_i as soi

{
  key soi.node_key                  as NodeKey,
      soi.parent_key                as ParentKey,
      soi.so_item_pos               as SoItemPos,
      soi.currency_code             as CurrencyCode,
      soi.gross_amount              as GrossAmount,
      soi.net_amount                as NetAmount,
      soi.tax_amount                as TaxAmount,

      @Semantics.amount.currencyCode: 'CurrencyCode'
      net_amount * (1 + division($parameters.vat 
                                 , 100
                                 , 2 ) ) as NewVatGrossAmount,

      case when soi.net_amount < :threshold_C then   'C'
           when soi.net_amount < :threshold_B  then  'B'
           else                             'A'
           end                      as PriceCategory
}
(C) Brandeis Consulting

Ausdrücke in der Feldliste

@AbapCatalog.sqlViewName: 'ZV_EXPRESSIONS'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'First CDS View'
define view zcds_expressions
  as

  select from       snwd_so_i as soi

    left outer join snwd_pd   as pd on soi.product_guid = pd.node_key
  // Blockmodus: Alt + Shift + A      <=>
  // Pretty Printer: Shift + F1
{
  key soi.node_key                  as NodeKey,
      soi.parent_key                as ParentKey,
      soi.so_item_pos               as SoItemPos,
      soi.currency_code             as CurrencyCode,
      soi.gross_amount              as GrossAmount,
      soi.net_amount                as NetAmount,
      soi.tax_amount                as TaxAmount,
      pd.category,


      ltrim(soi.so_item_pos, '0')   as Position0,

      division(soi.tax_amount
              ,soi.net_amount
              ,2)   * 100           as tax,

      case when soi.net_amount < 100 then   'C'
           when soi.net_amount < 1000 then  'B'
           else                             'A'
           end                      as PriceCategory,

      @Semantics.quantity.unitOfMeasure: 'VolumeUnit'
      cast(pd.width as abap.dec( 12,2 ))
        * cast(pd.height as abap.dec( 12,2 )) * pd.depth                  as Volume,

      @Semantics.unitOfMeasure: true
      cast('MMQ' as abap.unit( 3) ) as VolumeUnit,

      TSTMP_TO_DATS(pd.created_at
                  , $session.user_timezone
                  , $session.client
                  , 'NULL')         as created_date,
                  
      case when pd.weight_measure < 1 
             or pd.weight_unit = 'g'   then 'A'
           when pd.weight_measure >= 1 
            and pd.weight_measure <= 5 then 'B'
           else 'C'
      end as WeightCategory
}
(C) Brandeis Consulting

Aggregation

@AbapCatalog.sqlViewName: 'ZV_AGGREGATION'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Aggregation Demo'
define view zcds_aggregation as select from snwd_pd {

    key category as Category,
    key currency_code as CurrencyCode,
    
    count(*) as ProductCount,
    
    @Semantics.amount.currencyCode: 'CurrencyCode'
    min(price) as MinPrice, 
    
    @Semantics.amount.currencyCode: 'CurrencyCode'
    max(price) as MaxPrice
} group by category, currency_code
(C) Brandeis Consulting

Textjoin - ON und WHERE Bedingung

@AbapCatalog.sqlViewName: 'ZV_TEXT_JOIN'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Text JOIN mit WHERE und ON'
define view zcds_text_join
  as

  select from       snwd_ad as ad
    left outer join t005t   as ct on  ad.country = ct.land1
                                  and ct.spras   = 'D'
{
  key ad.node_key                        as NodeKey,
      ad.city                            as City,
      ad.postal_code                     as PostalCode,
      ad.street                          as Street,
      ad.building                        as Building,
      ad.country                         as Country,
      coalesce(ct.landx50
             , concat_with_space('Text für Land'
                                 ,ad.country
                                 ,1 )  ) as CountryText
}
(C) Brandeis Consulting

Assoziationen SalesOrderItems

@AbapCatalog.sqlViewName: 'ZV_VDM_SO_I'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'SalesOrderItem'
define view zcds_vdm_so_i as 

select from snwd_so_i as soi

association [1..1] to zcds_vdm_so as _SalesOrder
on soi.parent_key = _SalesOrder.NodeKey

{
    key node_key as NodeKey,
    parent_key as ParentKey,
    so_item_pos as SoItemPos,
    product_guid as ProductGuid,
    note_guid as NoteGuid,
    currency_code as CurrencyCode,
    gross_amount as GrossAmount,
    net_amount as NetAmount,
    tax_amount as TaxAmount,
    
    //Associations
    _SalesOrder
}
(C) Brandeis Consulting

Assoziationen SalesOrder

@AbapCatalog.sqlViewName: 'ZV_VDM_SO'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'SalesOrder'
define view zcds_vdm_so as 

select from snwd_so as so

association [*] to zcds_vdm_so_i as _SalesOrderItem
on so.node_key = _SalesOrderItem.ParentKey

association [1..1] to zcds_vdm_bpa as _Buyer
on so.buyer_guid = _Buyer.NodeKey

{
    key node_key as NodeKey,
    so_id as SoId,
    created_by as CreatedBy,
    created_at as CreatedAt,
    changed_by as ChangedBy,
    changed_at as ChangedAt,
    created_by_bp as CreatedByBp,
    changed_by_bp as ChangedByBp,
    note_guid as NoteGuid,
    buyer_guid as BuyerGuid,
    currency_code as CurrencyCode,
    gross_amount as GrossAmount,
    net_amount as NetAmount,
    tax_amount as TaxAmount,

    buy_contact_guid as BuyContactGuid,
    ship_to_adr_guid as ShipToAdrGuid,
    bill_to_adr_guid as BillToAdrGuid,
    
    // Associations
    _SalesOrderItem,
    _Buyer
}
(C) Brandeis Consulting

Assoziationen BusinessPartner

@AbapCatalog.sqlViewName: 'ZV_VDM_BPA'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Business Partner'
define view zcds_vdm_bpa as select from snwd_bpa {
    key node_key as NodeKey,
    bp_role as BpRole,
    email_address as EmailAddress,
    phone_number as PhoneNumber,
    fax_number as FaxNumber,
    web_address as WebAddress,
    address_guid as AddressGuid,
    bp_id as BpId,
    company_name as CompanyName,
    legal_form as LegalForm,
    created_by as CreatedBy,
    created_at as CreatedAt,
    changed_by as ChangedBy,
    changed_at as ChangedAt,
    currency_code as CurrencyCode
}
(C) Brandeis Consulting

Assoziationen Consumption View mit Aggregation (1)

@AbapCatalog.sqlViewName: 'ZV_VDM_SO_C'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Consumption'
define view zcds_vdm_so_c as select from zcds_vdm_so {
    key NodeKey,
    SoId,
    CreatedBy,
    CreatedAt,
    ChangedBy,
    ChangedAt,
    CreatedByBp,
    ChangedByBp,
    NoteGuid,
    BuyerGuid,
    CurrencyCode,
    GrossAmount,
    NetAmount,
    TaxAmount,
    BuyContactGuid,
    ShipToAdrGuid,
    BillToAdrGuid,
    /* Associations */
    _Buyer.CompanyName ,
    sum(_SalesOrderItem.GrossAmount) as sumItemGrossAmount
(C) Brandeis Consulting

Assoziationen Consumption View mit Aggregation (2)

} group by 
   NodeKey,
    SoId,
    CreatedBy,
    CreatedAt,
    ChangedBy,
    ChangedAt,
    CreatedByBp,
    ChangedByBp,
    NoteGuid,
    BuyerGuid,
    CurrencyCode,
    GrossAmount,
    NetAmount,
    TaxAmount,
    BuyContactGuid,
    ShipToAdrGuid,
    BillToAdrGuid,
    /* Associations */
    _Buyer.CompanyName
(C) Brandeis Consulting

Extraktor View Entity mit Delta Einstellungen

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'View Entity Extractor'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}

@Analytics:{ dataCategory: #CUBE,
             dataExtraction: { enabled: true,
                               delta.byElement:{ name: 'ChangedAt',
                                                 maxDelayInSeconds: 1800 ,
                                                 detectDeletedRecords: true,
                                                 ignoreDeletionAfterDays: 30
           } } }
define view entity zcdse_extractor_soi as 
  select from snwd_so_i as soi
  
  inner join snwd_so as so
  on soi.parent_key = so.node_key

{
  key soi.node_key      as NodeKey,
      soi.parent_key    as ParentKey,
      soi.so_item_pos   as SoItemPos,
      soi.product_guid  as ProductGuid,
      soi.currency_code as CurrencyCode,
      
      @Semantics.amount.currencyCode: 'CurrencyCode'
      soi.gross_amount  as GrossAmount,
      @Semantics.amount.currencyCode: 'CurrencyCode'
      soi.net_amount    as NetAmount,
      @Semantics.amount.currencyCode: 'CurrencyCode'
      soi.tax_amount    as TaxAmount,
      
      @Semantics.systemDateTime.lastChangedAt: true
      so.changed_at as ChangedAt //For Delta Extraction

}
(C) Brandeis Consulting

Cube View

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Cube View Entitiy'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}
@Analytics.dataCategory: #CUBE
define view entity zcdse_cube as 
select from zcds_vdm_so_i 

association [1..1] to zcdse_dim_product as _Product
on $projection.ProductGuid = _Product.NodeKey

association [1..1] to zcdse_dim_businesspartner as _Buyer
on $projection.buyerguid = _Buyer.BupaNodeKey

{

    @EndUserText.label: 'SalesOrderItem'
    key NodeKey,
    
    @EndUserText.label: 'SalesOrder'
    ParentKey,
    SoItemPos,
    
    @ObjectModel.foreignKey.association: '_Product'
    @EndUserText.label: 'Product'
    ProductGuid,
    
    @ObjectModel.foreignKey.association: '_Buyer'
    @EndUserText.label: 'Customer'
    _SalesOrder.BuyerGuid,
    CurrencyCode,
    
      @Semantics.amount.currencyCode: 'CurrencyCode'
      @DefaultAggregation: #SUM      
    GrossAmount,
    
      @Semantics.amount.currencyCode: 'CurrencyCode'
      @DefaultAggregation: #SUM
    NetAmount,
    
      @Semantics.amount.currencyCode: 'CurrencyCode'
      @DefaultAggregation: #SUM
    TaxAmount,
    _Product,
    _Buyer
}
(C) Brandeis Consulting

Dimension View Business Partner

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Business Partner Dimension'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}
@Analytics.dataCategory: #DIMENSION
@ObjectModel.representativeKey: 'BupaNodeKey'
define view entity zcdse_dim_businesspartner as 

select from zcds_vdm_bpa {

    @ObjectModel.text.element: ['CompanyName']
    key NodeKey as BupaNodeKey,
    BpRole,
    EmailAddress,
    PhoneNumber,
    FaxNumber,
    WebAddress,
    AddressGuid,
    BpId,
    
    @Semantics.text: true
    CompanyName,
    LegalForm,
    CreatedBy,
    CreatedAt,
    ChangedBy,
    ChangedAt,
    CurrencyCode
}
(C) Brandeis Consulting

Dimension View Product

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Product'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}
@Analytics.dataCategory: #DIMENSION
@ObjectModel.representativeKey: 'NodeKey'
define view entity zcdse_dim_product as 

select from snwd_pd 

association [0..1] to zcdse_txt_product as _ProductText
on $projection.NodeKey = _ProductText.ProductNodeKey

{
    @ObjectModel.text.association: '_ProductText'
    key node_key as NodeKey, 
    
    product_id as ProductId,
    type_code as TypeCode,
    
    @Semantics.text: true
    category as Category,
    name_guid as NameGuid,
    currency_code as CurrencyCode,
    
    @Semantics.amount.currencyCode: 'CurrencyCode'
    price as Price,
    _ProductText
}
(C) Brandeis Consulting

Text-View Product

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Product Text'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}
@ObjectModel.dataCategory: #TEXT
@ObjectModel.representativeKey: 'ProductNodeKey'
define view entity zcdse_txt_product
  as select from snwd_pd    as pd
    inner join   snwd_texts as tx on pd.name_guid = tx.parent_key
{
  key pd.node_key as ProductNodeKey,
      @Semantics.language: true
  key tx.language as Language,
      @Semantics.text: true
      tx.text
}
(C) Brandeis Consulting

CDS Query (1)

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'Query on the Cube'

@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
} 
@Analytics.query: true

define view entity zcdse_query 
with parameters  
  @EndUserText.label: 'Alternative Mehrwertsteuer'
  vat : abap.int1
as 
select from zcdse_cube {
  @AnalyticsDetails.query:{ hidden:        true }   //#DESC
    key NodeKey,
    
  @AnalyticsDetails.query:{ hidden:        true }   //#DESC
    ParentKey,
    
  @AnalyticsDetails.query:{ hidden:        true }   //#DESC
    SoItemPos,
    
    @AnalyticsDetails.query:{ display:        #TEXT,
                            axis:          #ROWS,   //#COLUMNS
                            totals:        #SHOW,
                            sortDirection: #ASC  }   //#DESC  
    ProductGuid,
    _Product.Category,
    
    @AnalyticsDetails.query:{ display:        #TEXT,
                            axis:          #ROWS,   //#COLUMNS
                            totals:        #SHOW,
                            sortDirection: #ASC  }   //#DESC  
    BuyerGuid,
(C) Brandeis Consulting

CDS Query (2)

    @Consumption.filter:{ selectionType:       #SINGLE,
                        multipleSelections: false,
                        mandatory:          false,
                        hidden:             false,
                        defaultValue:       'EUR'  }    
    CurrencyCode,
    
    GrossAmount,
    @AnalyticsDetails.query:{ axis:     #COLUMNS,  //optional - Standard VALUE FOR keyfigures
                          decimals: 0,         //Decimals
                          hidden:   false      //true = initialy invisible
}
    NetAmount,
    
    @AnalyticsDetails.query:{ axis:     #COLUMNS,  //optional - Standard VALUE FOR keyfigures
                          decimals: 3,         //Decimals
                          hidden:   false      //true = initialy invisible
}
    TaxAmount,
    
    @EndUserText.label: 'Alt.MWst'
    @Semantics.amount.currencyCode: 'CurrencyCode'
    CURR_TO_DECFLOAT_AMOUNT(NetAmount ) * $parameters.vat / 100 as AltTaxAmount,
     
    @EndUserText.label: 'Anzahl'
    @AnalyticsDetails.exceptionAggregationSteps: 
       [{exceptionAggregationBehavior: #COUNT,
         exceptionAggregationElements: ['BuyerGuid']}]
    1 as cnt,
    /* Associations */
    _Buyer,
    _Product
}
(C) Brandeis Consulting

Table Function CDS

@EndUserText.label: 'Demo CDS Table Function'
@ClientHandling.type: #CLIENT_DEPENDENT
@ClientHandling.algorithm: #SESSION_VARIABLE
define table function zcds_table_function
with parameters 
@Environment.systemField: #CLIENT
client : abap.clnt

returns {
  client : abap.clnt;
  RowNumber : abap.int4;
  item: snwd_so_item_pos ;
  GrossAmount: snwd_ttl_gross_amount ;
  CurrencyCode: snwd_curr_code  ;
  
  
}
implemented by method zcl_cds_tf=>tafu;
(C) Brandeis Consulting

AMDP Beispiel und Table Function (Definition)

CLASS zcl_cds_tf DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.
    INTERFACES if_amdp_marker_hdb.
    TYPES tt_t005t TYPE STANDARD TABLE OF t005t WITH DEFAULT KEY.
    METHODS get_country_texts IMPORTING VALUE(langu)  TYPE spras
                              EXPORTING VALUE(result) TYPE tt_t005t.
    CLASS-METHODS tafu FOR TABLE FUNCTION zcds_table_function.
ENDCLASS.
(C) Brandeis Consulting

AMDP Beispiel und Table Function (Implementierung)

CLASS zcl_cds_tf IMPLEMENTATION.

  METHOD get_country_texts BY DATABASE PROCEDURE
                           FOR HDB LANGUAGE SQLSCRIPT
                           OPTIONS READ-ONLY
                           USING t005t.

    result = select *
               from t005t
               where spras = :langu
                 and mandt = session_context( 'CLIENT' );
*    SELECT *
*      FROM t005t
*    INTO TABLE result
*    WHERE spras = langu.

  ENDMETHOD.

  METHOD if_oo_adt_classrun~main.
*    DATA lt_result TYPE tt_t005t.
    SELECT *
      FROM zcds_table_function( )
      INTO TABLE @DATA(lt_result)
      up to 20 rows.

*    get_country_texts( EXPORTING langu  = 'D'
*                       IMPORTING result = lt_result ).

    out->write( lt_result  ).
  ENDMETHOD.

  METHOD tafu BY DATABASE FUNCTION
              FOR HDB LANGUAGE SQLSCRIPT
              OPTIONS READ-ONLY
              USING zcdse_cube.

    RETURN SELECT :client          AS client ,
                  ROW_NUMBER ( ) OVER ( order by NodeKey  ) as rownumber,
                  c.soitempos      as item,
                  c.grossamount    as GrossAmount,
                  c.currencycode   as  CurrencyCode

           from zcdse_cube as c
           ;

  endmethod.

ENDCLASS.
(C) Brandeis Consulting