Skip to Content
Author's profile photo Thanga Prakash Thanga Raj

How to run SAP query in background and create a file in application server

It is possible to run SAP queries in background and to create a query output as a file in application server.

Follow below steps to implement the same.

1) As per OSS note 537735 – SAP Query: save to file in the background activate the enhancement SQUE0001 using the t.code SMOD.

2) Once the above enhancement is activated, it will add a new radio button option with text “Private file” under the output format block of the Query selection screen.

2-4-2014 3-00-13 PM.jpg

3) Create a Z table and maintain the sever filepath in which the files to be stored in application server.

2-4-2014 3-27-48 PM.jpg

4) Go to the function exit “EXIT_RSAQEXCE_001” and do your customized coding in the INCLUDE ZXQUEU01.

*Data Declaration.

DATA : c_lv_buf TYPE string,

      c_lv_line TYPE string,

      c_lv_filepath TYPE localfile,

      c_lv_query TYPE aqs_quname.

*Field symbols

FIELD-SYMBOLS : <fs_record> TYPE ANY,

                <fs_comp> TYPE ANY.

*Get a Query name from the program

CALL FUNCTION ‘RSAQ_DECODE_REPORT_NAME’

  EXPORTING

    reportname    = program

  IMPORTING

    query          = c_lv_query

  EXCEPTIONS

    no_query_report = 1

    OTHERS        = 2.

IF sy-subrc <> 0.

  MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

*Select the filepath from ZBSD_T0246 table based

*on the query name and variant.

SELECT SINGLE filepath

  FROM zbsd_t0246

  INTO c_lv_filepath

  WHERE query = c_lv_query

  AND qvariant = syst-slset.

IF sy-subrc = 0.

*Open application server file.

  OPEN DATASET c_lv_filepath FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

  IF sy-subrc = 0.

    CLEAR c_lv_line.

*Create header of the file

    LOOP AT listdesc.

      CONCATENATE c_lv_line listdesc-fcol ‘;’ INTO c_lv_line.

    ENDLOOP.

    TRANSFER c_lv_line TO c_lv_filepath.

    CLEAR c_lv_line.

*Transfer the data to the file

    LOOP AT datatab ASSIGNING <fs_record>.

      DO.

        ASSIGN COMPONENT sy-index OF STRUCTURE <fs_record> TO <fs_comp>.

        IF sy-subrc <> 0.

          EXIT.

        ENDIF.

        c_lv_buf = <fs_comp>.

        IF sy-index = 1.

          c_lv_line = c_lv_buf.

        ELSE.

          CONCATENATE c_lv_line c_lv_buf INTO c_lv_line SEPARATED BY ‘;’.

        ENDIF.

        CLEAR c_lv_buf.

      ENDDO.

      TRANSFER c_lv_line TO c_lv_filepath.

      CLEAR c_lv_line.

    ENDLOOP.

*Close the file once the datas are transfered.

    CLOSE DATASET c_lv_filepath.

    IF sy-subrc = 0.

*File created in path &1

      MESSAGE s483(zbsd_0001) WITH c_lv_filepath.

    ENDIF.

  ENDIF.

ELSE.

*File path not maintained in table ZBSD_T0246

  MESSAGE e484(zbsd_0001).

ENDIF.

5) Run the query in the background and the files will be created in the application server path maintained in Z-table.

