ALV GRID display with Average and subtotals.

[Former Member]

Hi Team,

i have a requirement like display the ALV Grid report with calculate the Average and Sub totals based on Customer(KUNNR).

Kunnr Amount

100001 100

200

300

400

100002 200

400

Need to be output as follows:

Kunnr Amount

100001 100

200

300

400

Avearge 250

Sub total 1000

100002 200

400

Avearge 300

Sub total 600

This is the requirement . Could you please suggest me it is possible to do it.If yes then please suggest me how to do this.

Thanks in Advance.

Puneet.

[former_member188829]

Hi,

You have to set DO_SUM = 'C' instead of 'X' in the field catalog for that column.(For getting Average)

WA-FCAT-DO_SUM = 'C'.

[Former Member]

Hi Visnu,

Thanks for quick reply,

But my requirement is that calculate both Average and Subtotals into the report layout. So is it possible to do both calculation in one report.

Thanks .

Puneet.

[I355602]

Hi,

In the field catalogs, use:-

*FIELD CATALOG
DATA : it_field TYPE slis_t_fieldcat_alv,
       wa_field TYPE slis_fieldcat_alv.

wa_fieldcat-do_sum = 'X'.  "<-- for grand total

wa_fieldcat-do_sum = 'C'. "<-- for average

append wa_fieldcat to it_fieldcat.

In the sort info, use:-

*SORTING
DATA : it_sort TYPE slis_t_sortinfo_alv,
       wa_sort TYPE slis_sortinfo_alv.

wa_sort-subtot = 'X'. "<-- for sub-totals
append wa_sort to it_sort.

Pass this internal tables it_fieldcat and it_sort in FM REUSE_ALV_GRID_DISPLAY

Hope this helps you.

Regards,

Tarun

[Former Member]

If we are passing value into the fieldcat firstly 'X' then 'C' the value will overwrite with 'C' so finally i got average instead of both Average and sub totals.

[I355602]

Hi,

>

> If we are passing value into the fieldcat firstly 'X' then 'C' the value will overwrite with 'C' so finally i got average instead of both Average and sub totals.

You didnt asked to print the grand total, do_sum = 'X' for grand total, you want to print average so use do_sum = 'C'.

In the field catalogs, use:-

*FIELD CATALOG
DATA : it_field TYPE slis_t_fieldcat_alv,
       wa_field TYPE slis_fieldcat_alv.
 
wa_fieldcat-do_sum = 'C'. "<-- for average
 
append wa_fieldcat to it_fieldcat.

In the sort info, use:-

*SORTING

DATA : it_sort TYPE slis_t_sortinfo_alv,

wa_sort TYPE slis_sortinfo_alv.

wa_sort-subtot = 'X'. "<-- for sub-totals

append wa_sort to it_sort.

Pass this internal tables it_fieldcat and it_sort in FM REUSE_ALV_GRID_DISPLAY

Hope this helps you.

Regards,

Tarun

[Former Member]

Hi Puneet,

DATA : it_field TYPE slis_t_fieldcat_alv,
       wa_field TYPE slis_fieldcat_alv.
DATA:  itab_sort TYPE slis_t_sortinfo_alv,
   wa_sort TYPE slis_sortinfo_alv.



  wa_field-fieldname = 'EBELN'.
  wa_field-tabname = 'IT_TAB'.
  wa_field-outputlen = 20.
  wa_field-col_pos = '1'.
  wa_field-seltext_l ='P.O NUMBER'.
wa_field-do_sum = 'X'.           "will displayed in the field catalog by this step
APPEND wa_field TO it_field.
CLEAR wa_field.


wa_sort-spos = 1.
wa_sort-fieldname = 'EBELN'.
wa_sort-tabname = 'IT_tab'.
wa_sort-up = 'X'.
wa_sort-subtot = 'X'.                "subtotal by this field
 APPEND wa_sort TO itab_sort.
CLEAR wa_sort.

Hope it helps

Regards

Mansi

[Former Member]

Hi,

Check out the following eg code for subtotal as well as totals.

