Pivot Table

Former Member — Wed, 23 Jan 2008 12:52:09 GMT
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.
Re: Pivot Table

Former Member — Wed, 23 Jan 2008 13:09:19 GMT
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
Re: Pivot Table

Former Member — Wed, 23 Jan 2008 13:47:19 GMT
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
Re: Pivot Table

Former Member — Thu, 24 Jan 2008 07:26:49 GMT
Hi all,
Do any one can help me out this.....
Eagerly waiting for ur reply...
Thanks in Advance...!
Brahma
topic Re: Pivot Table in Application Development and Automation Discussions

Pivot Table

Re: Pivot Table

Re: Pivot Table

Re: Pivot Table
