Skip to Content
author's profile photo Former Member
Former Member

Dynamically Displaying row data as colum in ALV

Dear Experts,

Please find herewith below my code. In this the column MONAT displays Month. Now scenario is as follows:

1. In the selection screen user inputs the YEAR & MONTH for which the report is to be displayed.

2. With reference to that the system should display VALUES in the MONTH column DYNAMICALLY.

For Ex: Currently my data is coming as follows:

CompanyCode	Year	Month	GLCode	CostCenter	Amt
1000		2008	MAR	aaaa	xxxx		1000
1000		2008	MAR	aaaa	xxxx		2000
1000		2008	MAR	aaaa	xxxx		3000
1000		2008	MAR	aaaa	xxxx		4000

1000		2008	MAR	bbbb	yyyy		1000
1000		2008	MAR	bbbb	yyyy		1000

1000		2008	MAR	cccc	yyyy		1000

1000		2008	APR	aaaa	xxxx		2000
1000		2008	APR	aaaa	xxxx		3000

1000		2008	APR	bbbb	xxxx		4000

I want the data as follows:

CompanyCode	Year	MAR	APR	GLCode	CostCenter	
1000		2008	10000	5000	aaaa	xxxx		

1000		2008	2000	0.00	bbbb	yyyy		

1000		2008	1000	0.00	cccc	yyyy		

1000		2008	0.00	4000	bbbb	xxxx		

The month should get generated dynamically.

My Code:

REPORT  z_co_expenses.

TYPE-POOLS: slis.

TABLES: bsis, "GL Table
        csks, "Cost Center Master Data
        cska. "Cost Elements(Dependent on Chart of Accounts).

DATA: BEGIN OF it_bsis OCCURS 0,
      bukrs TYPE bsis-bukrs	, "Company Code
      hkont TYPE bsis-hkont	, "General Ledger Account
      gjahr TYPE bsis-gjahr	, "Fiscal Year
      monat TYPE bsis-monat	, "Fiscal Period
      shkzg TYPE bsis-shkzg	, "Debit/Credit Indicator
      dmbtr TYPE bsis-dmbtr	, "Amount in Local Currency
      wrbtr TYPE bsis-wrbtr, "Amount in document currency
      kostl TYPE bsis-kostl	, "Cost Center
      END OF it_bsis.

DATA: BEGIN OF it_data OCCURS 0,
      bukrs TYPE bsis-bukrs	, "Company Code
      hkont TYPE bsis-hkont	, "General Ledger Account
      gjahr TYPE bsis-gjahr	, "Fiscal Year
      monat TYPE bsis-monat	, "Fiscal Period
      shkzg TYPE bsis-shkzg	, "Debit/Credit Indicator
      dmbtr TYPE bsis-dmbtr	, "Amount in Local Currency
      wrbtr TYPE bsis-wrbtr, "Amount in document currency
      kostl TYPE bsis-kostl	, "Cost Center
      dr_val TYPE bsis-dmbtr,
      cr_val TYPE bsis-dmbtr,
      END OF it_data.

DATA: BEGIN OF it_final OCCURS 0,
      bukrs TYPE bsis-bukrs	, "Company Code
      hkont TYPE bsis-hkont	, "General Ledger Account
      gjahr TYPE bsis-gjahr	, "Fiscal Year
      monat TYPE bsis-monat	, "Fiscal Period
      dmbtr TYPE bsis-dmbtr	, "Amount in Local Currency
      wrbtr TYPE bsis-wrbtr, "Amount in document currency
      kostl TYPE bsis-kostl	, "Cost Center
      dr_val TYPE bsis-dmbtr,
      cr_val TYPE bsis-dmbtr,
      count(30) TYPE c,
      END OF it_final.

DATA: BEGIN OF it_cost OCCURS 0,
      bukrs TYPE bsis-bukrs	, "Company Code
      hkont TYPE bsis-hkont	, "General Ledger Account
      gjahr TYPE bsis-gjahr	, "Fiscal Year
      monat TYPE bsis-monat	, "Fiscal Period
      dmbtr TYPE bsis-dmbtr	, "Amount in Local Currency
      wrbtr TYPE bsis-wrbtr, "Amount in document currency
      kostl TYPE bsis-kostl	, "Cost Center
      final_val TYPE bsis-dmbtr,
      count(30) TYPE c,
      END OF it_cost.

