Using Conditional Aggregation in CDS views on AnyDB or HANA

Background:

We recently needed to quickly create an extract of sales order related data with ~200 columns using ~20 source tables from ECC on anyDB. With a relatively straightforward pull of data, with little or no calculated columns or complex transformations but a short timeline, we decided to use CDS views to exclusively gather the data.

There are other blogs within SCN warn or even advise against using CDS on a anyDB system, however in our case this approach really worked really well. Quick development time, very easy to test/debug, excellent performance and we now have multiple re-usable data models for Sales Order Header & Line related data.

Disclaimer:

It is imperative that all CDS views be thoroughly tested for performance & efficient database execution. The example below is from an ECC system where the customer values are setup unique per Sales Order, this may not be the case in your equivalent system.

Requirement:

We needed to ensure unique entries per sales order & sales order lines, but also had a requirement to include multiple customer/partner function data (e.g. sold to, ship to, bill to etc).

Using conditional aggregation in SQL to pivot multiple rows into a reduced number of rows (e.g. single) with corresponding column headings is a common approach used in all rdbms for some time, especially before dedicated sql functions like pivot/unpivot became available.

What I would like to show in this blog is how we can use conditional aggregation in ABAP CDS views to accomplish this task, while avoiding having to bring the records into ABAP and having multiple looping iterations to produce the same output.

Step 1:

To get one record per Sales Order transaction, we need to split out the BP function values (e.g. Ship To Country) into it’s own individual field on the Select statement.

We do this by using the CASE statement as follows..

We now have separate fields per each BP function and associated field value. There is also one row per BP function, in the example below, one row for Sold To, separate row for Bill To where only the Bill To fields are populated.

Step 2:

Aggregate the rows for each BP function down to a single row per Transaction.

We achieve this by using MAX function with a group by as shown below.

Querying this view now on Vbeln will give you one row with all the associated BP values split out in individual columns.