Community
Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

Pivot Table

Former Member

‎2008 Jan 23 12:52 PM

0 Likes
1,358

Hi all,

Let me know can we obtain report output in the form of the pivot table, rather than the simple list and ALV grid.

I am working on the ECC5 version and windows 2007.

Thanks in Advance.....!

Brahma.

Hi all,

Let me know can we obtain report output in the form of the pivot table, rather than the simple list and ALV grid.

I am working on the ECC5 version and windows 2007.

Thanks in Advance.....!

Brahma.

Reply
3 REPLIES 3
Read only

Former Member

‎2008 Jan 23 1:09 PM

0 Likes
747

hi

good

Example below reates a Pivot in Excel direct from a SAP internal table.

The pivot interface object should be created soon after creating the document and spreadsheet interfaces object. Otherwise often it is not successful and returns a null pivot handle.

The Internal table should have a header line otherwise DYN_ANALYSE_SINGLE will fail.

This example should make it very easy to do Excel Integration.


Report ZExcelTest Message-id ym.

DATA spreadsheetintf TYPE REF TO i_oi_spreadsheet.
DATA documentintf    TYPE REF TO i_oi_document_proxy.
DATA pivotintf       TYPE REF TO i_oi_pivot.

DATA zjncerror       TYPE REF TO i_oi_error.
DATA zjncretcode     TYPE SOI_RET_STRING.

DATA: numRows    type I,
      maxRows    type I.

DATA: usa_sales TYPE i VALUE 1000,
      europe_sales TYPE i VALUE 2000,
      japan_sales TYPE i VALUE 1000,
      asia_sales TYPE i VALUE 100,
      america_sales TYPE i VALUE 100,
      africa_sales TYPE i VALUE 100.

  DATA: BEGIN OF head_table Occurs 0,
             hd_region(10),
             hd_sales(10),
             hd_date(10),
             hd_time(10),
             hd_weight(10),
             hd_amount(10),
             hd_id(10),
        END OF head_table.

  DATA: BEGIN OF sales_table Occurs 0,
             region(60),
             sales TYPE i,
             date  TYPE d,
             time  TYPE t,
             weight TYPE f,
             amount TYPE p DECIMALS 3,
             id(10) TYPE n,
        END OF sales_table.


  DATA: ind TYPE i.

Data: Begin of WA_PIVOT,
        region(60),
        date   TYPE d,
        amount TYPE p DECIMALS 3,
      End of   WA_PIVOT.

* Qty Internal Table for Date-wise Plant-Wise
Data: IT_PIVOT Like Sorted Table Of WA_PIVOT With Header Line
                  With Unique Key region date.

Data: DT_PIVOT     Like Table Of WA_PIVOT With Header Line.

Data: IT_ITEMS     type  SOI_NAMETYPE_TABLE.

Data: WA_ITEMS     like line of IT_ITEMS.

DATA: WA_NAMECOL   TYPE SOI_NAMECOL_ITEM.

DATA: NAMECOL      TYPE SOI_NAMECOL_TABLE.

DATA: WA_FIELD     LIKE RFC_FIELDS.

DATA: FIELDS_TABLE TYPE TABLE OF RFC_FIELDS.

