Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
thanga_prakash
Active Contributor
59,802

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.

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

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.

28 Comments
former_member182534
Active Participant
0 Kudos

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

thanga_prakash
Active Contributor
0 Kudos

Hello Piyas,

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

Regards,

Thanga

Former Member
0 Kudos

very useful for a bigger


Did you mean beggar or beginner?

thanga_prakash
Active Contributor
0 Kudos

Thanks Thangam..... 🙂

thanga_prakash
Active Contributor
0 Kudos

Thanks Ahmed :smile:

SwadhinGhatuary
Active Contributor
0 Kudos

Hello Thanga,

Nice Document .Really Helpful .

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

thanga_prakash
Active Contributor
0 Kudos

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

christianpunz
Participant
0 Kudos

hi Thanga,

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

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

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

shahinmikailov
Explorer
0 Kudos

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

matt
Active Contributor
0 Kudos

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

shahinmikailov
Explorer
0 Kudos

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 :

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

Thanks in advance

Shahin

matt
Active Contributor
0 Kudos

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

GirieshM
Active Contributor
0 Kudos

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

shahinmikailov
Explorer
0 Kudos

hi Matthew,

Problem solved using debug. Thanks for tip :smile:

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

Shahin

matt
Active Contributor
0 Kudos

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

thanga_prakash
Active Contributor
0 Kudos

Hello Giriesh,

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

Regards,

Thanga

timmybecker
Participant
0 Kudos

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

timmybecker
Participant
0 Kudos

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.

christianpunz
Participant
0 Kudos

*hehe* tricky.

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

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

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

br

christian

timmybecker
Participant
0 Kudos

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?

christianpunz
Participant
0 Kudos

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

br

christian

timmybecker
Participant
0 Kudos

Hi Christian,

it works peferfectly now! :smile:

Thanks you very much!!

Have a nice evening.

thanga_prakash
Active Contributor
0 Kudos

Hello Christian,

Thanks for helping Timmy :smile:

0 Kudos
 

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
marco_sposa
Participant
0 Kudos
Hy thanx for that great tutorial, how can i convert those data in internal table ( in order to send them as xls attachment?)
dr_ajt
Participant
0 Kudos
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.

 
norbertkeseler
Discoverer
0 Kudos
Hi all,

 

I know this is very old...

Did anyone do this include with class CL_RSDA_CSV_CONVERTER and could share the relevant code changings?

Many thanks and regards

Norbert
0 Kudos
Hi Prakash,

Thank you for the insightful post.

Currently there are reports set up in the SQ00 screen to pull data which we would like to schedule on a daily basis. We would like to know what options we have on format of this export and if it could be saved as a logical file where it could be picked up and imported into our data warehouse (Internal Cognos Team).

Regards

Reddy
Labels in this area