Skip to Content

In April 2005, I had written two weblogs titled Execute BW query using ABAP Part I & Execute BW query using ABAP Part II . Since then the function module (Y_EXECUTE_QUERY) explained in Execute BW query using ABAP Part I has gone for major change to handle performance and to provide some additional features. The changed code is provided in this document.

UPDATE (9th July 2008): you may be interested in this as well https://wiki.sdn.sap.com/wiki/display/BI/Expose+BI+Query+as+RESTful+Service

Enhancements:

  • Time consuming validations (query variable) are taken away. Now it’s the responsibility of the developer to pass proper variable/variable value.
  • Display attributes were not supported in the earlier version of Y_EXECUTE_QUERY and the same is fixed in this version.
  • Restricted key figures with multiple column headers which were not supported in the earlier version is fixed in this version.

If you are on BW3.5 you can use standard function module RRW3_GET_QUERY_VIEW_DATA to execute BW query and get the data.  The difference between RRW3_GET_QUERY_VIEW_DATA and Y_EXECUTE_QUERY is that the standard FM returns the data as separate tables for AXIS_INFO , CELL_DATA , AXIS_DATA and TXT_SYMBOLS where as using Y_EXECUTE_QUERY you can get the output in XML format and the same can be converted to a two dimensional itab using the XSLT program provided in Execute BW query using ABAP Part II. As far parameter passing for RRW3_GET_QUERY_VIEW_DATA you have to pass parameters to it like passing it for web query URL parameters. ( VAR_NAME_1= &VAR_VALUE_EXT_1=&etc…(more info)).  RRW3_GET_QUERY_VIEW_DATA is the one which is used when you call BW query as webservice.

To report this post you need to login first.