DATA: OFFSET    LIKE RFC_FIELDS-OFFSET VALUE 0.
DATA: N         TYPE I.
DATA: POS       TYPE I VALUE 1.
DATA: ALIGNMENT TYPE I.

  CLEAR: sales_table.

  sales_table-region = 'America'(ame).
  sales_table-sales = america_sales.
  APPEND sales_table.

  sales_table-region = 'Africa'(afr).
  sales_table-sales = africa_sales.
  APPEND sales_table.

  sales_table-region = 'USA'(usa).
  sales_table-sales = usa_sales.
  APPEND sales_table.

  sales_table-region = 'Europe'(eur).
  sales_table-sales = europe_sales.
  APPEND sales_table.

  sales_table-region = 'Japan'(jap).
  sales_table-sales = japan_sales.
  APPEND sales_table.

  sales_table-region = 'Asia'(asi).
  sales_table-sales = asia_sales.
  APPEND sales_table.

  LOOP AT sales_table.
    ind = sy-tabix.
    sales_table-date = sy-datum + ind.
    sales_table-time = sy-uzeit + ind.
    sales_table-weight = 100000 * ind.
    sales_table-amount = 11111 * ind.
    sales_table-id = ind.
    MODIFY sales_table.
  ENDLOOP.

  Describe Table  sales_table Lines numRows.

  LOOP AT sales_table.
    Move sales_table-region  to WA_PIVOT-region.
    Move sales_table-date    to WA_PIVOT-date.
    Move sales_table-amount  to WA_PIVOT-amount.
    Collect WA_PIVOT Into IT_PIVOT.
  ENDLOOP.

  DT_PIVOT[] = IT_PIVOT[].

  CALL FUNCTION 'ZJNC_START_EXCEL'
    IMPORTING
      SPREADSHEETINTF       =  SPREADSHEETINTF
      DOCUMENTINTF          =  DOCUMENTINTF.

  If documentintf    Is Initial
  Or spreadsheetintf Is Initial.
     message e999 with 'NULL document/spreadsheet interfaces'.
  EndIf.

  CALL METHOD documentintf->get_pivot_interface
       EXPORTING    no_flush        = ' '
       IMPORTING    pivot_interface = pivotintf
                    error           = zjncerror
                    retcode         = zjncretcode.

  If zjncretcode <> c_oi_errors=>ret_ok.
       CALL METHOD c_oi_errors=>raise_message
           EXPORTING type   = 'E'.
  EndIf.

  If pivotintf Is Initial.
     message e999 with 'NULL pivot interface'.
  EndIf.

  PERFORM DYN_ANALYSE_SINGLE TABLES    FIELDS_TABLE
                             USING     DT_PIVOT
                             CHANGING  POS N OFFSET ALIGNMENT.

  WA_NAMECOL-NAME = 'REGION'.
  WA_NAMECOL-COLUMN = '1'.
  APPEND WA_NAMECOL TO NAMECOL.
  WA_NAMECOL-NAME = 'DATE'.
  WA_NAMECOL-COLUMN = '2'.
  APPEND WA_NAMECOL TO NAMECOL.
  WA_NAMECOL-NAME = 'AMOUNT'.
  WA_NAMECOL-COLUMN = '3'.
  APPEND WA_NAMECOL TO NAMECOL.

  LOOP AT NAMECOL INTO WA_NAMECOL.
    READ TABLE FIELDS_TABLE INTO WA_FIELD INDEX WA_NAMECOL-COLUMN.
    IF SY-SUBRC EQ 0.
      WA_FIELD-FIELDNAME = WA_NAMECOL-NAME.
      MODIFY FIELDS_TABLE INDEX WA_NAMECOL-COLUMN FROM WA_FIELD.
    ENDIF.
  ENDLOOP.

  Move 'REGION' to WA_ITEMS-name.
  Move i_oi_pivot=>rowfield  to  WA_ITEMS-type.
  Append WA_ITEMS to IT_ITEMS.

  Move 'DATE' to WA_ITEMS-name.
  Move i_oi_pivot=>columnfield   to  WA_ITEMS-type.
  Append WA_ITEMS to IT_ITEMS.

  Move 'AMOUNT' to WA_ITEMS-name.
  Move i_oi_pivot=>datafield  to  WA_ITEMS-type.
  Append WA_ITEMS to IT_ITEMS.


  CALL METHOD pivotintf->set_source_table
       EXPORTING data_table   = DT_PIVOT[]
                 fields_table = FIELDS_TABLE[]
                 name         = 'PlanningPivot'
                 items        = IT_ITEMS[]
       IMPORTING error        = zjncerror
                 retcode      = zjncretcode.


  CLEAR: head_table.

  Head_Table-hd_region = 'Region'.
  Head_Table-hd_sales = 'Sales'.
  Head_Table-hd_date = 'Date'.
  Head_Table-hd_time = 'Time'.
  Head_Table-hd_weight = 'Weight in MT'.
  Head_Table-hd_amount = 'Value in Rupees'.
  Head_Table-hd_id = 'Sytem ID'.

  Append Head_Table.

  CALL FUNCTION 'ZJNC_ADD_SHEET'
    EXPORTING
      PSHEET                = 'Sheet ONE'
      SPREADSHEETINTF       = spreadsheetintf.

  maxRows = 1.

  CALL FUNCTION 'ZJNC_ADD_RANGE'
    EXPORTING
      PRANGE                = 'HeadRange1'
      STARTROW              = maxRows
      STARTCOL              = 1
      NUMROWS               = 1
      NUMCOLS               = 7
      PSHEET                = 'Sheet ONE'
      SPREADSHEETINTF       = spreadsheetintf.