REPORT sy-repid MESSAGE-ID 00.
*&---------------------------------------------------------------------*
*& Table declaration
*&---------------------------------------------------------------------*
TABLES: ytest_alv_subtot.
*&---------------------------------------------------------------------*
*& Type pool declaration
*&---------------------------------------------------------------------*
TYPE-POOLS: slis. " Type pool for ALV
*&---------------------------------------------------------------------*
*& Selection screen
*&---------------------------------------------------------------------*
*SELECT-OPTIONS: s_ebeln FOR ekko-ebeln.
*&---------------------------------------------------------------------*
*& Type declaration
*&---------------------------------------------------------------------*
* Type declaration for internal table to store ALV data
TYPES: BEGIN OF ty_alv,
        zroll TYPE ytest_alv_subtot-zroll,
        zname TYPE ytest_alv_subtot-zname,
        zsem TYPE ytest_alv_subtot-zsem,
        zsubj TYPE string,
        zmarks TYPE ytest_alv_subtot-zeng,
      END OF ty_alv.

*&---------------------------------------------------------------------*
*& Internal table declaration
*&---------------------------------------------------------------------*
DATA:
* Internal table to store ytest_alv_subtot data
  itab_data TYPE STANDARD TABLE OF ytest_alv_subtot INITIAL SIZE 0,
* Internal table to store ALV table data
  itab_alv TYPE STANDARD TABLE OF ty_alv INITIAL SIZE 0,
* Internal table for storing field catalog information
  i_fieldcat TYPE slis_t_fieldcat_alv,
* Internal table for Top of Page info. in ALV Display
  i_alv_top_of_page TYPE slis_t_listheader,
* Internal table for ALV Display events
  i_events TYPE slis_t_event,
* Internal table for storing ALV sort information
  i_sort  TYPE  slis_t_sortinfo_alv,
  i_event TYPE slis_t_event.
*&---------------------------------------------------------------------*
*& Work area declaration
*&---------------------------------------------------------------------*
DATA:
  wa_data       TYPE ytest_alv_subtot,
  wa_alv        TYPE ty_alv,
  wa_layout     TYPE slis_layout_alv,
  wa_events     TYPE slis_alv_event,
  wa_sort       TYPE slis_sortinfo_alv.
*&---------------------------------------------------------------------*
*& Start-of-selection event
*&---------------------------------------------------------------------*
START-OF-SELECTION.
* Select data from ekpo
  SELECT * FROM ytest_alv_subtot
  INTO TABLE itab_data.

  IF sy-subrc = 0.
    SORT itab_data BY zroll zname zsem.
  ENDIF.

* Build the ALV data internal table
  LOOP AT itab_data INTO wa_data.
    MOVE-CORRESPONDING wa_data TO wa_alv.
    wa_alv-zsubj  = 'Maths'.
    wa_alv-zmarks = wa_data-zmat.
    APPEND wa_alv TO itab_alv.

    wa_alv-zsubj  = 'English'.
    wa_alv-zmarks = wa_data-zeng.
    APPEND wa_alv TO itab_alv.

    wa_alv-zsubj  = 'Science'.
    wa_alv-zmarks = wa_data-zsci.
    APPEND wa_alv TO itab_alv.

  ENDLOOP.

  SORT itab_alv BY zroll zname zsem.

* To build the Page header
  PERFORM sub_build_header.

* To prepare field catalog
  PERFORM field_catalog.

* Perform to populate the layout structure
  PERFORM sub_populate_layout.

* Perform to populate the sort table.
  PERFORM sub_sort.

END-OF-SELECTION.
* Perform to display ALV report
  PERFORM sub_alv_report_display.
*&---------------------------------------------------------------------*
*&      Form  sub_build_header
*&---------------------------------------------------------------------*
*       To build the header
*----------------------------------------------------------------------*
*       No Parameter
*----------------------------------------------------------------------*
FORM sub_build_header .
  DATA : l_top TYPE string.
  l_top = 'Annual report for the year 2008-2009'.
  APPEND l_top TO i_alv_top_of_page.
ENDFORM.                    " sub_build_header

*&---------------------------------------------------------------------*
*&      Form  sub_populate_layout
*&---------------------------------------------------------------------*
*       Populate ALV layout
*----------------------------------------------------------------------*
*       No Parameter
*----------------------------------------------------------------------*
FORM sub_populate_layout .
  CLEAR wa_layout.
  wa_layout-colwidth_optimize = 'X'." Optimization of Col width
*  wa_layout-cell_merge = 'X'.
ENDFORM.                    " sub_populate_layout