Assigned tags

      26 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Piyas Kumar Das
      Piyas Kumar Das

      Hi Thanga,

      Nice Blog.....very useful for a bigger ....could have added some more images and a screen shot of how the file would look...needs a bit more explanation and details in some sections.

      Cheers

      Piyas

      Author's profile photo Thanga Prakash
      Thanga Prakash
      Blog Post Author

      Hello Piyas,

      Thanks for the feedback. Will improve in the next version of the document.

      Regards,

      Thanga

      Author's profile photo Former Member
      Former Member

      very useful for a bigger

      Did you mean beggar or beginner?

      Author's profile photo Thanga Prakash
      Thanga Prakash
      Blog Post Author

      Thanks Thangam..... 🙂

      Author's profile photo Thanga Prakash
      Thanga Prakash
      Blog Post Author

      Thanks Ahmed 🙂

      Author's profile photo swadhin ghatuary
      swadhin ghatuary

      Hello Thanga,

      Nice Document .Really Helpful .

      I need a know that do we use this approach in saving background job file in local ?

      Author's profile photo Thanga Prakash
      Thanga Prakash
      Blog Post Author

      Hello Swadhin,

      Thanks. It depends on the coding which you do inside the enhancement.

      I have done it in such a way that it will store files in application server.

      Might be you can do a coding to save files in the presentation server by using GUI_DOWNLOAD. instead of OPEN DATASET, TRANSFER and CLOSE DATASET.

      Regards,

      Thanga

      Author's profile photo Giriesh Chakaravarthy Mamurugan
      Giriesh Chakaravarthy Mamurugan

      Hi Thanga,

      Thank you for the post.

      Need a clarfication, you are running the query in BG mode right? Does it accepts the GUI_DOWNLOAD fm, which normally throws dump in BG.

      With Regards,

      Giriesh M

      Author's profile photo Matthew Billingham
      Matthew Billingham

      Gui download and background are mutually exclusive. This is common knowledge, surely?

      Author's profile photo Thanga Prakash
      Thanga Prakash
      Blog Post Author

      Hello Giriesh,

      You cannot use GUI_DOWNLOAD in the background. You can use it only when you run it interactively.

      Regards,

      Thanga

      Author's profile photo Christian Punz
      Christian Punz

      hi Thanga,

      very helpful, works fine and saved me time I could use for other stuff 🙂

      e.g. I kicked the Z-table and now I fetch the path and filename directly from the selection screen.

      2014-06-02 13_35_10-MLE(4)_100 SAP Query.png

      the code snippet:


      * fetch the path and file from the Query-Parameter %PATH

           lv_global_field = '(' && program && ')%PATH'.

           assign (lv_global_field) to <pathname>.

           if sy-subrc  0 and <pathname> is assigned.

             lv_filepath = <pathname>.

           endif.


      remark: do not use the variable %PATHNAME as it is empty while running in background!


      br

      christian

      Author's profile photo Timmy Becker
      Timmy Becker

      Hi Christian,

      I was thinking of the same idea. But the code snippet is not working. It seems not to possible to pick the value from Screen Field %PATH with the snippet.

      Did it really work for you?

      Thanks for your feedback.

      Author's profile photo Christian Punz
      Christian Punz

      *hehe* tricky.

      started the query directly in SQ01 and found that the assign does not fetch the %PATH 😯

      then I started via program SAP_QUERY_CALL (the one to use when executing a query via job) and everything fine:

      2015-03-31 16_09_03-MLE(4)_100 ABAP Debugger(5)  (exklusiv)(atvies9912vx_MLE_23).png

      and yes, the customer drives the thing in a daily manner.

      br

      christian

      Author's profile photo Timmy Becker
      Timmy Becker

      Hi Christian,

      thanks a lot for your quick response. I'm going to try it in backround later.

      But if I use the code as you're using it I get a syntax error. Strange.

      The <pathname> I've declared as a field-symol and TYPE ANY.

      The was declared like this: lv_global_field TYPE localfile.

      Do you have a guess on that?

      Unbenannt.JPG

      Author's profile photo Christian Punz
      Christian Punz

      i think you have an "old" release which can't interpret the &&-operator. please use CONCATENATE instead.

      br

      christian

      Author's profile photo Timmy Becker
      Timmy Becker

      Hi Christian,

      it works peferfectly now! 🙂

      Thanks you very much!!

      Have a nice evening.

      Author's profile photo Thanga Prakash
      Thanga Prakash
      Blog Post Author

      Hello Christian,

      Thanks for helping Timmy 🙂

      Author's profile photo Shahin Mikailov
      Shahin Mikailov

      Dear Thanga,

      At first , thanks a lot for post. I tried to run query , but always getting the error e484(zbsd_0001). All tasks done as you described. Please help.

      Shahin

      Author's profile photo Matthew Billingham
      Matthew Billingham

      The comment above that error in the code should give you a clue... or have you maintained a file path in table ZBSD_T0246?

      Author's profile photo Shahin Mikailov
      Shahin Mikailov

      Hi Matthew,

      Thank you for quick response. Yes, all three fields maintained as required, but job doesnt generate excel file. Table structure added to post :

      ScreenClip.png

      ScreenClip.png

      also, if to start query from SQ01 in bg mode , job finishes with success.

      ScreenClip.png

      ScreenClip.png

      Thanks in advance

      Shahin

      Author's profile photo Matthew Billingham
      Matthew Billingham

      Run in debug and find out why your select isn't coming back with Sy-subrc = 0.

      Author's profile photo Shahin Mikailov
      Shahin Mikailov

      hi Matthew,

      Problem solved using debug. Thanks for tip 🙂

      but know we have another problem. How can we transfer data in utf8?

      Shahin

      Author's profile photo Timmy Becker
      Timmy Becker

      Hi, unofortunately I've some trouble to make it run smoothly.

      I got a error calling the FM RSAQ_DECODE_REPORT_NAME .

      It says the Parameter Query is not defined. I'll try to set the parameters manually and check if it works.

      Update: I found the reason. The IMPORTING statement was commeted.

      CALL FUNCTION 'RSAQ_DECODE_REPORT_NAME'
         EXPORTING
           REPORTNAME            = program
        IMPORTING   <----------
      *   WORKSPACE             =
      *   USERGROUP             =
           QUERY                 = c_lv_query
      *   CLIENT                =
        EXCEPTIONS
          NO_QUERY_REPORT       = 1
          OTHERS                = 2

      Author's profile photo Manish Agrawal
      Manish Agrawal

       

      Hello Thanga,

       

      Really Nice post.. but we have a different problem...

       

      Although we have followed the steps but it is not showing the 'Private File' Option at all. I even tried placing a debugger inside the Exit but it is not hitting there ...

       

      Any suggestions

       

      Regards

      Manish Agrawal

      Author's profile photo Marco Sposa
      Marco Sposa

      Hy thanx for that great tutorial, how can i convert those data in internal table ( in order to send them as xls attachment?)

      Author's profile photo Adam Trickett
      Adam Trickett

      While this will work it will not always create valid CSV data. CSV data is actually hard to generate and parse and would be much better off using a SAP standard class to generate your CSV line than doing it by hand as you have done here.

      The SAP class CL_RSDA_CSV_CONVERTER has been available on most SAP ECC systems since 2004 and is a better way of turning structured data into CSV data. It is slightly more involved that the example here, but it is more robust.

       

      One can also generate Excel files in the background using the CL_SALV_TABLE and the TO_XML method. Which is sometimes more useful for users than CSV data.