* In ABAP Objects, you can only declare tables without headers.
* Hence sales_table[] ensures Header is Stripped

  CALL FUNCTION 'ZJNC_ADD_TABLE'
    EXPORTING
      PTABLE                = head_table[]
      PRANGE                = 'HeadRange1'
*     PSIZE                 = -1
      PBOLD                 = 1
*     PITALIC               = -1
*     PALIGN                = -1
*     PFRONT                = -1
*     PBACK                 = -1
*     PFORMAT               = 'NA'
      SPREADSHEETINTF       = spreadsheetintf.

  Add 1 to maxrows.

  Describe Table  sales_table Lines numRows.

  CALL FUNCTION 'ZJNC_ADD_RANGE'
    EXPORTING
      PRANGE                = 'DataRange1'
      STARTROW              = maxRows
      STARTCOL              = 1
      NUMROWS               = numRows
      NUMCOLS               = 7
      PSHEET                = 'Sheet ONE'
      SPREADSHEETINTF       = spreadsheetintf.

  CALL FUNCTION 'ZJNC_ADD_TABLE'
    EXPORTING
      PTABLE                = sales_table[]
      PRANGE                = 'DataRange1'
*     PSIZE                 = -1
      PBOLD                 = 0
*     PITALIC               = -1
*     PALIGN                = -1
      PFRONT                = 3
*     PBACK                 = -1
*     PFORMAT               = 'NA'
      SPREADSHEETINTF       = spreadsheetintf.

* Start NewSheet on TOP
  Move 1 to maxRows.

  CALL FUNCTION 'ZJNC_ADD_SHEET'
    EXPORTING
      PSHEET                = 'Sheet TWO'
      SPREADSHEETINTF       = spreadsheetintf.

  CALL FUNCTION 'ZJNC_ADD_RANGE'
    EXPORTING
      PRANGE                = 'HeadRange2'
      STARTROW              = maxRows
      STARTCOL              = 1
      NUMROWS               = 1
      NUMCOLS               = 7
      PSHEET                = 'Sheet TWO'
      SPREADSHEETINTF       = spreadsheetintf.

* In ABAP Objects, you can only declare tables without headers.
* Hence sales_table[] ensures Header is Stripped

  CALL FUNCTION 'ZJNC_ADD_TABLE'
    EXPORTING
      PTABLE                = head_table[]
      PRANGE                = 'HeadRange2'
*     PSIZE                 = -1
      PBOLD                 = 1
*     PITALIC               = -1
*     PALIGN                = -1
*     PFRONT                = -1
*     PBACK                 = -1
*     PFORMAT               = 'NA'
      SPREADSHEETINTF       = spreadsheetintf.

  Add 1 to maxrows.

  CALL FUNCTION 'ZJNC_ADD_RANGE'
    EXPORTING
      PRANGE                = 'DataRange2'
      STARTROW              = maxRows
      STARTCOL              = 1
      NUMROWS               = numRows
      NUMCOLS               = 7
      PSHEET                = 'Sheet TWO'
      SPREADSHEETINTF       = spreadsheetintf.

  CALL FUNCTION 'ZJNC_ADD_TABLE'
    EXPORTING
      PTABLE                = sales_table[]
      PRANGE                = 'DataRange2'
*     PSIZE                 = -1
      PBOLD                 = 0
*     PITALIC               = -1
*     PALIGN                = -1
      PFRONT                = 55
      PBACK                 = 6
*     PFORMAT               = 'NA'
      SPREADSHEETINTF       = spreadsheetintf.

  CALL METHOD documentintf->save_as
     EXPORTING file_name   = 'c:\jnc.xls'.


  CALL FUNCTION 'POPUP_TO_INFORM'
   EXPORTING
        titel = sy-repid
        txt2  = 'See EXCEL & SAVE if Needed'
        txt1  = 'Jai Hind ....'.