*&---------------------------------------------------------------------*
*&      Form  sub_get_event
*&---------------------------------------------------------------------*
*       Get ALV grid event and pass the form name to subtotal_text
*       event
*----------------------------------------------------------------------*
*       No Parameter
*----------------------------------------------------------------------*
FORM sub_get_event .
  CONSTANTS : c_formname_subtotal_text TYPE slis_formname VALUE 'SUBTOTAL_TEXT'.
  DATA: l_s_event TYPE slis_alv_event.
  CALL FUNCTION 'REUSE_ALV_EVENTS_GET'
    EXPORTING
      i_list_type     = 4
    IMPORTING
      et_events       = i_event
    EXCEPTIONS
      list_type_wrong = 0
      OTHERS          = 0.
* Subtotal
  READ TABLE i_event  INTO l_s_event
                    WITH KEY name = slis_ev_subtotal_text.
  IF sy-subrc = 0.
    MOVE c_formname_subtotal_text TO l_s_event-form.
    MODIFY i_event FROM l_s_event INDEX sy-tabix.
  ENDIF.
ENDFORM.                    " sub_get_event
*&---------------------------------------------------------------------*
*&      Form  sub_alv_report_display
*&---------------------------------------------------------------------*
*       For ALV Report Display
*----------------------------------------------------------------------*
*       No Parameter
*----------------------------------------------------------------------*
FORM sub_alv_report_display .
  DATA: l_repid TYPE syrepid .
  l_repid = sy-repid .
* This function module for displaying the ALV report
  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
      i_callback_program     = l_repid
      i_callback_top_of_page = 'SUB_ALV_TOP_OF_PAGE'
      is_layout              = wa_layout
      it_fieldcat            = i_fieldcat[]
      it_sort                = i_sort[]
      it_events              = i_event
      i_default              = 'X'
      i_save                 = 'A'
    TABLES
      t_outtab               = itab_alv
    EXCEPTIONS
      program_error          = 1
      OTHERS                 = 2.
  IF sy-subrc <> 0.
    MESSAGE i000 WITH 'Error in ALV report display'.
  ENDIF.
ENDFORM.                    " sub_alv_report_display
*&---------------------------------------------------------------------*
*       FORM sub_alv_top_of_page
*---------------------------------------------------------------------*
*       Call ALV top of page
*---------------------------------------------------------------------*
*       No parameter
*---------------------------------------------------------------------*
FORM sub_alv_top_of_page.                                   "#EC CALLED
* To write header for the ALV
  CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'
    EXPORTING
      it_list_commentary = i_alv_top_of_page.
ENDFORM.                    "alv_top_of_page
*&---------------------------------------------------------------------*
*&      Form  field_catalog
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM field_catalog .

  DATA: wa_lfl_fcat    TYPE  slis_fieldcat_alv.

  wa_lfl_fcat-fieldname = 'ZROLL'.
  wa_lfl_fcat-tabname = 'ITAB_ALV'.
  wa_lfl_fcat-do_sum = ' '.
  wa_lfl_fcat-cfieldname = ' '.
  APPEND wa_lfl_fcat TO i_fieldcat.

  wa_lfl_fcat-fieldname = 'ZNAME'.
  wa_lfl_fcat-tabname = 'ITAB_ALV'.
  wa_lfl_fcat-do_sum = ' '.
  wa_lfl_fcat-cfieldname = ' '.
  APPEND wa_lfl_fcat TO i_fieldcat.

  wa_lfl_fcat-fieldname = 'ZSEM'.
  wa_lfl_fcat-tabname = 'ITAB_ALV'.
  wa_lfl_fcat-do_sum = ' '.
  wa_lfl_fcat-cfieldname = ' '.
  APPEND wa_lfl_fcat TO i_fieldcat.

  wa_lfl_fcat-fieldname = 'ZSUBJ'.
  wa_lfl_fcat-tabname = 'ITAB_ALV'.
  wa_lfl_fcat-do_sum = ' '.
  wa_lfl_fcat-cfieldname = ' '.
  APPEND wa_lfl_fcat TO i_fieldcat.

  wa_lfl_fcat-fieldname = 'ZMARKS'.
  wa_lfl_fcat-tabname = 'ITAB_ALV'.
  wa_lfl_fcat-do_sum = 'X'.
  wa_lfl_fcat-cfieldname = ' '.
  APPEND wa_lfl_fcat TO i_fieldcat.


