RAP Custom Queries con Agregación: GROUP BY y Reporting

Kategorie

RAP

Veröffentlicht

15 feb 2026

Autor

Custom Entities con Agregación permiten funciones de reporting potentes en RAP. Mientras que las CDS Views son adecuadas para agregaciones simples, las Custom Queries con código ABAP ofrecen control total sobre operaciones GROUP BY complejas, filtros dinámicos y KPIs calculados.

¿Cuándo usar Custom Queries para Agregación?

Las Custom Queries son la elección correcta cuando:

Requisito	Agregación CDS	Custom Query
GROUP BY simple	Recomendado	Posible
Múltiples niveles de agregación	Limitado	Recomendado
Agrupación dinámica	No posible	Recomendado
Cálculos complejos (YoY, %)	Complejo	Recomendado
Fuentes de datos externas	No posible	Recomendado
Combinación de múltiples fuentes	No posible	Recomendado

Estructura Básica de una Custom Entity de Agregación

La Custom Entity define los campos agregados:

@EndUserText.label: 'Umsatzstatistik'
@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;
}

Implementación de Query con Agregación

La clase Query realiza la agregación mediante 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( ).
      " Filter abrufen
      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.

      " Aggregation durchführen
      DATA(lt_statistics) = aggregate_sales_data( lt_filter ).

      " Sortierung anwenden
      apply_sorting(
        EXPORTING it_sort = io_request->get_sort_elements( )
        CHANGING  ct_data = lt_statistics
      ).

      " Gesamtanzahl vor Paging speichern
      DATA(lv_total_count) = lines( lt_statistics ).

      " Paging anwenden
      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 mit 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.

    " Filter nachträglich anwenden
    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.
    " Dynamische Sortierung aufbauen
    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-Sortierung: Umsatz absteigend
      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.  " Kein Paging gewünscht
    ENDIF.

    " Maximale Seitengröße begrenzen
    IF lv_page_size > 500.
      lv_page_size = 500.
    ENDIF.

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

    " Nur gewünschten Bereich behalten
    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.

Ejemplo Práctico: Estadísticas de Facturación por Cliente y Mes

Un ejemplo completo para una aplicación de reporting de facturación:

1. Definición de Custom Entity

@EndUserText.label: 'Kundenumsatz-Statistik'
@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;
      " Berechnete KPIs
      RevenueShare  : abap.dec(5,2);    " % vom Gesamtumsatz
      GrowthRate    : abap.dec(7,2);    " % zum Vorjahr
}

2. Implementación de Query con KPIs Avanzados

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( ).
      " Filter abrufen
      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.

      " Aggregierte Daten laden
      DATA(lt_data) = load_aggregated_data( lt_filter ).

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

      " Sortierung
      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 vor 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.
    " Hauptaggregation mit 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' )  " Rechnungen und Gutschriften
    GROUP BY vbrk~kunag, vbrk~gjahr, vbrk~fkdat+4(2),
             kna1~name1, kna1~regio, vbrk~waerk
    INTO TABLE @rt_data.

    " Filter anwenden
    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. Gesamtumsatz für RevenueShare berechnen
    DATA(lv_total_revenue) = REDUCE wertv8(
      INIT sum = CONV wertv8( 0 )
      FOR ls_row IN ct_data
      NEXT sum = sum + ls_row-Revenue
    ).

    " 2. KPIs pro Zeile berechnen
    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 zum Vorjahresmonat
      <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.
    " Vorjahresumsatz abrufen
    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% Wachstum wenn Vorjahr = 0
    ENDIF.
  ENDMETHOD.
ENDCLASS.

Ordenación en Datos Agregados

Con datos agregados, la ordenación correcta es decisiva:

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

  " Sortierung über mehrere Felder
  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-Sortierung wenn nichts angegeben
  IF lt_sort IS INITIAL.
    SORT ct_data BY TotalRevenue DESCENDING.
  ENDIF.
ENDMETHOD.

Paging en Resultados Agregados

El paging debe ocurrir después de la agregación, pero antes de la salida:

