Execute BW query using ABAP Part I

Use the new modified code of Y_EXECUTE QUERY provided in this link

FUNCTION y_execute_query.

*”—-

---

““Local Interface:

*”  IMPORTING

*”     VALUE(QUERY_NAME) TYPE  CHAR50

*”  EXPORTING

*”     VALUE(XML_OUT) TYPE  STRING

*”  TABLES

*”      QUERY_VARIABLES STRUCTURE  RRX_VAR

*”      RETURN STRUCTURE  BAPIRET2 OPTIONAL

*”      META STRUCTURE  ZBW_QUERY_OUTPUT_METADATA OPTIONAL

*”  EXCEPTIONS

*”      BAD_VALUE_COMBINATION

*”      USER_NOT_AUTHORIZED

*”      UNKNOWN_ERROR

*”      QUERY_NOT_FOUND

*”—-

---

• data

TYPE-POOLS: rrx1 .

DATA: r_request TYPE REF TO cl_rsr_request.

DATA: r_dataset TYPE REF TO cl_rsr_data_set.

DATA: zcx_message TYPE REF TO cx_rsr_x_message.

DATA: zcx_root TYPE REF TO cx_root.

DATA: lcount TYPE i .

DATA: xcount TYPE i .

DATA: wa_var TYPE rrx1_s_var.

DATA: i_var TYPE rrx1_t_var.

DATA: wa_axis LIKE LINE OF r_dataset->n_sx_version_20a_1-axis_data .

DATA: wa_axis_info LIKE LINE OF r_dataset->n_sx_version_20a_1-axis_info .

DATA: wa_chars LIKE LINE OF wa_axis_info-chars .

DATA: wa_cell LIKE LINE OF r_dataset->n_sx_version_20a_1-cell_data .

DATA: wa_set LIKE LINE OF wa_axis-set.

DATA: cube_name LIKE bapi6110var-cube_nam .

DATA: q_return LIKE bapiret2 OCCURS 0 WITH HEADER LINE .

DATA: p_genuniid TYPE rsgenuniid .

DATA: wf_query_var TYPE REF TO cl_rsr_query_variables .

DATA: error_string TYPE string.

DATA: no_of_chars TYPE  i ,

      no_of_keyf TYPE i .

DATA: var_nam(10) .

DATA: iobj_detail LIKE bapi6108 .

DATA: iobj_details LIKE bapi6108 OCCURS 0 WITH HEADER LINE .

DATA: it_fieldcat TYPE lvc_t_fcat,

      is_fieldcat LIKE LINE OF it_fieldcat.

DATA: new_table TYPE REF TO data.

DATA: new_line  TYPE REF TO data.

FIELD-SYMBOLS:  TYPE ANY.

DATA: meta_data LIKE zbw_query_output_metadata OCCURS 0 WITH HEADER LINE .

DATA: off  TYPE i,

    moff TYPE i,

    mlen TYPE i.

DATA: iobj_return LIKE bapiret2 OCCURS 0 WITH HEADER LINE .

DATA: n_counter(3) TYPE n .

DATA: char_count TYPE i .

DATA: wf_fldnm(40) .

*******

  MOVE: query_name TO cube_name .

• convert the query name to technical id

  CLEAR p_genuniid .

  CALL FUNCTION ‘CONVERSION_EXIT_GENID_INPUT’

    EXPORTING

      input  = query_name

    IMPORTING

      output = p_genuniid.

  IF p_genuniid IS INITIAL .

    RAISE query_not_found .

  ENDIF .

• create instance of  cl_rsr_request

  CREATE OBJECT r_request

  EXPORTING i_genuniid = p_genuniid .

*create instance of cl_rsr_variables

  i_var[] = query_variables[] .

  CREATE OBJECT wf_query_var

    EXPORTING

      i_r_request           = r_request

       i_t_nvar              = i_var

    EXCEPTIONS

      user_not_authorized   = 1

      no_processing         = 2

      bad_value_combination = 3

      x_message             = 4

      OTHERS                = 5

      .

  IF sy-subrc <> 0.

    CASE sy-subrc .

      WHEN 1 .

        RAISE user_not_authorized .

      WHEN 3 .

        RAISE bad_value_combination .

      WHEN OTHERS .

        RAISE unknown_error .

    ENDCASE .

  ENDIF.

• set the variable and execute the query

  TRY.

      r_request->variables_set( i_t_var = i_var ).

      r_request->variables_start( ).

      r_request->read_data( ).

      r_dataset = cl_rsr_data_set=>get( i_r_request = r_request ).

      r_dataset->refresh( i_version = 1 ).

    CATCH cx_rsr_x_message INTO zcx_message.

    CATCH cx_root INTO zcx_root.

      CLEAR error_string .

      error_string = zcx_root->get_text( ).

      MOVE: ‘E’ TO return-type ,

            error_string+0(220) TO return-message .

      APPEND return .

      CLEAR return .

      RETURN .

  ENDTRY.

  IF r_request->n_sx_output-no_authorization NE ‘X’ AND

     r_request->n_sx_output-no_data NE ‘X’ .

