RAP Custom Queries with Aggregation: GROUP BY and Reporting

When to Use Custom Queries for Aggregation?

Custom Queries are the right choice when:

Requirement	CDS Aggregation	Custom Query
Simple GROUP BY	✅ Recommended	Possible
Multiple aggregation levels	⚠️ Limited	✅ Recommended
Dynamic grouping	❌ Not possible	✅ Recommended
Complex calculations (YoY, %)	⚠️ Complex	✅ Recommended
External data sources	❌ Not possible	✅ Recommended
Combining multiple sources	❌ Not possible	✅ Recommended

Basic Structure of an Aggregation Custom Entity

The Custom Entity defines the aggregated fields:

@EndUserText.label: 'Sales Statistics'
@ObjectModel.query.implementedBy: 'ABAP:ZCL_SALES_STAT_QUERY'
define custom entity ZI_SalesStatistics
{
  key CustomerId    : abap.char(10);
  key SalesMonth    : abap.char(7);      " YYYY-MM Format
      CustomerName  : abap.char(80);
      TotalRevenue  : abap.dec(15,2);
      OrderCount    : abap.int4;
      AvgOrderValue : abap.dec(15,2);
      MinOrderValue : abap.dec(15,2);
      MaxOrderValue : abap.dec(15,2);
      Currency      : abap.cuky;
}

Query Implementation with Aggregation

The query class performs aggregation using ABAP SQL:

CLASS zcl_sales_stat_query DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_rap_query_provider.

  PRIVATE SECTION.
    TYPES: BEGIN OF ty_statistics,
             CustomerId    TYPE kunnr,
             SalesMonth    TYPE char7,
             CustomerName  TYPE name1,
             TotalRevenue  TYPE wertv8,
             OrderCount    TYPE int4,
             AvgOrderValue TYPE wertv8,
             MinOrderValue TYPE wertv8,
             MaxOrderValue TYPE wertv8,
             Currency      TYPE waers,
           END OF ty_statistics,
           tt_statistics TYPE STANDARD TABLE OF ty_statistics WITH EMPTY KEY.

    METHODS aggregate_sales_data
      IMPORTING it_filter    TYPE if_rap_query_filter=>tt_name_range_pairs
      RETURNING VALUE(rt_data) TYPE tt_statistics.

    METHODS apply_sorting
      IMPORTING it_sort  TYPE abp_behv_sort_tab
      CHANGING  ct_data  TYPE tt_statistics.

    METHODS apply_paging
      IMPORTING io_paging TYPE REF TO if_rap_query_paging
      CHANGING  ct_data   TYPE tt_statistics.
ENDCLASS.