*&---------------------------------------------------------------------*
*&      Form  DYN_ANALYSE_SINGLE
*&---------------------------------------------------------------------*
FORM DYN_ANALYSE_SINGLE TABLES   P_FIELDS STRUCTURE RFC_FIELDS
                        USING    P_DATA
                        CHANGING POS TYPE I
                                 LEN TYPE I
                                 OFFSET LIKE RFC_FIELDS-OFFSET
                                 ALIGNMENT_STRUCT.

  DATA : TYP(1) TYPE C, DECS TYPE I.
  DATA : N_COMPS TYPE I.
  DATA : NN_COMPS TYPE I.
  DATA : ALIGNMENT TYPE I.
  DATA : MAX_ALIGNMENT TYPE I VALUE 1.
  DATA : THISFIELDS TYPE RFC_FIELDS OCCURS 0 WITH HEADER LINE.
  DATA : DELTA TYPE I.
  DATA : OLDOFFSET TYPE I.
  DATA : DATATYPE TYPE C.
  DATA : DATALEN TYPE I.
  DATA : DATADEC TYPE I.

  FIELD-SYMBOLS : <F>.

* First check whether we have a structure
  DESCRIBE FIELD P_DATA TYPE DATATYPE COMPONENTS N_COMPS
                        LENGTH DATALEN IN BYTE MODE DECIMALS DATADEC.
  IF N_COMPS > 0.

    DO.
      ASSIGN COMPONENT SY-INDEX OF STRUCTURE P_DATA TO <F>.
      IF SY-SUBRC <> 0.
        ALIGNMENT_STRUCT = MAX_ALIGNMENT.
* CORRECT OFFSET FOR INNER STRUCTURES
        IF MAX_ALIGNMENT > 1.
* loop at all fields an correct offset according to alignment of
* the field type and the alignment of entire structure
          LOOP AT THISFIELDS.
            OLDOFFSET = THISFIELDS-OFFSET.
* DELTA always contains the current shift already done.
            THISFIELDS-OFFSET = THISFIELDS-OFFSET + DELTA.
            IF SY-TABIX = 1.
* First element is aligned according to max alignment of any element
* in structure.
              PERFORM OFFSET_CORRECTION USING
                                            ' '
                                            MAX_ALIGNMENT
                                        CHANGING
                                            THISFIELDS-OFFSET.
            ELSE.
* All subsequent elements are aligned according to their typ.
              PERFORM OFFSET_CORRECTION USING
                                            THISFIELDS-EXID
                                            0
                                        CHANGING
                                             THISFIELDS-OFFSET.
            ENDIF.
* remember current shift in DELTA
            DELTA = THISFIELDS-OFFSET - OLDOFFSET.
            MODIFY THISFIELDS.
          ENDLOOP.
* new global offset for next fields must be at least latest offset + 1
          OFFSET =  THISFIELDS-OFFSET + 1.
        ENDIF.
* ABAP aligns inner strcutures also at the end according to the
* structure alignment. That means the global offset OFFSET has to be
* aligned to that value.
        PERFORM OFFSET_CORRECTION USING
                                     ' '
                                     MAX_ALIGNMENT
                                  CHANGING
                                     OFFSET.
* Append fields for this structure to entire fields table ...
        APPEND LINES OF THISFIELDS TO P_FIELDS.
* ... and leave
        EXIT.
      ENDIF.
      DESCRIBE FIELD <F> TYPE TYP
                         COMPONENTS NN_COMPS
                         LENGTH LEN IN BYTE MODE
                         DECIMALS DECS.
* Do recursive calls for nested structures.
      IF NN_COMPS > 0.
        PERFORM DYN_ANALYSE_SINGLE TABLES THISFIELDS
                                   USING <F>
                                   CHANGING POS LEN OFFSET ALIGNMENT.
* remember maximum alignment requirement, since inner structures are
* aligned according to the maximum required by inner elements.
        IF ALIGNMENT > MAX_ALIGNMENT.
          MAX_ALIGNMENT = ALIGNMENT.
        ENDIF.
      ELSE.