DATA: it_fieldcat TYPE slis_t_fieldcat_alv,
      wa_layout TYPE slis_layout_alv,
      it_header   TYPE slis_t_listheader,
      wa_header   LIKE LINE OF it_header,
      it_events TYPE slis_t_event,
      wa_events LIKE LINE OF it_events.

****************************************************************************************
* selection screen *
********************

SELECTION-SCREEN BEGIN OF BLOCK a WITH FRAME TITLE text-030.

SELECT-OPTIONS:  so_bukrs FOR bsis-bukrs,  "Company Code
                 so_gjahr FOR bsis-gjahr,      "Fiscal Year
                 so_monat FOR bsis-monat,      "Fiscal Period
                 so_kostl FOR bsis-kostl,      "Cost Center
                 so_hkont FOR bsis-hkont.      "Cost Element

SELECTION-SCREEN END OF BLOCK a.

****************************************************************************************
*START_OF_SELECTION  *
**********************

START-OF-SELECTION.

  PERFORM get_data.

  PERFORM fill_catalog.

  PERFORM display_data.

END-OF-SELECTION.
*&---------------------------------------------------------------------*
*&      Form  GET_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM get_data .

  SELECT
        bukrs
        hkont
        gjahr
        monat
        shkzg
        SUM( dmbtr )
        SUM( wrbtr )
        kostl
        FROM bsis
        INTO TABLE it_bsis
        WHERE
        bukrs IN so_bukrs AND
        gjahr IN so_gjahr AND
        monat IN so_monat AND
        kostl IN so_kostl AND
        hkont IN ( SELECT kstar FROM cska WHERE  kstar IN so_hkont AND ktopl = '1000' )
        GROUP BY gjahr monat bukrs shkzg hkont kostl.

  SORT it_bsis BY bukrs gjahr monat hkont shkzg kostl.

  IF it_bsis[] IS NOT INITIAL.

    LOOP AT it_bsis.

      it_data-bukrs = it_bsis-bukrs.
      it_data-hkont = it_bsis-hkont.
      it_data-gjahr = it_bsis-gjahr.
      it_data-monat = it_bsis-monat.
      it_data-shkzg = it_bsis-shkzg.
      it_data-kostl = it_bsis-kostl.

      IF it_data-shkzg = 'H'.
        it_data-cr_val = it_data-cr_val + it_bsis-dmbtr.
      ENDIF.

      IF it_data-shkzg = 'S'.
        it_data-dr_val = it_data-dr_val + it_bsis-dmbtr.
      ENDIF.

      APPEND it_data.
      CLEAR it_data.

    ENDLOOP.

    SORT it_data BY bukrs gjahr monat hkont kostl.


    LOOP AT it_data.


      MOVE: it_data-bukrs TO it_final-bukrs,
            it_data-hkont TO it_final-hkont,
            it_data-gjahr TO it_final-gjahr,
            it_data-monat TO it_final-monat,
            it_data-kostl TO it_final-kostl,
            it_data-dr_val TO it_final-dr_val,
            it_data-cr_val TO it_final-cr_val.

*      CONCATENATE it_data-bukrs it_data-gjahr it_data-monat it_data-hkont it_data-kostl INTO it_final-count.
       collect it_final.

      APPEND it_final.
      CLEAR it_final.

    ENDLOOP.

    SORT it_final BY  count.

    DATA: w_bukrs TYPE bsis-bukrs,
          w_hkont TYPE bsis-hkont,
          w_gjahr TYPE bsis-gjahr,
          w_kostl TYPE bsis-kostl,
          w_monat TYPE bsis-monat.

    LOOP AT it_final.

      w_bukrs = it_final-bukrs.
      w_hkont = it_final-hkont.
      w_gjahr = it_final-gjahr.
      w_kostl = it_final-kostl.
      w_monat = it_final-monat.

      AT END OF monat.
      sum.
        it_cost-bukrs = w_bukrs.
        it_cost-gjahr = w_gjahr.
        it_cost-monat = w_monat.
        it_cost-hkont = w_hkont.
        it_cost-kostl = w_kostl.
        it_cost-final_val = it_final-dr_val - it_final-cr_val.

      ENDAT.

      CLEAR: w_bukrs, w_gjahr, w_monat, w_hkont, w_kostl.

    ENDLOOP.

  ENDIF.