CLASS zcl_sales_stat_query IMPLEMENTATION.
  METHOD if_rap_query_provider~select.
    IF io_request->is_data_requested( ).
      " Retrieve filters
      DATA(lt_filter) = VALUE if_rap_query_filter=>tt_name_range_pairs( ).
      TRY.
          lt_filter = io_request->get_filter( )->get_as_ranges( ).
        CATCH cx_rap_query_filter_no_range.
      ENDTRY.

      " Perform aggregation
      DATA(lt_statistics) = aggregate_sales_data( lt_filter ).

      " Apply sorting
      apply_sorting(
        EXPORTING it_sort = io_request->get_sort_elements( )
        CHANGING  ct_data = lt_statistics
      ).

      " Store total count before paging
      DATA(lv_total_count) = lines( lt_statistics ).

      " Apply paging
      apply_paging(
        EXPORTING io_paging = io_request->get_paging( )
        CHANGING  ct_data   = lt_statistics
      ).

      io_response->set_data( lt_statistics ).
    ENDIF.

    IF io_request->is_total_numb_of_rec_requested( ).
      io_response->set_total_number_of_records( lv_total_count ).
    ENDIF.
  ENDMETHOD.

  METHOD aggregate_sales_data.
    " Aggregation with GROUP BY
    SELECT
      vbak~kunnr AS CustomerId,
      LEFT( CAST( vbak~erdat AS char ), 7 ) AS SalesMonth,
      kna1~name1 AS CustomerName,
      SUM( vbap~netwr ) AS TotalRevenue,
      COUNT( DISTINCT vbak~vbeln ) AS OrderCount,
      AVG( vbap~netwr ) AS AvgOrderValue,
      MIN( vbap~netwr ) AS MinOrderValue,
      MAX( vbap~netwr ) AS MaxOrderValue,
      vbak~waerk AS Currency
    FROM i_salesorder AS vbak
      INNER JOIN i_salesorderitem AS vbap ON vbap~salesorder = vbak~salesorder
      INNER JOIN i_customer AS kna1 ON kna1~customer = vbak~soldtoparty
    WHERE vbak~salesordertype = 'TA'
    GROUP BY vbak~kunnr, LEFT( CAST( vbak~erdat AS char ), 7 ),
             kna1~name1, vbak~waerk
    INTO TABLE @rt_data.

    " Apply filters afterwards
    LOOP AT it_filter INTO DATA(ls_filter).
      CASE ls_filter-name.
        WHEN 'CUSTOMERID'.
          DELETE rt_data WHERE CustomerId NOT IN ls_filter-range.
        WHEN 'SALESMONTH'.
          DELETE rt_data WHERE SalesMonth NOT IN ls_filter-range.
      ENDCASE.
    ENDLOOP.
  ENDMETHOD.

  METHOD apply_sorting.
    " Build dynamic sorting
    DATA(lv_sort_string) = ``.

    LOOP AT it_sort INTO DATA(ls_sort).
      IF lv_sort_string IS NOT INITIAL.
        lv_sort_string = lv_sort_string && ` `.
      ENDIF.

      lv_sort_string = lv_sort_string && ls_sort-element_name.

      IF ls_sort-descending = abap_true.
        lv_sort_string = lv_sort_string && ` DESCENDING`.
      ELSE.
        lv_sort_string = lv_sort_string && ` ASCENDING`.
      ENDIF.
    ENDLOOP.

    IF lv_sort_string IS NOT INITIAL.
      SORT ct_data BY (lv_sort_string).
    ELSE.
      " Default sorting: Revenue descending
      SORT ct_data BY TotalRevenue DESCENDING.
    ENDIF.
  ENDMETHOD.

  METHOD apply_paging.
    DATA(lv_offset) = io_paging->get_offset( ).
    DATA(lv_page_size) = io_paging->get_page_size( ).

    IF lv_page_size <= 0.
      RETURN.  " No paging requested
    ENDIF.

    " Limit maximum page size
    IF lv_page_size > 500.
      lv_page_size = 500.
    ENDIF.

    DATA(lv_from) = lv_offset + 1.
    DATA(lv_to) = lv_offset + lv_page_size.

    " Keep only desired range
    DATA lt_paged LIKE ct_data.
    LOOP AT ct_data INTO DATA(ls_row) FROM lv_from TO lv_to.
      APPEND ls_row TO lt_paged.
    ENDLOOP.

    ct_data = lt_paged.
  ENDMETHOD.
ENDCLASS.

Practical Example: Revenue Statistics by Customer and Month

A complete example for a revenue reporting application:

1. Custom Entity Definition

@EndUserText.label: 'Customer Revenue Statistics'
@ObjectModel.query.implementedBy: 'ABAP:ZCL_REVENUE_REPORT_QUERY'
define custom entity ZI_CustomerRevenue
{
  key Customer      : kunnr;
  key FiscalYear    : gjahr;
  key FiscalPeriod  : monat;
      CustomerName  : name1;
      Region        : regio;
      Revenue       : wertv8;
      Quantity      : menge_d;
      OrderCount    : abap.int4;
      AvgOrderValue : wertv8;
      Currency      : waers;
      " Calculated KPIs
      RevenueShare  : abap.dec(5,2);    " % of total revenue
      GrowthRate    : abap.dec(7,2);    " % vs. previous year
}

