Program to archive the Bex report output to AL11 with Header

[Former Member]

Archiving the Bex report output in AL11 with Header

Table of Contents

1. Business Scenario                            ..…………………………………………………………………………………     3

2. Introduction                                       ……………………………………………………………………………………   3

3. Program                                          ……………………………………………………………………………………     4

4. Query Detail                                         …………………………………………………………………………………   10

5. Output                                                …………………………………………………………………………………… 11

1. Business Scenario:

In a typical business scenario, users would want to view the Bex report output of the past days. For this, they need to save the past Bex report output. This can be achieved by archiving the Bex reports in AL11 folder and use them when needed. This white paper demonstrates how to archive the Bex report output to AL11 folder with header.

2.    Introduction:

BI reports designed in query designer can be published in various manners for future use. This data can be used for further processing by different SAP and non-SAP systems when required. Standard SAP Function Module  'RS_VC_GET_QUERY_VIEW_DATA_FLAT' is used to get the report output data into internal tables.

To fetch the values of the input variables in the selection screen of the Bex report, is implemented using the class ‘CL_RSR_REQUEST’ and the method ‘VARIABLES_SEND_ABAP_DYNPRO’.

3.    Program:

The input parameters in the selection screen of the report are the Query name and the target file path in AL11, where the output has to be archived.

The query name is the input parameter of the class ‘CL_RSR_REQUEST’. If there are any mandatory input variables in the selection screen of the Bex report, then the method ‘VARIABLES_SEND_ABAP_DYNPRO’ will display the screen to input the values for selection.

The input parameter for the function module 'RS_VC_GET_QUERY_VIEW_DATA_FLAT' is the query name. This function module returns the output of the Bex report in tables.

* -  Data Declarations

DATA:  lt_parameter type TABLE OF W3QUERY,

lt_celldata type table of BAPI6111CD,

wa_celldata like line of lt_celldata,

lt_axisinfo type table of RRX_AXIS_INFO,

wa_axisinfo like line of lt_axisinfo,

lt_axischars type TABLE OF RRX_AXIS_CHARS,

wa_axischars LIKE LINE OF lt_axischars,

lt_axisattrs type table of RRX_AXIS_ATTRS,

lt_axisdatacol type table of RRX_AXIS_DATA,

wa_axisdatacol like line of lt_axisdatacol,

lt_axisdatarow type TABLE OF RRX_AXIS_DATA,

wa_axisdatarow like line of lt_axisdatarow,

lt_axisdataslicer type TABLE OF RRX_AXIS_DATA,

lt_attrdatarows type TABLE OF RRX_AXIS_DATA,

lt_ttrdatacols type TABLE OF RRX_AXIS_DATA,

lt_textsymbols type table of RRWS_S_TEXT_SYMBOLS.

DATA:  var_keyfigno TYPE N LENGTH 2,

              var_noofrecords TYPE N LENGTH 10,

               var_charno TYPE N LENGTH 2,

               ls_rowheader type string,

               ls_keyfigheader type string,

               ls_freechar type string,

                ls_rowdata type string,

               ls_keyfigdata type string,

               var_freechar type N LENGTH 2,

                lv_rec type string,

               lv_separator TYPE RSCRMSEPAR value '|'.

DATA:  lv_from type n length 10,

               lv_to type n length 10,

             i type n length 2.

             i = '0'.

* - Reference object for the class

DATA: LO_REQ type ref to CL_RSR_REQUEST.

* - Selection screen for the report

SELECTION-SCREEN BEGIN OF BLOCK B1.

  PARAMETERS:   p_query TYPE RSZCOMPID obligatory,

                                  p_filenm TYPE RSFILENM obligatory.

SELECTION-SCREEN END OF BLOCK B1.

CREATE OBJECT LO_REQ

  EXPORTING

      I_QUERY           =         p_query.

* - Input screen of the report to be archived

CALL METHOD LO_REQ->VARIABLES_SEND_ABAP_DYNPRO
EXPORTING
I_FORCE               = RS_C_FALSE
EXCEPTIONS
SCREEN_CANCELED       = 2
.
IF SY-SUBRC <> 0.
IF SY-SUBRC = 2.
MESSAGE 'Enter Input on the Selection Screen' TYPE 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
ENDIF.

