Technical Articles
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
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
}
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:
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.
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.