Skip to Content

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.

To report this post you need to login first.

29 Comments

You must be Logged on to comment or reply to a post.

    1. Krishna Moturi
      Eddy,

      I looked for your article at the above link and didnt find it. Would you kindly let me know where I can access it. By the way I checked out your website, it is cool.

      best regards
      kris moturi

      (0) 
    2. Krishna Moturi
      Eddy,

      I looked for your article at the above link and didnt find it. Would you kindly let me know where I can access it. By the way I checked out your website, it is cool.

      best regards
      kris moturi

      (0) 
    3. Robin H
      Hi Durairaj ,
      First of all let me tell u, that u r awesome….Your blogs were too good.
      Initially i tried to use your function module, but there isnt a class called cl_rsr_query_variables.
      So i tried using RRW3_GET_QUERY_VIEW_DATA.
      But i am getting a dump saying “Exception condition “NO_APPLICABLE_DATA” raised. “.
      Here is the code im using.

      zparam-NAME = ‘VAR_NAME_1’.
      zparam-VALUE = ‘0RPA_BDD’.
      append zparam to zparam2.
      zparam-NAME = ‘VAR_VALUE_EXT_1’.
      zparam-VALUE = ‘20080220’.
      append zparam to zparam2.

      CALL FUNCTION ‘RRW3_GET_QUERY_VIEW_DATA’
      EXPORTING
      i_infoprovider = ‘ZMC_POS_A’
      i_query = ‘REP_ZMC_POS_A_001_TEST’
      i_t_parameter = zparam2

      IMPORTING
      e_axis_data = wa_axis_data
      e_cell_data = wa_CELL_DATA.

      Can you also tell me how to pass to i_t_parameter , if i want to use a date range.
      Thanks in advance.

      Regards,
      Sumanth

      (0) 
  1. Madhukar Balguri
    HI Durairaj,

    Your FM is gearat. This is what we are looking for .
    I was trying on my system with hierarcy node variables as you have mentioned, but I am not getting the required result.

    It throws a exception BAD_VALUE_COMBINATION but the query runs fine.

    works fine with Chrecterstic variable.

    Can you please let me know the exact way of passing the hierarchy node variables.

    Regards,
    Madhu

    (0) 
    1. Durairaj Athavan Raja Post author
      Sorry for the delay in replying. (thursday & friday is our weekend)

      did you pass hierarchy node variable with 0HIER_NODE in the high column? and still you are getting BAD_VALUE_COMBINATION exception?

      May be a little bit of debugging would tell where the problem is.

      Regards
      Raja

      (0) 
  2. Annie Sam
    Hi,

    Your blog was just excellent. I could do a BI to XML delivery using the same.

    Thanks and All the Best.
    Hope to see more blogs.

    With Regards,
    Annie.

    (0) 
  3. Dario Balice
    Hi Raja,

    what’s a good work!
    This is my first post…i’m excited!
    I’ve a question about the integration of the steps you described.
    I have to extract the query result starting and ending on a web dynpro. The question is: how can i integrate the FM running, the XSLT transformation and the ABAP in a one-step running?

    Greetings

    Dario

    (0) 
  4. Tony Cromwell
    Hi Athavan,

    I think your FM is great…
    But I’m facing a problem with this TABLES parameter

    QUERY_VARIABLES     LIKE     RRX_VAR

    The problem is RRX_VAR-VNAM length is only 8 Characters.

    I have a Query with variable length more than 8.

    I tried using the std FM: RRW3_GET_QUERY_VIEW_DATA
    This can accept variable names with more than 8 Chars.
    But here I’m not able to filter data with an asterisk ( “*” ).

    Thanks for the help n keep up the great work!
    Tony.

    (0) 
    1. Rajeev Kapur
      Hi,

      Function Module (RRW3_GET_QUERY_VIEW_DATA) is working for us as well, however when i use an asterisk it does bring the data.  Please can you post the resolution for the same.

      Regards and Thanks
      Rajeev

      (0) 
      1. Durairaj Athavan Raja Post author
        if you want all data you can leave the input value blank.

        however i am not sure about patterns like

        value = abc* as VAR_OPERATOR_I doesnt support CP (contains pattern)

        (0) 
  5. Rithesh Vijayakrishnan
    Hi,

    I am using your method to execute queries from ABAP. But I have a query where i need to filter the characteristics in the ‘Rows’ using variables. When i put these cahracteristics in ‘Filter’ section I am able to pass the variables from ABAP. But when the restriction is applied in Rows am not able to pass the variables. is there any way to do this ?

    Thanks,

    (0) 
  6. Robin H
    Hi Durairaj ,
    First of all let me tell u, that u r awesome….Your blogs were too good.
    Initially i tried to use your function module, but there isnt a class called cl_rsr_query_variables.
    So i tried using RRW3_GET_QUERY_VIEW_DATA.
    But i am getting a dump saying “Exception condition “NO_APPLICABLE_DATA” raised. “.
    Here is the code im using.

    zparam-NAME = ‘VAR_NAME_1’.
    zparam-VALUE = ‘0RPA_BDD’.
    append zparam to zparam2.
    zparam-NAME = ‘VAR_VALUE_EXT_1’.
    zparam-VALUE = ‘20080220’.
    append zparam to zparam2.

    CALL FUNCTION ‘RRW3_GET_QUERY_VIEW_DATA’
    EXPORTING
    i_infoprovider = ‘ZMC_POS_A’
    i_query = ‘REP_ZMC_POS_A_001_TEST’
    i_t_parameter = zparam2

    IMPORTING
    e_axis_data = wa_axis_data
    e_cell_data = wa_CELL_DATA.

    Can you also tell me how to pass to i_t_parameter , if i want to use a date range.
    Thanks in advance.

    Regards,
    Sumanth

    (0) 
  7. Sam Varughese
    Hello, the FM Y_EXECUTE_QUERY doesn’t compile in our system anymore.  It’s giving a message “cl_rsr_query_variables is unknown”.  Please help.  Thanks.
    (0) 
  8. Sam Varughese
    Hello, the FM Y_EXECUTE_QUERY doesn’t compile in our system anymore.  It’s giving a message “cl_rsr_query_variables is unknown”.  Please help.  We are currently on BI 7.0.
    Thanks.
    (0) 
  9. BW Basis Support
    Hi Durairaj Athavan Raja

    Thank you for your informative Blog.

    Can you please advise how i can feed the query results directly into a flat table.

    What i am tring to achieve, is creating a Customer Exit Variable, which has a BADI.

    I will then call your function module to execute the query with the required variable values that i pass through.

    I then simply want to return the results into a table.  (Very similar to how WRITEQUERY program works)

    Alternatively if you have sample solution to create a FM to call WRITEQUERY, and pass parameters genuid and Variable Values of query through in order to get the results.

    Thank you.

    Simon

    (0) 
  10. Ramkumar Valluru
    Hi,
    I want to display the BI report data in bsp or webdynpro abap which is in arabic language.The BI system doesn’t support the non-unicode.so i have to get that report data into unicode sytem in webdynpro or bsp and encode that data and pass to Bi system.
    Is there any functionmodule to encode the data?
    How to display that report data in bsp and which UI element support the Browser.

    Please help me in the same.

    Thanking you in advacne..

    (0) 
  11. Toni Sierra
    Hi,

    I coment a peice of code because I don’t have the class CL_RSR_QUERY_VARIABLES.

    Now, I want to execute one query that have a filter variable under one dimension. I put the next code:

    MOVE: ‘ZD_CHAN’ TO wa_var-vnam,
    ‘I’ TO wa_var-sign,
    ‘EQ’ TO wa_var-opt,
    ‘Internet’ TO wa_var-low,
    ‘0HIER_NODE’ to wa_var-high,
    APPEND wa_var TO var .

    The problem, is that this code execute the query but don’t apply the filter.

    Can Anybody helps me ???

    Thanks!!!

    (0) 
    1. Durairaj Athavan Raja Post author
      the parameter passing should be something like below

      ls_para-name = ‘VAR_NAME_1’.
      ls_para-value = ‘L_PVALMNT’.
      APPEND ls_para TO lt_para.
      ls_para-name = ‘VAR_OPERATOR_1’.
      ls_para-value = ‘BT’.
      APPEND ls_para TO lt_para.
      ls_para-name = ‘VAR_VALUE_LOW_EXT_1’.
      ls_para-value = ‘04.2008’.
      APPEND ls_para TO lt_para.
      ls_para-name = ‘VAR_VALUE_HIGH_EXT_1’.
      ls_para-value = ‘05.2008’.
      APPEND ls_para TO lt_para.

      also would suggest you to check the part iii of this blog
      Execute BW query using ABAP Part III

      (0) 
      1. Joseph Tshwene
        HI Durairaj Athavan Raja

        Im having a problem with reading certain specific sections of a query  using the parameters. Ive tried using the scenarios that defined in the forum but still cant be able to fix my problem

        Pls Help

        (0) 

Leave a Reply