Architect / Cross-trackintermediate

CDS Views Part 3: Aggregation and Analytical Annotations

Key takeaway

Aggregate data in CDS with SUM, COUNT, MIN, MAX, AVG and GROUP BY, understand why amounts must be grouped by currency, and see how analytical annotations turn CDS views into cubes and queries for embedded analytics.

Explanation

📌 KEY POINTS
✅ CDS supports SUM, COUNT(*), COUNT(DISTINCT), MIN, MAX and AVG with GROUP BY and HAVING — aggregation runs in the database and returns few rows.
✅ Every non-aggregated field in the field list must appear in GROUP BY; amounts must be grouped by their currency field.
✅ Analytical annotations (@Analytics.dataCategory: #CUBE / #DIMENSION, @Aggregation.default) describe views for the analytical engine used by embedded analytics, SAC and Analysis for Office.
⚠️ Summing amounts across currencies produces a meaningless number that looks correct — the most common aggregation bug.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Part 2 calculated values per row. Now we condense many rows into totals.

━━━━━━━━━━━━━━━━━━━━
▶ 1. AGGREGATION FUNCTIONS

sum( field ), count( * ), count( distinct field ), min, max, avg( field as abap.dec(15,2) ).
Every field that is not aggregated must be in GROUP BY.
HAVING filters on aggregated values (e.g. customers with more than 10 orders).

━━━━━━━━━━━━━━━━━━━━
▶ 2. KEYS IN AGGREGATED VIEWS

The key of an aggregated view is the GROUP BY combination (e.g. customer + currency).
Mark those fields as key so consumers and OData know what identifies a row.

━━━━━━━━━━━━━━━━━━━━
▶ 3. CURRENCY AND UNIT RULE

Group amount totals by currency (WAERK) and quantities by unit (VRKME/MEINS).
If one total in one currency is needed, convert first with currency_conversion, then aggregate — and know the performance cost (Part 10).

━━━━━━━━━━━━━━━━━━━━
▶ 4. STATIC AGGREGATION VS ANALYTICAL ENGINE

A view with GROUP BY returns fixed totals — good for reports and ABAP consumers.
Analytical models keep detail rows and let the analytical engine aggregate at runtime, depending on which dimensions the user drills into.

━━━━━━━━━━━━━━━━━━━━
▶ 5. ANALYTICAL ANNOTATIONS

@Analytics.dataCategory: #DIMENSION — master data (customer, material).
@Analytics.dataCategory: #CUBE — transactional facts with measures.
@Aggregation.default: #SUM on measures tells the engine how to aggregate.
Analytical query on top of the cube: classic style @Analytics.query: true; newer releases use a transient view entity with provider contract analytical_query.
Test in transaction RSRT or with Analysis for Office / Fiori Query Browser.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP

Build ZI_SalesByCustomer: VBAK grouped by KUNNR and WAERK with SUM(NETWR) and COUNT(*).
Mark SoldToParty and TransactionCurrency as key.
Read it from a classic report — one row per customer and currency.
Build a small cube view with @Analytics.dataCategory: #CUBE and @Aggregation.default: #SUM on NetAmount.

✅ CHECKPOINT: you can aggregate correctly in CDS and explain the difference between a GROUP BY view and an analytical cube.

Code example

ABAP Code

// ===== STEP 1: aggregated view (fixed totals) =====@AccessControl.authorizationCheck: #NOT_REQUIRED@EndUserText.label: 'Sales totals by customer and currency'define view entity ZI_SalesByCustomer  as select from vbak{  key kunnr as SoldToParty,  key waerk as TransactionCurrency,      @Semantics.amount.currencyCode: 'TransactionCurrency'      sum( netwr ) as TotalNetAmount,      count( * )   as NumberOfOrders}group by kunnr, waerk // ===== STEP 2: analytical cube (engine aggregates at runtime) =====@AccessControl.authorizationCheck: #NOT_REQUIRED@EndUserText.label: 'Sales order cube'@Analytics.dataCategory: #CUBEdefine view entity ZI_SalesOrderCube  as select from vbak{  key vbeln as SalesOrder,      vkorg as SalesOrganization,      kunnr as SoldToParty,      erdat as CreationDate,      waerk as TransactionCurrency,      @Semantics.amount.currencyCode: 'TransactionCurrency'      @Aggregation.default: #SUM      netwr as NetAmount} * ===== STEP 3: classic report reading the totals =====REPORT zcds_sales_by_customer. TYPES: BEGIN OF ty_tot,         soldtoparty         TYPE kunnr,         transactioncurrency TYPE waerk,         totalnetamount      TYPE netwr_ak,         numberoforders      TYPE i,       END OF ty_tot. DATA: lt_tot TYPE STANDARD TABLE OF ty_tot,      ls_tot TYPE ty_tot. START-OF-SELECTION.  SELECT soldtoparty transactioncurrency totalnetamount numberoforders    FROM zi_salesbycustomer    INTO TABLE lt_tot.   SORT lt_tot BY totalnetamount DESCENDING.   LOOP AT lt_tot INTO ls_tot.    WRITE: / ls_tot-soldtoparty,             ls_tot-totalnetamount CURRENCY ls_tot-transactioncurrency,             ls_tot-transactioncurrency, ls_tot-numberoforders.  ENDLOOP.

Real project scenario

A regional sales dashboard summed NETWR per customer without grouping by currency. A customer ordering in both EUR and JPY showed a "total" of over 40 million — mostly yen added to euros. Management questioned the whole dashboard.
The fix grouped by WAERK in the CDS view and added a second view converting to group currency with currency_conversion before aggregation. The dashboard then showed per-currency totals plus one converted total, and the numbers reconciled with FI.

Common mistakes

Summing amounts without grouping by currency (or quantities without unit).
Forgetting a non-aggregated field in GROUP BY (activation error).
Not marking the GROUP BY fields as key.
AVG on integer fields returning truncated values instead of decimals.
Building fixed GROUP BY views where users need flexible drill-down (use a cube instead).

Best practices

Always include currency/unit in GROUP BY for amounts and quantities.
Use GROUP BY views for fixed report totals, cube + query for flexible analytics.
Annotate measures with @Aggregation.default.
Check standard analytical views (I_*Cube, C_*Q) before building your own.
Reconcile totals with a trusted source (FI report, SE16) before go-live.

Interview angle

Questions: "How do you aggregate in CDS?", "What is the difference between a CDS cube and a dimension?", "What goes wrong when summing amounts?"
Strong answer: SUM/COUNT/MIN/MAX/AVG with GROUP BY and HAVING in the database; all non-aggregated fields in GROUP BY; group amounts by currency. #CUBE holds facts and measures with @Aggregation.default, #DIMENSION holds master data; the analytical engine aggregates at runtime via an analytical query. Summing mixed currencies gives a meaningless but plausible number.

Practice this lesson

Quick check

1. A report needs total order value per customer. Which approach uses HANA pushdown best?

2. A CDS view sums NETWR per customer. A customer orders in EUR and JPY. What must the view do to return correct totals?

3. Annotation @Analytics.dataCategory: #CUBE marks a view as:

Interview questions

1. A CDS-based sales dashboard shows a customer total of 40 million, but the customer orders in EUR and JPY. What went wrong and how do you fix it?

Answer it in your own words first, then compare.

2. Users want to drill a sales KPI by sales org, customer and month in any combination. Would you build a GROUP BY CDS view or an analytical cube? Explain.

Answer it in your own words first, then compare.

Practice more with Interview AI →

Reference-verified: the steps are checked against official SAP documentation and tutorials, and object and field names in the sample code are checked for consistency. The code has not been compiled or run on an SAP system by ERPClimb. Syntax marked release-dependent varies by SAP release, so check it in your own system.