09-05-2008 6:40 AM
Hi Friends,
Could you please anyone help me to get the functionality of generating Pivot table in Excel from SAP.
and let me know how to use the structure EXCELPIVOT.
I am very greatful if anyone get me for this.
09-05-2008 6:46 AM
09-05-2008 6:50 AM
Hi ,
Here is the code.
----
--> p1 text
<-- p2 text
----
FORM fill_xxl_interface .
xmpl_header1 = text-048.
PERFORM fill_xmplt_o.
PERFORM fill_xmplt_p.
PERFORM fill_xmplt_v. " online text
PERFORM fill_xmplt_s.
PERFORM fill_xmplt_h.
PERFORM xxl_list_output.
ENDFORM. " fill_xxl_interface
&----
*& Form fill_xmplt_o
&----
text
----
--> p1 text
<-- p2 text
----
FORM fill_xmplt_o .
DATA:
info1(40),
info2(40),
ival1(60),
ival2(60).
info1(40) = text-049.
info2(40) = text-050.
WRITE sy-datum TO ival1.
WRITE sy-uname TO ival2.
xmplt_o-line_no = 1. " Text which can be displayed
xmplt_o-info_name = info1. " \ online in the list viewer
xmplt_o-info_value = ival1. " \ is defined here.
APPEND xmplt_o. "#EC *
xmplt_o-line_no = 2.
xmplt_o-info_name = info2.
xmplt_o-info_value = ival2.
APPEND xmplt_o. "#EC *
ENDFORM. " fill_xmplt_o
&----
*& Form fill_xmplt_p
&----
text
----
--> p1 text
<-- p2 text
----
FORM fill_xmplt_p .
DATA:
ptxt1(60),
ptxt2(60),
ptxt3(60).
ptxt1(20) = text-051. " SAP R/3 XXL Listviewer
ptxt2(20) = text-052. " Programmplanung
WRITE sy-datum TO ptxt3.
xmplt_p-hf = 'H'. "
xmplt_p-lcr = 'L'. " left position
xmplt_p-line_no = 1. "
xmplt_p-text = ptxt1.
APPEND xmplt_p. "#EC *
xmplt_p-lcr = 'C'.
xmplt_p-line_no = 2.
xmplt_p-text = ptxt2.
APPEND xmplt_p. "#EC *
xmplt_p-lcr = 'R'.
xmplt_p-line_no = 1.
xmplt_p-text = ptxt3.
APPEND xmplt_p. "#EC *
ENDFORM. " fill_xmplt_p
&----
*& Form fill_xmplt_v
&----
text
----
--> p1 text
<-- p2 text
----
FORM fill_xmplt_v .
vtxt1 = text-004. " Production Line
REPLACE ';;' WITH ' ' INTO vtxt1.
vtxt2 = text-007. " Year
REPLACE ';;' WITH ' ' INTO vtxt2.
vtxt3 = text-024. " Month
REPLACE ';;' WITH ' ' INTO vtxt3.
vtxt4 = text-026. " Material
REPLACE ';;' WITH ' ' INTO vtxt4.
vtxt5 = text-022. " Material Description
REPLACE ';;' WITH ' ' INTO vtxt5.
vtxt6 = text-028. " Dosage Form
REPLACE ';;' WITH ' ' INTO vtxt6.
vtxt7 = text-047. " Dosage
REPLACE ';;' WITH ' ' INTO vtxt7.
vtxt8 = text-009. " Units per Pack
REPLACE ';;' WITH ' ' INTO vtxt8.
vtxt9 = text-036. " Manufacturing Stage
REPLACE ';;' WITH ' ' INTO vtxt9.
vtxt12 = text-092. " Order Number
REPLACE ';;' WITH ' ' INTO vtxt12.
vtxt13 = text-093. " Start Date
REPLACE ';;' WITH ' ' INTO vtxt13.
xmplt_v-col_no = 1.
xmplt_v-col_name = vtxt1.
APPEND xmplt_v. "#EC *
xmplt_v-col_no = 2.
xmplt_v-col_name = vtxt2.
APPEND xmplt_v. "#EC *
xmplt_v-col_no = 3.
xmplt_v-col_name = vtxt3.
APPEND xmplt_v. "#EC *
xmplt_v-col_no = 4.
xmplt_v-col_name = vtxt4.
APPEND xmplt_v. "#EC *
xmplt_v-col_no = 5.
xmplt_v-col_name = vtxt5.
APPEND xmplt_v. "#EC *
xmplt_v-col_no = 6.
xmplt_v-col_name = vtxt6.
APPEND xmplt_v. "#EC *
xmplt_v-col_no = 7.
xmplt_v-col_name = vtxt7.
APPEND xmplt_v. "#EC *
xmplt_v-col_no = 8.
xmplt_v-col_name = vtxt8.
APPEND xmplt_v. "#EC *
xmplt_v-col_no = 9.
xmplt_v-col_name = vtxt9.
APPEND xmplt_v. "#EC *
xmplt_v-col_no = 10.
xmplt_v-col_name = vtxt12.
APPEND xmplt_v. "#EC *
xmplt_v-col_no = 11.
xmplt_v-col_name = vtxt13.
APPEND xmplt_v. "#EC *
ENDFORM. " fill_xmplt_v
&----
*& Form fill_xmplt_s
&----
text
----
--> p1 text
<-- p2 text
----
FORM fill_xmplt_s .
DATA: n TYPE i, "#EC NEEDED
col_no TYPE i. "#EC NEEDED
CLEAR: xmplt_s, n.
DO n_vrt_keys TIMES.
ADD 1 TO col_no.
PERFORM line_xmplt_s USING col_no 'STR' 'DFT'.
ENDDO.
DO 6 TIMES.
ADD 1 TO col_no.
PERFORM line_xmplt_s USING col_no 'NUM' 'ADD'. "CO 589334
ENDDO.
ENDFORM. " fill_xmplt_s
&----
*& Form LINE_XMPLT_S
&----
text
----
-->P_COL_NO text
-->P_1708 text
-->P_1709 text
----
FORM line_xmplt_s USING col_no
col_typ
col_ops.
xmplt_s-col_no = col_no.
xmplt_s-col_typ = col_typ.
xmplt_s-col_ops = col_ops.
APPEND xmplt_s. "#EC *
ENDFORM. " LINE_XMPLT_S
&----
*& Form fill_xmplt_h
&----
text
----
--> p1 text
<-- p2 text
----
FORM fill_xmplt_h .
PERFORM line_xmplt_h USING 1 1 text-012.
PERFORM line_xmplt_h USING 1 2 text-014.
PERFORM line_xmplt_h USING 1 3 text-016.
PERFORM line_xmplt_h USING 1 4 text-019.
PERFORM line_xmplt_h USING 1 5 text-030.
PERFORM line_xmplt_h USING 1 6 text-033.
ENDFORM. " fill_xmplt_h
&----
*& Form line_xmplt_h
&----
text
----
-->P_1 text
-->P_1 text
-->P_TEXT_012 text
----
FORM line_xmplt_h USING row_no col_no text.
xmplt_h-row_no = row_no.
xmplt_h-col_no = col_no.
xmplt_h-col_name = text.
APPEND xmplt_h. "#EC *
ENDFORM. " line_xmplt_h
&----
*& Form xxl_list_output
&----
text
----
--> p1 text
<-- p2 text
----
FORM xxl_list_output .
DATA slines TYPE i.
DESCRIBE TABLE xmplt_s LINES slines.
n_att_cols = slines - n_vrt_keys.
SORT t_final BY year month dispo.
CALL FUNCTION 'XXL_FULL_API' "#EC *
EXPORTING
filename = 'XXLPB001'
header_1 = xmpl_header1
no_dialog = space
n_att_cols = n_att_cols
n_hrz_keys = 6
n_vrt_keys = n_vrt_keys
sema_type = ' '
TABLES
data = t_final
hkey = xmplt_h
online_text = xmplt_o
print_text = xmplt_p
sema = xmplt_s
vkey = xmplt_v
EXCEPTIONS
cancelled_by_user = 1
data_too_big = 2
dim_mismatch_data = 3
dim_mismatch_sema = 4
dim_mismatch_vkey = 5
error_in_hkey = 6
error_in_sema = 7
file_open_error = 8
file_write_error = 9
inv_data_range = 10
inv_winsys = 11
inv_xxl = 12
OTHERS = 13.
IF sy-subrc <> 0.
ENDIF.
ENDFORM. " xxl_list_output
----
Data declarations for 'XXL_FULL_API' *
----
DATA: xmpl_header1 TYPE gxxlt_p-text.
DATA BEGIN OF xmplt_o OCCURS 0."#EC * " Internal tables which shall be
INCLUDE STRUCTURE gxxlt_o."#EC * " \ used as input into the function
DATA END OF xmplt_o. " \ module's table parameters,
" \ are defined with reference to
DATA BEGIN OF xmplt_p OCCURS 0."#EC * " \ the appropriate DD structures.
INCLUDE STRUCTURE gxxlt_p. "#EC *
DATA END OF xmplt_p.
DATA BEGIN OF xmplt_h OCCURS 0. "#EC *
INCLUDE STRUCTURE gxxlt_h. "#EC *
DATA END OF xmplt_h.
DATA BEGIN OF xmplt_v OCCURS 0. "#EC *
INCLUDE STRUCTURE gxxlt_v. "#EC *
DATA END OF xmplt_v.
DATA BEGIN OF xmplt_s OCCURS 0. "#EC *
INCLUDE STRUCTURE gxxlt_s. "#EC *
DATA END OF xmplt_s.
DATA: n_att_cols TYPE i,
n_vrt_keys TYPE i VALUE '11'.