2. Query Implementation with Extended KPIs

CLASS zcl_revenue_report_query DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_rap_query_provider.

  PRIVATE SECTION.
    TYPES: BEGIN OF ty_revenue,
             Customer      TYPE kunnr,
             FiscalYear    TYPE gjahr,
             FiscalPeriod  TYPE monat,
             CustomerName  TYPE name1,
             Region        TYPE regio,
             Revenue       TYPE wertv8,
             Quantity      TYPE menge_d,
             OrderCount    TYPE int4,
             AvgOrderValue TYPE wertv8,
             Currency      TYPE waers,
             RevenueShare  TYPE decfloat16,
             GrowthRate    TYPE decfloat16,
           END OF ty_revenue,
           tt_revenue TYPE STANDARD TABLE OF ty_revenue WITH EMPTY KEY.

    METHODS load_aggregated_data
      IMPORTING it_filter TYPE if_rap_query_filter=>tt_name_range_pairs
      RETURNING VALUE(rt_data) TYPE tt_revenue.

    METHODS calculate_kpis
      CHANGING ct_data TYPE tt_revenue.

    METHODS calculate_growth_rate
      IMPORTING iv_customer   TYPE kunnr
                iv_year       TYPE gjahr
                iv_period     TYPE monat
                iv_revenue    TYPE wertv8
      RETURNING VALUE(rv_rate) TYPE decfloat16.
ENDCLASS.