ENDFORM.                    " field_catalog
*&---------------------------------------------------------------------*
*&      Form  sub_sort
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM sub_sort .
  wa_sort-fieldname = 'ZROLL'.
  wa_sort-up = 'X'.
  wa_sort-subtot = ' '.
  APPEND wa_sort TO i_sort.

  wa_sort-fieldname = 'ZNAME'.
  wa_sort-up = 'X'.
  wa_sort-subtot = 'X'.
  APPEND wa_sort TO i_sort.

  wa_sort-fieldname = 'ZSEM'.
  wa_sort-up = 'X'.
  wa_sort-subtot = 'X'.
  APPEND wa_sort TO i_sort.

ENDFORM.                    " sub_sort

[Former Member]

i calculate subtotal and average by my code not automatic with alv, but i think subtotal may be calculate and output by alv. hope it helpful thanks

TYPE-POOLS slis.

TYPES: BEGIN OF ty_data,
         kunnr    TYPE kna1-kunnr,
         amount    TYPE i,
         subtotal TYPE i,
         color    TYPE char4,
       END OF ty_data.



DATA: it_data   TYPE TABLE OF ty_data,
      it_out    TYPE TABLE OF ty_data,
      iw_out    TYPE ty_data,
      w_sum_kun TYPE i,
      iw_data   TYPE ty_data.
DATA: iw_fcat   TYPE slis_fieldcat_alv,
      it_fcat   TYPE slis_t_fieldcat_alv,
      iw_layout TYPE slis_layout_alv.

iw_data-kunnr = '100001'.
iw_data-amount = 100.
APPEND iw_data TO it_data.

iw_data-amount = 200.
APPEND iw_data TO it_data.

iw_data-amount = 300.
APPEND iw_data TO it_data.

iw_data-amount = 400.
APPEND iw_data TO it_data.

iw_data-kunnr = '100002'.
iw_data-amount = 400.
APPEND iw_data TO it_data.

iw_data-amount = 200.
APPEND iw_data TO it_data.

SORT it_data BY kunnr.

LOOP AT it_data INTO iw_data.
  w_sum_kun = w_sum_kun + 1.
  iw_out-kunnr = iw_data-kunnr.
  iw_out-amount = iw_out-amount + iw_data-amount.
  IF w_sum_kun > 1.
    CLEAR iw_data-kunnr.
  ENDIF.
  APPEND iw_data TO it_out.
  AT END OF kunnr.
    iw_out-kunnr = 'Avearge'.
    iw_out-subtotal = iw_out-amount.
    iw_out-amount = iw_out-amount DIV w_sum_kun.
    iw_out-color = 'C310'.
    APPEND iw_out TO it_out.

    iw_out-kunnr = 'Sub total'.
    iw_out-amount = iw_out-subtotal.
    APPEND iw_out TO it_out.
    CLEAR: w_sum_kun,iw_out.
  ENDAT.
ENDLOOP.

iw_fcat-fieldname = 'KUNNR'.
iw_fcat-ref_tabname = 'KNA1'.
APPEND iw_fcat TO it_fcat.

CLEAR iw_fcat.
iw_fcat-fieldname = 'AMOUNT'.
iw_fcat-seltext_m = 'AMOUNT'.
APPEND iw_fcat TO it_fcat.

iw_layout-info_fieldname = 'COLOR'.
iw_layout-colwidth_optimize = 'X'.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
  EXPORTING
    i_callback_program = sy-repid
    is_layout          = iw_layout
    it_fieldcat        = it_fcat
  TABLES
    t_outtab           = it_out
  EXCEPTIONS
    program_error      = 1
    OTHERS             = 2.
IF sy-subrc <> 0.

ENDIF.

[Former Member]

HI Tarun

how to set do_sum = 'X' or do_sum = 'C'. with same column

[I355602]

Hi,

>

> HI Tarun
>    how to set do_sum = 'X' or do_sum = 'C'. with same column

You cant set do_sum = 'X' and do_sum = 'C' for the same column simultaneously.

Only one of them can be used at a time with one column.

Regards,

Tarun

[Former Member]

hi,

i am having the same problem, but with ALV List Display.

I have done this--

wa_disptab_field-col_pos = 5.

wa_disptab_field-fieldname = 'RATE'.

wa_disptab_field-ref_tabname = 'IT_DISPTAB'.

wa_disptab_field-seltext_m = 'RS PER KL'.

wa_disptab_field-hotspot = 'X'.

wa_disptab_field-do_sum = 'C'.

wa_disptab_field-datatype = 'QUAN'.

APPEND wa_disptab_field TO it_disptab_field.

But I am not getting the average for that column. Please Help.

Thankss...

Edited by: debarati.04 on Aug 30, 2011 6:24 AM