┌─────────────────────────────────────────────────┐
│  1. SELECT mit GROUP BY                         │
│     → Aggregierte Rohdaten (z.B. 500 Zeilen)    │
├─────────────────────────────────────────────────┤
│  2. KPI-Berechnung                              │
│     → RevenueShare, GrowthRate, etc.            │
├─────────────────────────────────────────────────┤
│  3. Filter auf aggregierte Felder               │
│     → z.B. TotalRevenue > 10000                 │
├─────────────────────────────────────────────────┤
│  4. Total Count speichern                       │
│     → Für Pagination-Info (z.B. 487 Zeilen)     │
├─────────────────────────────────────────────────┤
│  5. Sortierung                                  │
│     → Nach Request-Parametern                   │
├─────────────────────────────────────────────────┤
│  6. Paging anwenden                             │
│     → Nur Seite X von Y zurückgeben             │
└─────────────────────────────────────────────────┘

METHOD if_rap_query_provider~select.
  " Schritt 1-3: Aggregation und Filter
  DATA(lt_data) = aggregate_and_filter( io_request ).

  " Schritt 4: Total Count VOR Paging speichern
  DATA(lv_total_count) = lines( lt_data ).

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

  " Schritt 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 für Pagination-Controls
  IF io_request->is_total_numb_of_rec_requested( ).
    io_response->set_total_number_of_records( lv_total_count ).
  ENDIF.
ENDMETHOD.

Filtro en Campos Agregados

A veces se quiere filtrar por valores agregados (ej. “solo clientes con facturación > 10000”):

METHOD apply_aggregation_filter.
  " Standard-Filter (vor Aggregation)
  LOOP AT it_filter INTO DATA(ls_filter).
    CASE ls_filter-name.
      " Filter auf Grunddaten - in WHERE-Clause
      WHEN 'CUSTOMER' OR 'FISCALYEAR' OR 'REGION'.
        " Diese Filter bereits in der SQL-Abfrage

      " Filter auf aggregierte Werte - nachträglich
      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.

Lógica Equivalente a HAVING

Para filtros de agregación complejos, use lógica equivalente a HAVING:

METHOD aggregate_with_having.
  " Aggregation durchführen
  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       " Mindest-Umsatz
     AND COUNT( * ) >= 5            " Mindest-Bestellungen
  INTO TABLE @rt_data.
ENDMETHOD.

Exposición del Servicio

La Custom Entity se expone como servicio OData de la manera habitual:

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

Consejos de Rendimiento para Queries de Agregación

Consejo	Descripción
Usar índices	Los campos de GROUP BY deben estar indexados
Aplicar filtros temprano	WHERE antes de GROUP BY reduce el volumen de datos
Limitar paging	Máx. 500 filas por página
Caching	Almacenar en caché agregaciones consultadas frecuentemente
Asíncrono	Reports largos como Background Job

" Beispiel: Caching für häufige Aggregationen
CLASS-DATA: gt_cache TYPE tt_statistics,
            gv_cache_key TYPE string,
            gv_cache_time TYPE timestamp.

METHOD get_cached_or_fresh.
  " Cache-Key aus Filter bauen
  DATA(lv_key) = build_cache_key( it_filter ).

  " Cache gültig? (5 Minuten)
  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.

Mejores Prácticas

Agregación en la base de datos - SELECT con GROUP BY en lugar de LOOP AT con COLLECT
Aplicar filtros temprano - WHERE-Clause reduce el volumen de datos antes de la agrupación
Usar HAVING - Para filtros en valores agregados
Total Count separado - Guardar antes de paging para paginación correcta
Ordenación flexible - Permitir ordenación dinámica sobre todos los campos
Calcular KPIs - RevenueShare, GrowthRate después de la agregación
Usar caching - Almacenar en caché reports consultados frecuentemente
Manejo de errores - TRY-CATCH para conversión de filtros

Resumen

Las Custom Queries con Agregación permiten reporting potente en RAP:

GROUP BY en ABAP SQL para agregación eficiente
SUM, COUNT, AVG, MIN, MAX como funciones de agregación
Paging después de la agregación para grandes conjuntos de resultados
Ordenación dinámica sobre todos los campos
Cálculo de KPIs después de la agregación básica
Filtro en valores agregados para evaluaciones flexibles

Temas Relacionados

RAP Custom Entities - Fundamentos de fuentes de datos externas
ABAP SQL Window Functions - Funciones analíticas
Novedades ABAP SQL 2024/2025 - GROUPING SETS y más
Custom Analytical Queries - Integración con SAP Analytics Cloud