CLASS zcl_revenue_report_query IMPLEMENTATION.
  METHOD if_rap_query_provider~select.
    IF io_request->is_data_requested( ).
      " Retrieve filters
      DATA(lt_filter) = VALUE if_rap_query_filter=>tt_name_range_pairs( ).
      TRY.
          lt_filter = io_request->get_filter( )->get_as_ranges( ).
        CATCH cx_rap_query_filter_no_range.
      ENDTRY.

      " Load aggregated data
      DATA(lt_data) = load_aggregated_data( lt_filter ).

      " Calculate KPIs (RevenueShare, GrowthRate)
      calculate_kpis( CHANGING ct_data = lt_data ).

      " Sorting
      DATA(lt_sort) = io_request->get_sort_elements( ).
      IF lt_sort IS NOT INITIAL.
        DATA(lv_sort_str) = ``.
        LOOP AT lt_sort INTO DATA(ls_sort).
          IF lv_sort_str IS NOT INITIAL.
            lv_sort_str = lv_sort_str && ` `.
          ENDIF.
          lv_sort_str = lv_sort_str && ls_sort-element_name.
          IF ls_sort-descending = abap_true.
            lv_sort_str = lv_sort_str && ` DESCENDING`.
          ELSE.
            lv_sort_str = lv_sort_str && ` ASCENDING`.
          ENDIF.
        ENDLOOP.
        SORT lt_data BY (lv_sort_str).
      ELSE.
        SORT lt_data BY Revenue DESCENDING.
      ENDIF.

      " Total count before paging
      DATA(lv_count) = lines( lt_data ).

      " Paging
      DATA(lv_offset) = io_request->get_paging( )->get_offset( ).
      DATA(lv_size) = io_request->get_paging( )->get_page_size( ).

      IF lv_size > 0.
        DATA lt_paged LIKE lt_data.
        LOOP AT lt_data INTO DATA(ls_row)
          FROM lv_offset + 1 TO lv_offset + lv_size.
          APPEND ls_row TO lt_paged.
        ENDLOOP.
        io_response->set_data( lt_paged ).
      ELSE.
        io_response->set_data( lt_data ).
      ENDIF.
    ENDIF.

    IF io_request->is_total_numb_of_rec_requested( ).
      io_response->set_total_number_of_records( lv_count ).
    ENDIF.
  ENDMETHOD.

  METHOD load_aggregated_data.
    " Main aggregation with GROUP BY
    SELECT
      vbrk~kunag AS Customer,
      vbrk~gjahr AS FiscalYear,
      vbrk~fkdat+4(2) AS FiscalPeriod,
      kna1~name1 AS CustomerName,
      kna1~regio AS Region,
      SUM( vbrp~netwr ) AS Revenue,
      SUM( vbrp~fkimg ) AS Quantity,
      COUNT( DISTINCT vbrk~vbeln ) AS OrderCount,
      AVG( vbrp~netwr ) AS AvgOrderValue,
      vbrk~waerk AS Currency
    FROM i_billingdocument AS vbrk
      INNER JOIN i_billingdocumentitem AS vbrp
        ON vbrp~billingdocument = vbrk~billingdocument
      INNER JOIN i_customer AS kna1
        ON kna1~customer = vbrk~soldtoparty
    WHERE vbrk~fkart IN ( 'F2', 'L2' )  " Invoices and credit memos
    GROUP BY vbrk~kunag, vbrk~gjahr, vbrk~fkdat+4(2),
             kna1~name1, kna1~regio, vbrk~waerk
    INTO TABLE @rt_data.

    " Apply filters
    LOOP AT it_filter INTO DATA(ls_filter).
      CASE ls_filter-name.
        WHEN 'CUSTOMER'.
          DELETE rt_data WHERE Customer NOT IN ls_filter-range.
        WHEN 'FISCALYEAR'.
          DELETE rt_data WHERE FiscalYear NOT IN ls_filter-range.
        WHEN 'FISCALPERIOD'.
          DELETE rt_data WHERE FiscalPeriod NOT IN ls_filter-range.
        WHEN 'REGION'.
          DELETE rt_data WHERE Region NOT IN ls_filter-range.
      ENDCASE.
    ENDLOOP.
  ENDMETHOD.

  METHOD calculate_kpis.
    " 1. Calculate total revenue for RevenueShare
    DATA(lv_total_revenue) = REDUCE wertv8(
      INIT sum = CONV wertv8( 0 )
      FOR ls_row IN ct_data
      NEXT sum = sum + ls_row-Revenue
    ).

    " 2. Calculate KPIs per row
    LOOP AT ct_data ASSIGNING FIELD-SYMBOL(<ls_row>).
      " RevenueShare
      IF lv_total_revenue > 0.
        <ls_row>-RevenueShare = ( <ls_row>-Revenue / lv_total_revenue ) * 100.
      ENDIF.

      " GrowthRate vs. previous year month
      <ls_row>-GrowthRate = calculate_growth_rate(
        iv_customer = <ls_row>-Customer
        iv_year     = <ls_row>-FiscalYear
        iv_period   = <ls_row>-FiscalPeriod
        iv_revenue  = <ls_row>-Revenue
      ).
    ENDLOOP.
  ENDMETHOD.

  METHOD calculate_growth_rate.
    " Retrieve previous year revenue
    DATA(lv_prev_year) = iv_year - 1.

    SELECT SINGLE SUM( vbrp~netwr ) AS Revenue
      FROM i_billingdocument AS vbrk
        INNER JOIN i_billingdocumentitem AS vbrp
          ON vbrp~billingdocument = vbrk~billingdocument
      WHERE vbrk~kunag = @iv_customer
        AND vbrk~gjahr = @lv_prev_year
        AND vbrk~fkdat+4(2) = @iv_period
        AND vbrk~fkart IN ( 'F2', 'L2' )
      INTO @DATA(lv_prev_revenue).

    IF lv_prev_revenue > 0.
      rv_rate = ( ( iv_revenue - lv_prev_revenue ) / lv_prev_revenue ) * 100.
    ELSEIF iv_revenue > 0.
      rv_rate = 100.  " 100% growth if previous year = 0
    ENDIF.
  ENDMETHOD.
ENDCLASS.

Sorting Aggregated Data

Proper sorting is crucial for aggregated data:

