Aggregation Expressions and CDS-Views

In this blog post i wanted to show some ideas how to work with aggregation expressions in CDS-View-Clusters. CDS-View-Clusters are my term for a bundle of Basic-, Composite- and Consumption-Views, which are chained together as a virtual data model. As example i will use some requirements from a recent project.

Context

The CDS-View should provide the quantities of certified raw material consumed in process orders.

The database scheme

ZCERTIFIED_MAT

Certified Material

ZGM_TYPES

Goods movement types

Implementation

The first fact view splits goods movements for certified and non certified materials. The quick and dirty way would be hardcoding the movement types in the where-clause.

@AbapCatalog.sqlViewName: 'zcm_cons1'
@VDM.viewType: #BASIC
define view zcm_consumption1 as select from aufm as gm
  left outer join zcertified_mat as cm on cm.matnr = gm.matnr
  inner join mara as m on m.matnr = gm.matnr {

  key gm.aufnr,
  gm.matnr,
  m.mtart,
  case when cm.matnr is not null then case gm.shkzg when 'H' then gm.menge else -1 * gm.menge end else 0 end as quantity_certified_material,
  case gm.shkzg when 'H' then gm.menge else -1 * gm.menge end as quantity
} where bwart = '261' or bwart = '262'

But instead of hardcoding i want to show you a more customizable way. The table ZGM_TYPES contains the movement types. So we can integrate this table in the CDS-View.

@AbapCatalog.sqlViewName: 'zcm_cons1'
@VDM.viewType: #BASIC
define view zcm_consumption1 as select from aufm as gm
  left outer join zcertified_mat as cm on cm.matnr = gm.matnr
  inner join mara as m on m.matnr = gm.matnr
  // workaround to prevent syntax error
  inner join zgm_types as t on t.mandt = gm.mandt {

  key gm.aufnr,
  gm.matnr,
  m.mtart,
  case when cm.matnr is not null then case gm.shkzg when 'H' then gm.menge else -1 * gm.menge end else 0 end as quantity_certified_material,
  case gm.shkzg when 'H' then gm.menge else -1 * gm.menge end as quantity
} where gm.bwart = t.consumption or gm.bwart = t.cancel_consumption

The second fact view aggregates the goods movements. In this view we need to filter materials by its type. Here i want to show you the approach with customizing tables, too. The material type is stored as parameter in table TVARVC. This table is joined with the view ZCM_CONSUMPTION2. The join replaces the hardcoded value in the where-clause.

@AbapCatalog.sqlViewName: 'zcm_cons2'
@VDM.viewType: #COMPOSITE
define view zcm_consumption2 as select from zcm_consumption1 as mc
  inner join tvarvc as v on v.name = 'ZRAW_MATERIAL_TYPE' and v.type = 'P' and v.low = mc.mtart {

  key mc.aufnr,
  sum( quantity_certified_material ) as quantity_certified_material,
  sum( quantity ) as quantity

} group by mc.aufnr

This view just provides the order number as a key field. So the group by-clause clearly indicates how the key figures are aggregated.

The consumption view joins finally joins the process order properties with the consumed quantities.

@AbapCatalog.sqlViewName: 'zcm_proc_ord'
@VDM.viewType: #CONSUMPTION
define view zcm_process_order as select from caufv as po
  inner join zcm_consumption2 as c on c.aufnr = po.aufnr {

  key po.aufnr,
  po.werks,
  po.auart,
  po.autyp,
  c.quantity_certified_material,
  c.quantity as consumed_quantity

}