50 Comments

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

  1. Anonymous
    Hi Raja
              There is call to a Z* function module in your sample code. What is this Z* function module – is it just a copy of the standard object BAPI_IOBJ_GETDETAIL or what are the modifications and would you be able to share the code for this Z* object -with the reasons as to why it was modified.
    *******************************************
    CALL FUNCTION ‘Z_BAPI_IOBJ_GETDETAIL’
    EXPORTING
    version = rs_c_objvers-active
    infoobject = wa_chars-chanm
    IMPORTING
    details = iobj_detail.
    **********************************************
    (0) 
    1. Durairaj Athavan Raja Post author
      * 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.

      I have mentioned this is the PART1 code sample.
      Regards
      Raja

      (0) 
      1. Anonymous
        Raja
             Thanks for your prompt reply. I appreciate it. My speciality is not in the area of BW – but still tasked with calling a query from outside and formatting its results.

        Hence I have a simple question. I have an existing query – with some mandatory variables/parameters to be passed to the query inorder to run it. How do I get the technical names of the variables so that I can fill them in in wa_var structure in your example . Would opening the query defn using RRMX t.code give me the technical names of the variables of the query ?

        (0) 
          1. jaisingh yadav
            Hi Raja,

            We have used your FM in a program and executed it in Background. Now the job is finished successfully, but how can I see the result?

            Quick responce is appreciated.

            Regards,
            Jai

            (0) 
    2. Steven Hebert
      Regarding Fm: RRW3_GET_QUERY_VIEW_DATA

      I ran into a major problem trying to run it.

      Here is the issue:

      I just can’t seem to use the “I_T_PARAMETER” table properly.

      In my query for which I would like to fetch a single contract (ZOS_CONT) and the value is ‘300000015’.

      How would should “I_T_PARAMETER” be populated?

      I_T_PARAMETER-NAME = ‘ZOS_CONT’.
      I_T_PARAMETER-VALUE = ‘300000015’.

      Right now, all contracts are being returned be the FM.

      I would definitly appreciate your help.

      (0) 
      1. Durairaj Athavan Raja Post author
        you should use like below

        I_T_PARAMETER-NAME = “VAR_NAME_1”
        I_T_PARAMETER-VALUE = “ZOS_CONT”
        append i_t_parameter .
        I_T_PARAMETER-NAME = “VAR_VALUE_EXT_1”
        I_T_PARAMETER-VALUE = “300000015”
        append i_t_parameter .

        Regards
        Raja

        (0) 
        1. Enno Wulff
          Hey Raja!
          thanks for your blog! it helped me to get data from BW.
          My Problem is: Although i passed the variables like mentioned it does not seem to work. the parameters are ignored by fm RRW3_GET_QUERY_VIEW_DATA… :-[

            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.

          Does anyone have any idea why the parameters are ignored?

          thanks for any suggestions!

          (0) 
            1. Enno Wulff
              Hello Raja!
              Thanks! very mindful! But there was another problem: Only Variables that can be selected at the beginning of the query can be addressed in this way. Even if you can set filter criteria in the query results you are not able to access them from the function RRW3_GET_QUERY_VIEW_DATA. If I am wrong, please tell me.
              Thanks! Enno
              (0) 
    3. Ingo La Roche
      Hi everyone,

      I have a problem with the FM RRW3_GET_QUERY_VIEW_DATA in BW 3.0. SAP Note 675000 gives us a way to create this in FM in BW 3.0. But as described here we habe problems with I_T_PARAMETERS. Even if we use the correct syntax we do get the values unfilterd!

      Any idea who we can fix this?

      Best Regards
      Ingo La Roche

      (0) 
  2. lionel depaepe
    Hello,

    We are already using your developments to FTP some queries result. But this time, i need to pass also the description of the characteristics And i don’t see the text in the XML output of Y_EXECUTE_QUERY. Is it alos possible to have the texts ? to summarize, i would like to have exactly the same result than displayed in BEX or the Web and export to a file ( as the export to csv) and ftp it ?

    Thx a lot in advance.

    Lionel

    (0) 
  3. Anonymous
    Hi,

    i realy like this function module but I still have got a problem.

    We have an Infoobject 0PROFIT_CTR (10char) with compounding on 0CO_AREA (4char).

    The internal table I receive has got a field PROFIT_CTR (10char) but it contains the 4 chars of the 0CO_AREA followed by the first 6 chars of the 0PROFIT_CTR.

    Is it possible to just receive the value from 0PROFIT_CTR without the compunding value?

    best regards
    Stefan

    (0) 
      1. Anonymous

        Thanks for your hint. I could solve my problem by changeing the following code from:Stefan

        (0) 
    1. Durairaj Athavan Raja Post author
      Glad to hear that.
      to make it more dynamic , you can have a import parameter to decide whether you want to return values in internal format or external format.

      Regards
      Raja

      (0) 
  4. Anonymous
    Hello,

    I tried to use a “Customer exit formula variable”. If I view my query in Bex everything works fine. But when I try to receive the data via your function module y_execute_query, it does not seem to work. Because, he is not getting the value from my variable, allthough he is accessing the user exit.
    The itab i_var_final is always empty then

    Please could someone help me?

    Kind regards
    Stefan

    (0) 
    1. Iuliana Waltzer
      Hello Stefan?

      Did you solve the problem with “Customer exit formula variable”? I have the same problem now and I could not solve it for more than one week.
      Please help me if you have any answer.

      Thank you,
      Iuliana

      (0) 
      1. Durairaj Athavan Raja Post author
        This should work. I have used it. Did you try debugging and see whats going wrong?

        If you still couldnt solve it, send me your code by email to me. i will see whether anything is wrong with that. You can find my email id in my business card

        Regards
        Raja

        (0) 
  5. Bala S.
    Raja,
    Is there any version of this tool that works in BW 7.0 / NW04s. Currently we are upgrading and found that several classes don’t exist in BW7.0. For example cl_rsr_query_variables.

    Regards
    -Bala

    (0) 
    1. Durairaj Athavan Raja Post author
      Hi Bala,

      You can just comment out the following code in the FM

      CREATE OBJECT wf_query_var
      EXPORTING
      i_r_request = r_request
      i_t_nvar = i_var_final
      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.

      and also the variable declaration wf_query_var

      Regards
      Raja

      (0) 
  6. Dario Balice
    Hi Raja,

    i have problem with populating QUERY_VARIABLES structure; putting into VNAM field the query variable name (derived from 0FISCPER infoobject), sign I, option BT and trying to populate LOW, LOW_EXT and so on in any combination, the message is “no applicable data”. Using RRW3_GET_QUERY_VIEW_DATA with I_T_PARAMETER populated data are retrieved. Can you please help me? Greetings, Dario

    (0) 
    1. Durairaj Athavan Raja Post author
      Hi,
      As i have mentioned in earlier post, there may be some errors in some cases. I would suggest you to change the code to use RRW3_GET_QUERY_VIEW_DATA fm inside my FM instead of cl_RSR_REQUEST and dataset classes. If you find it difficult to do, mail me (you can find my email address in my business card) i can send you the code. Soon i will post the new code which is more roboust and error free.

      Regards
      Raja

      (0) 
  7. Anonymous
    Hi Durairaj

    And thanks a lot for your blog.
    I’m trying to execute a BW query using the RRW3_GET_QUERY_VIEW_DATA function module but when I test the function module I’m getting the data expected.
    My query is a simple query returning the cost center hierarchy that my authorizations give me access to). I fill the import parameters I_INFOPROVIDER and I_QUERY and leave I_VIEW_ID and I_T_PARAMETER empty. When I execute the function module I only get zeros in the E_CELL_DATA export parameter. The query works fine, when I execute it through the Query Analyzer.
    Any ideas what I might be doing wrong?
    We are using a BW 3.5 SP19

    Regards,
    Jacob Vennervald

    (0) 
  8. AM M
    i am trying to read values of AXIS_DATA-set-item for a query. but cannot read as it says that the AXIS_DATA doesnot have any component called set.

    can anyone show me the working example of this or th way to do it.

    email me on sap.architect@gmail.com

    (0) 
  9. mohamed dahmoune
    Very helpfull log

    Me, I Get trouble to pass type Date paramter.

    data : zparam2 type RRXW3TQUERY.
      data : zparam type W3QUERY.
      clear zparam.

    zparam-NAME = ‘VAR_NAME_1’.
    zparam-VALUE = ‘ZUPGDDAT’. “0calday variable
    append zparam to zparam2.
    zparam-NAME = ‘VAR_VALUE_EXT_1’.
    zparam-VALUE = p_date.   ” sy_datum type
    append zparam to zparam2.

      CALL FUNCTION ‘RRW3_GET_QUERY_VIEW_DATA’
         EXPORTING
             i_infoprovider           = ‘ZMC_STCK’
           i_query                   ‘ZVALMB_PMPUPGRD’
             i_t_parameter            = zparam2

         IMPORTING
            e_axis_data             = wa_axis_data
            e_cell_data              = wa_CELL_DATA.

    any idea ?

    (0) 
      1. Nooruddin Shaikh
        Hi Raja,
        Thanks for your information, however its not working. I have an hierarchy on the InfoObject 0COSTELMNT, with variable name ZDB_MI1 and would like to restrict on a node value ‘ABC123’.

        VAR_NAME_1 = ‘ZDB_MT1’
        VAR_VALUE_EXT_1 = ‘ABC123’
        VAR_NODE_IOBJNM_1 = ‘0COSTELMNT’

        However it comes up with with an ABAP runtime error. Would appreciate if you know somework around.

        Thanks
        Noor

        (0) 
  10. Nigel K
    I have a query that reports against the 0Bpartner infoobject.

    All this query does is display data for the attributes of 0Bpartner . Very straight forward report. The report runs fine in BEX.

    When I execute the FM  – RRW3_GET_QUERY_VIEW_DATA  against the same query ,how do check to see if the results return attribute data of the 0Bpartner infoobject.

    Reason being I have a java client interacting with SAP/BW over SAPJCO . The problem is the XML output returned by SAP does not have any attribute data in the  E_AXIS_DATA .  When I ran the same query in Bex, I see all the attribute data.

    Any ideas how I can fix this ?
    Thanks

    (0) 
      1. Nigel K
        Thanks. I figured out what the problem was. For some reason I had to uncheck the infoobject “display as hierarchy” option so that the data being returned was in a flat structure.

        However I am still having trouble filtering by hierarchy. You had recommended the following format

        VAR_NAME_1 = ZU_HIER
        VAR_VALUE_EXT_1 = HIERARCHY_A
        VAR_NODE_IOBJNM_1 = 0BPARTNER

        I tried it but like that user below, I got an ABAP runtime error. Any help would be much appreciated.

        Thanks !

        (0) 
  11. Marco Navrat
    Hi,
    I am having some problems when there are key figures in the rows of the query. Maybe because of the structure?

    Any ideas? Perhaps the function module Z_BAPI_IOBJ_GETDETAIL could be modified to work also with key figures?

    Thanks a lot and Best Regards

    (0) 
  12. Praveen Gudishetty
    Hi Raja,

    The report built is one of the best O have seen. I was succesfull in executing it with “No Parameters”. But when I use any parameters it basically gives me an error. Any insight in regards to this is greatly appreciated.

    The process flow I am using is as follows:
    DATA: WA_VAR     TYPE     RRX_VAR ,
          VAR TYPE STANDARD TABLE OF RRX_VAR.
    WA_VAR-VNAM = ‘0S_PLANT’.
    WA_VAR-SIGN = ‘I’.
    WA_VAR-OPT = ‘EQ’.
    WA_VAR-LOW = ‘1100’.
    APPEND WA_VAR TO VAR .

    WA_VAR-VNAM = ‘0S_MAT’.
    WA_VAR-OPT = ‘EQ’.
    WA_VAR-LOW = ’04-280′.
    APPEND WA_VAR TO VAR .

    It returns “No applicable data”.
    When I run the same query from RSRT with the same options, it returns data.

    Any help greatly appreciated.

    Regards
    Praveen

    (0) 
  13. Praveen Gudishetty
    Hi raja,

    The issue is with non cummulative K.F’s not variables. The variable work fine.

    I dont know why it is not capturing the non cummulative K.F values..

    Regards
    Praveen

    (0) 
  14. lionel depaepe
    Hello,

    This tool wirks fine for me except on one point.
    I have in a query a char NUMC(10), but we only display texts. In the ouptut, i get well the long texts, the the field is defined on 10 positions, and then the text is cut.
    It seems that for this field, he take the lenght of the definition of the characteritics and not what displayed in the query.

    I don’t know if’s it’s related to NUMC or not.

    ANy idea ?

    Lionel

    (0) 
  15. Sergi Marin
    Let me congratulate your blog. I’ve used your information many times to implement different requirements.

    There is the new requirement I have now: We need to call a query with defined free characteristics. Some times we need to call this query  splitting the data by a specific Free Characteristic (and appearing this characteristic as column), and some times we need to call this query without this free characteristic appearing columns.

    Do you know any way to do that? Maybe some other standard FM to implement this call?

    Thank you very much.

    (0) 
  16. Behnaz Agahian
    Thank you very much for the detailed explanations.  However, it seems that I don’t have the class CL_RSR_QUERY_VARIABLES. 

    Any suggestions.
    Thank you in advance for  your response.
    B.A.

    (0) 
  17. SD Beginner

    Hi Raja,

    Can you please point out the mistake in below code.

    I am trying to read data from the query with ABAP program which has 4 variables in the selection screen.

    1.Key date

    2.Currency

    3.Hierarchy – Type of variable is Hierarchy and Single value

    4.Hierarchy – Type of variable is Hierarchy node and multiple values

    Hierarchy is based on cost units. The variable 4 will be based on the variable 3.

    For example we have 4 hierarchies for cost units and for each hierarchy we have different nodes.

    So based on the hierarchy we selectin variable 3 , the relevant nodes we can select in variable 4.

    I am facing challenge is passing the values for hierarchy and node. Please help.

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

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

    DATA: wa_set LIKE LINE OF wa_axis-set.

    CREATE OBJECT r_request

      EXPORTING

        i_genuniid = ‘7243KWKVL2R9VY4LFMPSWLOWR2’.

    *Now set your variables if nessecary:

    CLEAR wa_var.

    wa_var-vnam = ‘YDCDAT01’.

    **

    wa_var-sign = ‘I’.

    **

    wa_var-opt = ‘EQ’.

    **

    wa_var-low =  sy-datum.

    **

    APPEND wa_var TO i_var.

    CLEAR wa_var.

    wa_var-vnam = ‘YDYEFYR’.

    wa_var-sign = ‘I’.

    wa_var-opt = ‘EQ’.

    wa_var-low =  ‘2015’.

    APPEND wa_var TO i_var.

    CLEAR wa_var.

    wa_var-vnam = ‘YHYCOS’.

    wa_var-sign = ‘I’.

    wa_var-opt = ‘EQ’.

    wa_var-low =  ‘COST UNIT’.

    wa_var-high = ‘0HIER_NODE’.

    APPEND wa_var TO i_var.

    CLEAR wa_var.

    wa_var-vnam = ‘YHCOST’.

    wa_var-sign = ‘I’.

    wa_var-opt = ‘EQ’.

    wa_var-low =  ‘C_10010’.

    wa_var-high = ‘0HIER_NODE’.

    APPEND wa_var TO i_var.

    TRY.

        r_request->variables_set( i_t_var = i_var ).

        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.

    ENDTRY.

    CLEAR wa_axis .

    LOOP AT r_dataset->n_sx_version_20a_1-axis_data INTO wa_axis .

      IF wa_axis-axis EQ ‘001’ .

        LOOP AT wa_axis-set INTO wa_set .

        ENDLOOP.

      ENDIF.

    ENDLOOP.

    Thank you..

    (0) 
    1. Vi Tran

      Raja,

      When your using hierarchy variables you need to use the following:

      VAR_NODE_IOBJNM_0=0HIER_NODE instead of what you currently have of setting the high value.

      (0) 

Leave a Reply