* get the required alignment for this type of element and remeber
* maximum.
        PERFORM GET_ALIGNMENT USING TYP CHANGING ALIGNMENT.
        IF ALIGNMENT > MAX_ALIGNMENT.
          MAX_ALIGNMENT = ALIGNMENT.
        ENDIF.
* store all significant information in THISFIELDS, which is appended
* to P_FIELDS later.
        THISFIELDS-EXID = TYP.
        THISFIELDS-INTLENGTH = LEN.
        THISFIELDS-POSITION = POS.
        THISFIELDS-OFFSET = OFFSET.
        THISFIELDS-FIELDNAME = POS.
        THISFIELDS-TABNAME = CNDP_SAP_TAB_UNKNOWN.
        THISFIELDS-DECIMALS = DECS.
* Adding the length to offset is fine here, since the correct offset
* according to the required alignment is done later.
        OFFSET = THISFIELDS-OFFSET + LEN.
        POS = POS + 1.
        APPEND THISFIELDS.
      ENDIF.
    ENDDO.
  ELSE.                                " if n_Comps > 0
* We only have a simple field.
* Be straight forward, just put the elements into p_fields.
    REFRESH P_FIELDS.
    P_FIELDS-EXID = DATATYPE.
    P_FIELDS-INTLENGTH = DATALEN.
    P_FIELDS-POSITION = 1.             " Position is always
    P_FIELDS-OFFSET = 0.               " Offset at beginning
    P_FIELDS-FIELDNAME = '1'.          "#EC_TEXT Name as above
    P_FIELDS-TABNAME = CNDP_SAP_TAB_UNKNOWN.
    P_FIELDS-DECIMALS = DATADEC.
    APPEND P_FIELDS.
  ENDIF.

ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  GET_ALIGNMENT
*&---------------------------------------------------------------------*
FORM GET_ALIGNMENT USING    P_TYP
                   CHANGING ALIGNBASE.
  ALIGNBASE = 1.
  CASE P_TYP.
*FLOAT VALUE NEEDS 8 BYTE ALIGNMENT
    WHEN 'F'.
      ALIGNBASE = 8.
*SHORT INTEGER NEEDS 2 BYTE ALIGNMENT
    WHEN 's'.
      ALIGNBASE = 2.
*LONG INTEGER NEEDS 4 BYTE ALIGNMENT
    WHEN 'I'.
      ALIGNBASE = 4.
*ALL OTHER TYPES ARE BYTE ALIGNED
  ENDCASE.

ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  OFFSET_CORRECTION
*&---------------------------------------------------------------------*
FORM OFFSET_CORRECTION USING    P_TYP ALIGNBASE
                       CHANGING P_FIELDS_OFFSET.

  DATA : MODRESULT TYPE I.
  DATA : ADDOFFSET TYPE I.
  DATA : THISALIGNBASE TYPE I.

  IF P_TYP NE ' '.
    PERFORM GET_ALIGNMENT USING P_TYP CHANGING THISALIGNBASE.
  ELSE.
    THISALIGNBASE = ALIGNBASE.
  ENDIF.
  IF THISALIGNBASE NE 1.
* If the reminder of mod operation is not equal 0 we have to adjust
* the offset
    MODRESULT = P_FIELDS_OFFSET MOD THISALIGNBASE.
    IF MODRESULT NE 0.
      ADDOFFSET = THISALIGNBASE - MODRESULT.
      P_FIELDS_OFFSET = P_FIELDS_OFFSET + ADDOFFSET.
    ENDIF.
  ENDIF.
ENDFORM.

thanks

mrutyun^

Code Formatted by: Alvaro Tejada Galindo on Jan 23, 2008 6:17 PM

Read only

Former Member
In response to Former Member

‎2008 Jan 23 1:47 PM

0 Likes
747

Hi mrutyun^,

first of all thanks.....

I tried ur code executing but in ur code u have been used the z function modules......what is the use of that function modules......do you mean that i want to create those function modules and execute......and what can be declared for importing and exporting and tables fields to be done...plz can u clarify me this it is very urgent....

thanks in Advance.....

brahma

Read only

Former Member
In response to Former Member

‎2008 Jan 24 7:26 AM

0 Likes
747

Hi all,

Do any one can help me out this.....

Eagerly waiting for ur reply...

Thanks in Advance...!

Brahma