ENDFORM.                    " GET_DATA
*&---------------------------------------------------------------------*
*&      Form  FILL_CATALOG
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM fill_catalog .

  data lwa_fieldcat type slis_fieldcat_alv.

  wa_layout-zebra = 'X'.
  wa_layout-colwidth_optimize = 'X'.

  clear lwa_fieldcat.
  lwa_fieldcat-fieldname = 'BUKRS'.
  lwa_fieldcat-seltext_m = 'Company Code'.
  append lwa_fieldcat to it_fieldcat.

  clear lwa_fieldcat.
  lwa_fieldcat-fieldname = 'GJAHR'.
  lwa_fieldcat-seltext_m = 'Year'.
  append lwa_fieldcat to it_fieldcat.

  clear lwa_fieldcat.
  lwa_fieldcat-fieldname = 'MONAT'.
  lwa_fieldcat-seltext_m = 'Month'.
  append lwa_fieldcat to it_fieldcat.

  clear lwa_fieldcat.
  lwa_fieldcat-fieldname = 'HKONT'.
  lwa_fieldcat-seltext_m = 'Cost Element'.
  append lwa_fieldcat to it_fieldcat.

  clear lwa_fieldcat.
  lwa_fieldcat-fieldname = 'KOSTL'.
  lwa_fieldcat-seltext_m = 'Cost Center'.
  append lwa_fieldcat to it_fieldcat.

  clear lwa_fieldcat.
  lwa_fieldcat-fieldname = 'FINAL_VAL'.
  lwa_fieldcat-seltext_m = 'VALUE'.
  append lwa_fieldcat to it_fieldcat.


ENDFORM.                    " FILL_CATALOG
*&---------------------------------------------------------------------*
*&      Form  DISPLAY_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM display_data .

ENDFORM.                    " DISPLAY_DATA

Please tell me how to go about... As usual your expert suggestions are most valued.

Also any suggestion for writing report in different way is also welcomed.

Regards,