METHOD apply_aggregation_sorting.
  DATA(lt_sort) = io_request->get_sort_elements( ).

  " Sorting over multiple fields
  LOOP AT lt_sort INTO DATA(ls_sort).
    CASE ls_sort-element_name.
      WHEN 'TOTALREVENUE'.
        IF ls_sort-descending = abap_true.
          SORT ct_data BY TotalRevenue DESCENDING.
        ELSE.
          SORT ct_data BY TotalRevenue ASCENDING.
        ENDIF.

      WHEN 'ORDERCOUNT'.
        IF ls_sort-descending = abap_true.
          SORT ct_data BY OrderCount DESCENDING TotalRevenue DESCENDING.
        ELSE.
          SORT ct_data BY OrderCount ASCENDING.
        ENDIF.

      WHEN 'AVGORDERVALUE'.
        IF ls_sort-descending = abap_true.
          SORT ct_data BY AvgOrderValue DESCENDING.
        ELSE.
          SORT ct_data BY AvgOrderValue ASCENDING.
        ENDIF.

      WHEN 'CUSTOMERNAME'.
        IF ls_sort-descending = abap_true.
          SORT ct_data BY CustomerName DESCENDING.
        ELSE.
          SORT ct_data BY CustomerName ASCENDING.
        ENDIF.
    ENDCASE.
  ENDLOOP.

  " Default sorting when nothing specified
  IF lt_sort IS INITIAL.
    SORT ct_data BY TotalRevenue DESCENDING.
  ENDIF.
ENDMETHOD.

Paging Aggregated Results

Paging must occur after aggregation but before output:

┌─────────────────────────────────────────────────┐
│  1. SELECT with GROUP BY                        │
│     → Aggregated raw data (e.g., 500 rows)      │
├─────────────────────────────────────────────────┤
│  2. KPI calculation                             │
│     → RevenueShare, GrowthRate, etc.            │
├─────────────────────────────────────────────────┤
│  3. Filter on aggregated fields                 │
│     → e.g., TotalRevenue > 10000                │
├─────────────────────────────────────────────────┤
│  4. Store total count                           │
│     → For pagination info (e.g., 487 rows)      │
├─────────────────────────────────────────────────┤
│  5. Sorting                                     │
│     → According to request parameters           │
├─────────────────────────────────────────────────┤
│  6. Apply paging                                │
│     → Return only page X of Y                   │
└─────────────────────────────────────────────────┘

METHOD if_rap_query_provider~select.
  " Steps 1-3: Aggregation and filters
  DATA(lt_data) = aggregate_and_filter( io_request ).

  " Step 4: Total count BEFORE paging
  DATA(lv_total_count) = lines( lt_data ).

  " Step 5: Sorting
  apply_sorting(
    EXPORTING it_sort = io_request->get_sort_elements( )
    CHANGING  ct_data = lt_data
  ).

  " Step 6: Paging
  DATA(lv_offset) = io_request->get_paging( )->get_offset( ).
  DATA(lv_page_size) = io_request->get_paging( )->get_page_size( ).

  IF lv_page_size > 0.
    DATA lt_paged LIKE lt_data.
    DATA(lv_from) = lv_offset + 1.
    DATA(lv_to) = lv_offset + lv_page_size.

    LOOP AT lt_data INTO DATA(ls_row) FROM lv_from TO lv_to.
      APPEND ls_row TO lt_paged.
    ENDLOOP.

    io_response->set_data( lt_paged ).
  ELSE.
    io_response->set_data( lt_data ).
  ENDIF.

  " Total count for pagination controls
  IF io_request->is_total_numb_of_rec_requested( ).
    io_response->set_total_number_of_records( lv_total_count ).
  ENDIF.
ENDMETHOD.

Filtering on Aggregated Fields

Sometimes you want to filter by aggregated values (e.g., “only customers with revenue > 10000”):