* - Opening the file in AL11

OPEN DATASET p_filenm FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

  If sy-subrc = 0.

        SET DATASET p_filenm POSITION 0.

  endif.

* - Function module to get query output data into tables

CALL FUNCTION 'RS_VC_GET_QUERY_VIEW_DATA_FLAT'

EXPORTING

     I_QUERY                         = p_query

I_MAX_ROWS                = -1

TABLES

I_T_PARAMETER = lt_parameter

E_T_CELL_DATA = lt_celldata

E_T_AXIS_INFO = lt_axisinfo

E_T_AXIS_CHARS = lt_axischars

E_T_AXIS_ATTRS = lt_axisattrs

E_T_AXIS_DATA_COLUMNS    = lt_axisdatacol 

E_T_AXIS_DATA_ROWS            = lt_axisdatarow

E_T_AXIS_DATA_SLICER          = lt_axisdataslicer

E_T_ATTR_DATA_ROWS           = lt_attrdatarows

E_T_ATTR_DATA_COLUMNS   = lt_ttrdatacols

E_T_TEXT_SYMBOLS                 = lt_textsymbols

EXCEPTIONS

    NO_APPLICABLE_DATA           = 1 .

IF SY-SUBRC <> 0.

    IF SY-SUBRC = 1.

        ls_rowdata = 'Data do not exist'.

        Transfer ls_rowdata to p_filenm.

       Exit.

    ENDIF.

1. ELSE.

     Loop at lt_axisinfo into wa_axisinfo.

         If wa_axisinfo-AXIS = 000.

“Number of key figures in the report.

var_keyfigno = wa_axisinfo-NCOORDS.

         elseif wa_axisinfo-AXIS = 001.

“ Number of records in the query output

var_noofrecords = wa_axisinfo-NCOORDS.

var_charno = wa_axisinfo-NCHARS.

         endif.

      endloop.

* - Write the header to AL11 file

    CONCATENATE  'No of Records =' var_noofrecords into lv_rec.

     if lt_celldata is not initial.

TRANSFER lv_rec to p_filenm.

      loop at lt_axischars into wa_axischars.

       IF WA_axischars-AXIS = '000'.

loop at lt_axisdatacol into wa_axisdatacol.

if wa_axischars-chanm = wa_axisdatacol-chanm.

* - Get the Description of Key Figures header into a string

CONCATENATE ls_keyfigheader wa_axisdatacol-caption lv_separator INTO ls_keyfigheader.

1. endif.
2. ENDLOOP.

* - Get the Description of the characteristics header into a string

         ELSEIF WA_axischars-AXIS = '001'.

CONCATENATE ls_rowheader wa_axischars-caption lv_separator INTO ls_rowheader.

         endif.

       endloop.

* - Concatenate the characteristics and key figure header

CONCATENATE ls_rowheader ls_keyfigheader into ls_rowheader.

* - Write the header to AL11 file.

Transfer ls_rowheader to p_filenm.

* -  Write the records to AL11 file

loop at lt_axisdatarow into wa_axisdatarow.

CONCATENATE ls_rowdata  wa_axisdatarow-chavl_ext  lv_separator INTO  ls_rowdata.

At end of TUPLE_ORDINAL.

if i NE '0'.

lv_from = lv_to + 1.

lv_to = lv_from + var_keyfigno.

1. else.

lv_from = 1.

lv_to =  var_keyfigno.

1. endif.

Loop at lt_celldata into wa_celldata from lv_from to lv_to.

CONCATENATE ls_keyfigdata wa_celldata-formatted_value lv_separator INTO ls_keyfigdata.

1. endloop.

CONCATENATE LS_ROWDATA LS_KEYFIGDATA INTO LS_ROWDATA.

Transfer ls_rowdata to p_filenm.

Clear ls_keyfigdata.

clear ls_rowdata.

1. ENDAT.

i = i + 1.

1. endloop.

     endif.

     ENDIF.

Close dataset p_filenm.

MESSAGE 'DATA IS WRITTEN IN AL11 FILE' TYPE 'I'.

1. 4.    Query details:

 

 

5.    Output:

Selection screen for the report:

Input screen for the Bex report to be archived:

Information message upon archiving:

Data in in file in AL11 with header: