Skip to Content
Technical Articles
Author's profile photo Johannes Gerbershagen

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

}

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Andrea Schlotthauer
      Andrea Schlotthauer

      Did you know? CDS views (DEFINE VIEW) are obsolete! When creating new data models, you should use CDS view entities (DEFINE VIEW ENTITY) instead.

      Details:

      For analytical models, CDS projection views with provider contract ANALYTICAL_QUERY are available. They are the preferred option, because they are designed only for analytical use cases and offer a suitable feature set and syntax checks.

      Details:

      Author's profile photo Johannes Gerbershagen
      Johannes Gerbershagen
      Blog Post Author

      From SAP perspective they may declared obsolete. From the customer perspective especially smaller companies, which can just slowly adopt new techniques, CDS-Views are still a feasible option. My personal opinion is, what is obsolete or not is moreless a personal decision, because obsolete technices still have the right to exist, when the work reliable.

      What technique will you choose, when customer expect just a reliable piece of software? The one you know best. In the project i referred in the blog post we created a simple ABAP report by calling the consumption view and display it as ALV-Grid, because this was the most reliable technique we know. The users told us, reliability is more important than fancy development techniques.

      Author's profile photo Shai Sinai
      Shai Sinai

      I don't think that CDS view entities are less reliable than CDS views.

      However, you raised a very important point: CDS view entities are available only since NW 7.55, which most of the customers don't have yet.