METHOD apply_aggregation_filter.
  " Standard filters (before aggregation)
  LOOP AT it_filter INTO DATA(ls_filter).
    CASE ls_filter-name.
      " Filters on base data - in WHERE clause
      WHEN 'CUSTOMER' OR 'FISCALYEAR' OR 'REGION'.
        " These filters already in SQL query

      " Filters on aggregated values - afterwards
      WHEN 'TOTALREVENUE'.
        DELETE ct_data WHERE TotalRevenue NOT IN ls_filter-range.

      WHEN 'ORDERCOUNT'.
        DELETE ct_data WHERE OrderCount NOT IN ls_filter-range.

      WHEN 'AVGORDERVALUE'.
        DELETE ct_data WHERE AvgOrderValue NOT IN ls_filter-range.
    ENDCASE.
  ENDLOOP.
ENDMETHOD.

HAVING-Equivalent Logic

For complex aggregation filters, use HAVING-equivalent logic:

METHOD aggregate_with_having.
  " Perform aggregation
  SELECT
    kunag AS Customer,
    gjahr AS FiscalYear,
    SUM( netwr ) AS TotalRevenue,
    COUNT( * ) AS OrderCount,
    AVG( netwr ) AS AvgOrderValue
  FROM i_billingdocument
  GROUP BY kunag, gjahr
  HAVING SUM( netwr ) > 10000       " Minimum revenue
     AND COUNT( * ) >= 5            " Minimum orders
  INTO TABLE @rt_data.
ENDMETHOD.

Service Exposure

The Custom Entity is exposed as an OData service as usual:

@EndUserText.label: 'Revenue Reporting Service'
define service ZUI_RevenueReport {
  expose ZI_CustomerRevenue as CustomerRevenue;
  expose ZI_SalesStatistics as SalesStatistics;
}

Performance Tips for Aggregation Queries

Tip	Description
Use indexes	GROUP BY fields should be indexed
Apply filters early	WHERE before GROUP BY reduces data volume
Limit paging	Max 500 rows per page
Caching	Cache frequently accessed aggregations
Asynchronous	Long reports as background jobs

" Example: Caching for frequent aggregations
CLASS-DATA: gt_cache TYPE tt_statistics,
            gv_cache_key TYPE string,
            gv_cache_time TYPE timestamp.

METHOD get_cached_or_fresh.
  " Build cache key from filter
  DATA(lv_key) = build_cache_key( it_filter ).

  " Cache valid? (5 minutes)
  DATA(lv_now) = utclong_current( ).
  DATA(lv_age) = cl_abap_tstmp=>subtract(
    tstmp1 = lv_now
    tstmp2 = gv_cache_time
  ).

  IF lv_key = gv_cache_key AND lv_age < 300.
    rt_data = gt_cache.
  ELSE.
    rt_data = aggregate_fresh( it_filter ).
    gt_cache = rt_data.
    gv_cache_key = lv_key.
    gv_cache_time = lv_now.
  ENDIF.
ENDMETHOD.

Best Practices

Aggregation in Database - SELECT with GROUP BY instead of LOOP AT with COLLECT
Apply Filters Early - WHERE clause reduces data volume before grouping
Use HAVING - For filters on aggregated values
Separate Total Count - Store before paging for correct pagination
Flexible Sorting - Enable dynamic sorting over all fields
Calculate KPIs - RevenueShare, GrowthRate after aggregation
Use Caching - Cache frequently accessed reports
Error Handling - TRY-CATCH for filter conversion

Summary

Custom Queries with aggregation enable powerful reporting in RAP:

GROUP BY in ABAP SQL for efficient aggregation
SUM, COUNT, AVG, MIN, MAX as aggregation functions
Paging after aggregation for large result sets
Dynamic sorting over all fields
KPI calculation after basic aggregation
Filters on aggregate values for flexible evaluations

RAP Custom Entities - Basics of external data sources
ABAP SQL Window Functions - Analytical functions
ABAP SQL Features 2024/2025 - GROUPING SETS and more
Custom Analytical Queries - SAP Analytics Cloud integration