• find no. of key figures

    CLEAR: lcount ,wa_axis , wa_axis_info .

    READ TABLE r_dataset->n_sx_version_20a_1-axis_data  INTO wa_axis WITH KEY axis = ‘000’  .

    IF sy-subrc EQ 0 .

      CLEAR no_of_keyf .

      DESCRIBE TABLE wa_axis-set LINES no_of_keyf .

    ENDIF .

• find number of characteristics

    READ TABLE r_dataset->n_sx_version_20a_1-axis_info  INTO wa_axis_info WITH KEY axis = ‘001’  .

    IF sy-subrc EQ 0 .

      CLEAR no_of_chars .

      DESCRIBE TABLE wa_axis_info-chars LINES no_of_chars .

    ENDIF .

    CLEAR : iobj_detail , iobj_details .

    REFRESH iobj_details .

    CLEAR wa_axis_info .

• get chars. details

    READ TABLE r_dataset->n_sx_version_20a_1-axis_info  INTO wa_axis_info WITH KEY axis = ‘001’  .

    IF sy-subrc EQ 0 .

      LOOP AT wa_axis_info-chars INTO wa_chars .

        CLEAR off .

        FIND ‘__’ IN SECTION OFFSET off OF

               wa_chars-chanm

               MATCH OFFSET moff

               MATCH LENGTH mlen.

        IF sy-subrc EQ 0 .

          off = moff + mlen .

          SHIFT wa_chars-chanm LEFT BY off PLACES .

        ENDIF .

        CLEAR: iobj_return .

        REFRESH : iobj_return .

• this BAPI(Z_BAPI_IOBJ_GETDETAIL) is a copy of BAPI_IOBJ_GETDETAIL without the authority check

• with the following code commented out

• CALL METHOD cl_rsd_iobj=>authority_check

•    EXPORTING

•      i_iobjnm            = infoobject

•      i_activity          = rssb_c_auth_actvt-display

•    EXCEPTIONS

•      user_not_authorized = 1.

• IF sy-subrc <> 0.

•    bapi_syserror.

•    EXIT.

• ENDIF.

        CALL FUNCTION ‘Z_BAPI_IOBJ_GETDETAIL’

          EXPORTING

            version    = rs_c_objvers-active

            infoobject = wa_chars-chanm

          IMPORTING

            details    = iobj_detail.

        IF  NOT iobj_detail IS INITIAL .

          MOVE-CORRESPONDING iobj_detail TO iobj_details .

          APPEND iobj_details .

          CLEAR iobj_details .

        ENDIF .

        CLEAR : iobj_detail .

      ENDLOOP .

    ENDIF .

• build field cat. for building the itab

    LOOP AT iobj_details .

      is_fieldcat-fieldname = iobj_details-infoobject .

      IF is_fieldcat-fieldname+0(1) EQ ‘0’ .

        SHIFT is_fieldcat-fieldname LEFT BY 1 PLACES .

      ENDIF .

      is_fieldcat-datatype = iobj_details-datatp.

      is_fieldcat-outputlen = iobj_details-outputlen .

      is_fieldcat-scrtext_l = iobj_details-textlong.

      APPEND is_fieldcat TO it_fieldcat.

      CLEAR : is_fieldcat .

    ENDLOOP .

    CLEAR :n_counter, wa_axis .

    READ TABLE r_dataset->n_sx_version_20a_1-axis_data  INTO wa_axis WITH KEY axis = ‘000’  .

    IF sy-subrc EQ 0 .

      LOOP AT wa_axis-set INTO wa_set .

        n_counter = n_counter + 1 .

        CONCATENATE ‘VALUE’ n_counter INTO is_fieldcat-fieldname .

        is_fieldcat-outputlen = ’30’.

        is_fieldcat-datatype = ‘CHAR’.

        is_fieldcat-scrtext_l = wa_set-caption.

        APPEND is_fieldcat TO it_fieldcat.

        CLEAR : is_fieldcat .

      ENDLOOP .

    ENDIF .

    CLEAR meta_data .

    REFRESH meta_data .

    LOOP AT it_fieldcat INTO is_fieldcat .

      MOVE-CORRESPONDING is_fieldcat TO meta_data .

      APPEND meta_data .

      CLEAR: meta_data, is_fieldcat .

    ENDLOOP .

• create itab

    CALL METHOD cl_alv_table_create=>create_dynamic_table

      EXPORTING

        it_fieldcatalog = it_fieldcat

      IMPORTING

        ep_table        = new_table.

• Create a new Line with the same structure of the table.

    ASSIGN new_table->* TO You could test from SE37, but the only problem would be that you cannot see

the returned XML result in full. Sample code is provided in

Execute BW query using ABAP Part II to do a complete test of this.

Create the FM and try it yourself. I am sure that there will be areas of
improvement in this FM for which i am really looking forward to your valuable
comments.

Please note that not all types of errors are handled in
this sample. Only some basic error handling is done.