Skip to Content
Author's profile photo Durairaj Athavan Raja

Execute BW query using ABAP Part III

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.

Assigned Tags

      50 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member
      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.
      **********************************************
      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog 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

      Author's profile photo Former Member
      Former Member
      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 ?

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog Post Author
      open the query in query designer and right click on the characteristics to see the variable names.

      Regards
      Raja

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Steven Hebert
      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.

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog 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

      Author's profile photo Enno Wulff
      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!

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog Post Author
      you are missing the "var_sign_1=I"

      Regards
      Raja

      Author's profile photo Enno Wulff
      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
      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog Post Author
      if the query is defined to show both key and text or text you will get the text.

      Regards
      Raja

      Author's profile photo Former Member
      Former Member
      I am getting the 'bapi_syserror' is not defined please let me know why it comes

      Regards,

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog Post Author

      you may have to slightly adjust the code.

      Author's profile photo Former Member
      Former Member

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

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog 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

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog 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

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog 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

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog 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

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog Post Author
      e_cell_data is for bringing the key fig values. hierarchy node values will be in e_axis_data
      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Former Member
      Former Member
      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 ?

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog Post Author
      date has to be in user specific format.
      Author's profile photo Former Member
      Former Member
      Hi,
      Can we use the hierarchy variable in the funtion module. My query consists an hierarchy variable, how can i pass those values to it.

      Thanks in Advance
      Cheers
      Noor

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog Post Author
      yes you can and the pattern is

      VAR_NAME_ = "hierarchyvariable var name"
      VAR_VALUE_EXT_
      = "value"
      VAR_NODE_IOBJNM_ = "0HIER_NODE"

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog Post Author
      VAR_NODE_IOBJNM_1 = '0COSTELMNT'

      is wrong it should be

      VAR_NODE_IOBJNM_1 = '0HIER_NODE'

      Author's profile photo Amol Jaiswal
      Amol Jaiswal

      Hi Durairaj,

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog Post Author
      E_AXIS_DATA-attributes table will have the details.
      Author's profile photo Former Member
      Former Member
      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 !

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Former Member
      Former Member
      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

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog Post Author
      the length is defaulted to 130 chars. I would suggest you to debug the code to see where is it going wrong.

      Regards
      Raja

      Author's profile photo Former Member
      Former Member
      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.

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog Post Author
      The current code wont work if there are no characteristics in the result set. If you debug the code you will be able to easily fix this.

      Regards
      Raja

      Author's profile photo Former Member
      Former Member
      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.

      Author's profile photo Durairaj Athavan Raja
      Durairaj Athavan Raja
      Blog Post Author
      Check my answer to B. Shetty's comment.
      Author's profile photo Former Member
      Former Member
      Thank you.
      Author's profile photo Former Member
      Former Member

      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..

      Author's profile photo Vi Tran
      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.