Jitesh

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Feb 25, 2009 at 06:01 AM

    hi,

    you can do this by using dynamic internal table following is the code:

    data pos type i.

    fcat type slis_t_fieldcat_alv,

    it_fieldcat type lvc_t_fcat,

    is_fieldcat like line of it_fieldcat,

    ist like line of it_fieldcat.

    *field catalog for dynamic internal table

    clear ist.

    ist-inttype = 'C'.

    ist-intlen = '50'.

    ist-fieldname = 'MATNR'.

    append ist to it_fieldcat.

    clear ist.

    ist-inttype = 'C'.

    ist-intlen = '50'.

    ist-fieldname = 'EQUNR'.

    append ist to it_fieldcat.

    clear ist.

    ist-inttype = 'C'.

    ist-intlen = '50'.

    ist-fieldname = 'MSGRP'.

    append ist to it_fieldcat.

    clear ist.

    ist-inttype = 'C'.

    ist-intlen = '50'.

    ist-fieldname = 'EQFNR'.

    append ist to it_fieldcat.

    clear ist.

    ist-inttype = 'C'.

    ist-intlen = '50'.

    ist-fieldname = 'TXT04'.

    append ist to it_fieldcat.

    clear ist.

    ist-inttype = 'C'.

    ist-intlen = '50'.

    ist-fieldname = 'TXT30'.

    append ist to it_fieldcat.

    *field catalog for alv grid

    ws_alvfield-col_pos = 1.

    ws_alvfield-fieldname = 'MATNR'.

    ws_alvfield-seltext_l = text-t01. "'Material'.

    ws_alvfield-outputlen = 18.

    append ws_alvfield to wt_fieldcat.

    clear ws_alvfield.

    ws_alvfield-col_pos = 2.

    ws_alvfield-fieldname = 'EQUNR'.

    ws_alvfield-seltext_l = text-t02. "'Equip No'.

    ws_alvfield-outputlen = 18.

    append ws_alvfield to wt_fieldcat.

    clear ws_alvfield.

    ws_alvfield-col_pos = 3.

    ws_alvfield-fieldname = 'MSGRP'.

    ws_alvfield-seltext_l = text-t04. "'Room

    ws_alvfield-outputlen = 8.

    append ws_alvfield to wt_fieldcat.

    clear ws_alvfield.

    ws_alvfield-col_pos = 4.

    ws_alvfield-fieldname = 'EQFNR'.

    ws_alvfield-seltext_l = text-t09. "'Room

    ws_alvfield-outputlen = 30.

    append ws_alvfield to wt_fieldcat.

    clear ws_alvfield.

    ws_alvfield-col_pos = 5.

    ws_alvfield-fieldname = 'TXT04'.

    ws_alvfield-seltext_l = text-t07. "'Room

    ws_alvfield-outputlen = 5.

    append ws_alvfield to wt_fieldcat.

    clear ws_alvfield.

    ws_alvfield-col_pos = 6.

    ws_alvfield-fieldname = 'TXT30'.

    ws_alvfield-seltext_l = text-t08. "'Room

    ws_alvfield-outputlen = 30.

    append ws_alvfield to wt_fieldcat.

    clear ws_alvfield.

    pos = 7.

    *create dynamic columns for language

    loop at r_spras.

    ist-inttype = 'C'.

    ist-intlen = '80'.

    on change of r_spras-low.

    select single SPTXT into t002t-sptxt

    from t002t where spras = 'EN' and sprsl = r_spras-low .

    translate t002t-sptxt to upper case.

    move t002t-sptxt tO ist-fieldname. "r_spras-low

    append ist to it_fieldcat.

    *--- field catalog for alv grid

    ws_alvfield-col_pos = POS.

    ws_alvfield-fieldname = ist-fieldname.

    ws_alvfield-seltext_l = ist-fieldname.

    ws_alvfield-just = 'L'.

    append ws_alvfield to wt_fieldcat.

    clear ws_alvfield.

    pos = pos + 1.

    endon.

    endloop.

    *********************************************************************

    • CREATE DYNAMIC TABLE *

    *********************************************************************

    data : new_table type ref to data,

    new_line type ref to data.

    CALL METHOD cl_alv_table_create=>create_dynamic_table

    EXPORTING

    it_fieldcatalog = it_fieldcat

    IMPORTING

    ep_table = new_table.

    assign new_table->* to <TAB>.

    create data new_line like line of <TAB>.

    assign new_line->* to <WA>.

    sort wt_teqkt by equnr spras.

    CLEAR <WA>.

    *assign the data to the created dynamic table <TAB>

    loop at wt_final into wa_final.

    assign component 1 of structure <WA> to <F>.

    <F> = wa_final-matnr.

    assign component 2 of structure <WA> to <F>.

    <F> = wa_final-equnr.

    assign component 3 of structure <WA> to <F>.

    <F> = wa_final-msgrp.

    assign component 4 of structure <WA> to <F>.

    <F> = wa_final-eqfnr.

    assign component 5 of structure <WA> to <F>.

    <F> = wa_final-txt04.

    assign component 6 of structure <WA> to <F>.

    <F> = wa_final-txt30.

    *move description according to the language

    loop at r_spras.

    loop at wt_teqkt into wa_teqkt where equnr = wa_final-equnr

    and spras = r_spras-low.

    select single SPTXT into t002t-sptxt

    from t002t where spras = 'EN' and sprsl = r_spras-low.

    translate t002t-sptxt to upper case.

    assign component t002t-sptxt of structure <WA> to <F>.

    <F> = wa_teqkt-eqktx.

    clear wa_teqkt.

    endloop.

    endloop.

    append <WA> to <TAB>.

    clear <WA>.

    endloop.

    ENDFORM. " BUILD_FCAT

    thanks,

    anupama

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 25, 2009 at 12:01 PM

    Hi,

    Check the below code, this will work for your requirement.

    Here I created a int tab named it_tab of similar structure of it_bsis and

    it_final which is in the required output format.

    TYPE-POOLS: slis.
    
    TABLES: bsis."GL Table
    
    DATA: it_fieldcat TYPE slis_t_fieldcat_alv,
          wa_layout TYPE slis_layout_alv.
    
    "Table which has the required values
    DATA: BEGIN OF it_tab OCCURS 0,
          bukrs TYPE bsis-bukrs	, "Company Code
          gjahr TYPE bsis-gjahr	, "Fiscal Year
          monat TYPE char3	, "Fiscal Period
          hkont TYPE bsis-hkont	, "General Ledger Account
          kostl TYPE bsis-kostl	, "Cost Center
          dmbtr TYPE bsis-dmbtr	, "Amount in Local Currency
          END OF it_tab.
    
    "Table which is in the required output format with months as fields
    "All months are used as the month may be anything
    DATA: BEGIN OF it_final OCCURS 0,
          bukrs TYPE bsis-bukrs	, "Company Code
          gjahr TYPE bsis-gjahr	, "Fiscal Year
          jan TYPE bsis-dmbtr,
          feb TYPE bsis-dmbtr,
          mar TYPE bsis-dmbtr,
          apr TYPE bsis-dmbtr,
          may TYPE bsis-dmbtr,
          jun TYPE bsis-dmbtr,
          jul TYPE bsis-dmbtr,
          aug TYPE bsis-dmbtr,
          sep TYPE bsis-dmbtr,
          oct TYPE bsis-dmbtr,
          nov TYPE bsis-dmbtr,
          dec TYPE bsis-dmbtr,
          hkont TYPE bsis-hkont	, "General Ledger Account
          kostl TYPE bsis-kostl	, "Cost Center
          END OF it_final.
    
    DATA: w_total TYPE bsis-dmbtr, "Total value for each month
          w_ind TYPE i. "Table index
    FIELD-SYMBOLS: <fs_amt> TYPE ANY.
    
    "Fill the it_tab according to your logic
    
    SORT it_tab BY bukrs hkont ASCENDING monat DESCENDING.
    
    LOOP AT it_tab.
      ADD it_tab-dmbtr TO w_total. "Add the total
      AT END OF kostl.
        MOVE-CORRESPONDING it_tab TO it_final. "Move the other values except month and amount
        ASSIGN COMPONENT it_tab-monat OF STRUCTURE it_final TO <fs_amt>. "Assign the field of current month
        <fs_amt> = w_total. "Fill the field month with the total amount
        READ TABLE it_final WITH KEY bukrs = it_tab-bukrs
                                   gjahr = it_tab-gjahr
                                   hkont = it_tab-hkont
                                   kostl = it_tab-kostl
                                   TRANSPORTING NO FIELDS. "Check whether the entry already exists
        w_ind = sy-tabix.
        IF sy-subrc EQ 0.
          "If exists modify the value for the current month
          MODIFY it_final INDEX w_ind TRANSPORTING (it_tab-monat).
        ELSE.
          "Other wise append the entry
          APPEND it_final.
        ENDIF.
        CLEAR: w_total, it_final.
      ENDAT.
    ENDLOOP.
    
    PERFORM fill_catalog.
    PERFORM display_data.
    
    *&---------------------------------------------------------------------*
    *&      Form  fill_catalog
    *&---------------------------------------------------------------------*
    *       text
    *----------------------------------------------------------------------*
    FORM fill_catalog.
      DATA lwa_fieldcat TYPE slis_fieldcat_alv.
    
      wa_layout-zebra = 'X'.
      wa_layout-colwidth_optimize = 'X'.
    
      CLEAR lwa_fieldcat.
      lwa_fieldcat-fieldname = 'BUKRS'.
      lwa_fieldcat-seltext_m = 'Company Code'.
      APPEND lwa_fieldcat TO it_fieldcat.
    
      CLEAR lwa_fieldcat.
      lwa_fieldcat-fieldname = 'GJAHR'.
      lwa_fieldcat-seltext_m = 'Year'.
      APPEND lwa_fieldcat TO it_fieldcat.
    
      "Fill the fieldcat with the month values
      "Month which are not available will not be filled
      LOOP AT it_tab.
        AT END OF kostl.
          READ TABLE it_fieldcat WITH KEY fieldname = it_tab-monat
          TRANSPORTING NO FIELDS.
          IF sy-subrc NE 0.
            "Append the field names only if they not exist
            CLEAR lwa_fieldcat.
            lwa_fieldcat-fieldname = it_tab-monat.
            lwa_fieldcat-seltext_m = it_tab-monat. "I used field name as the description
            APPEND lwa_fieldcat TO it_fieldcat.
          ENDIF.
        ENDAT.
      ENDLOOP.
      CLEAR lwa_fieldcat.
      lwa_fieldcat-fieldname = 'HKONT'.
      lwa_fieldcat-seltext_m = 'Cost Element'.
      APPEND lwa_fieldcat TO it_fieldcat.
    
      CLEAR lwa_fieldcat.
      lwa_fieldcat-fieldname = 'KOSTL'.
      lwa_fieldcat-seltext_m = 'Cost Center'.
      APPEND lwa_fieldcat TO it_fieldcat.
    
    
    ENDFORM.                    "fill_catalog
    
    *&---------------------------------------------------------------------*
    *&      Form  display_data
    *&---------------------------------------------------------------------*
    *       text
    *----------------------------------------------------------------------*
    FORM display_data.
      CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
        EXPORTING
          i_callback_program = sy-repid
          is_layout          = wa_layout
          it_fieldcat        = it_fieldcat
        TABLES
          t_outtab           = it_final
        EXCEPTIONS
          OTHERS             = 1.
      IF sy-subrc <> 0.
    * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
    *         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
      ENDIF.
    
    ENDFORM.                    "display_data

    Revert in case of issues.

    Regards,

    Manoj Kumar P

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.