Your company uses SAP Query to meet its reporting needs. Some queries run weekly or monthly. Have you ever wondered if you could set them up to run automatically? Well, it is possible. SAP allows you to run the query in the background and send the output to a shared location or to send the output to users in their SAP inbox or external email account.

I’m going to show you the option of sending the output to a shared location. I’ll explain the other option of sending the output to users in their SAP inboxes or external email accounts in a subsequent article.

You need to follow these steps to send the output to a shared location:

• Step 1. Set up the query: Select the output format, dynamically default the dates, and save the query with a variant
• Step 2. Write the ABAP code to put the file on the shared location
• Step 3. Set up the job to run in the background

Step 1. Set up the query. Execute the transaction SQ01 and select the query you wish to set up to run automatically. Click on the Execute button. Click on the output format Private file under the Program selections tab on the query selection screen (Figure 1).

Figure 1

Select the output format

Next, you dynamically default the dates on the query selection screen: You always select data based on a certain date range. Now that you will be running the query automatically, you need to update the selection dates automatically every time the query is executed. You do this via menu path Goto>Variants>Save as variant. Enter the Variant name and Description (Figure 2).

Figure 2

Enter the Variant name and Description

Let’s look at an example. Assume that there is a requirement to run a query every Tuesday with a selection period of the previous Saturday to the following Friday. To meet these requirements, you need to set up the query so that the “from” date is three days prior to the run date and the “to” date is three days later.

To set this up, scroll down on the variant screen (Figure 3) and select the check box for Selection variable (Field attribute type L) under the Field attributes section for the Data selection: From and Data selection: To date fields. Click on the button Selection variables at the top left of the screen. You then see the screen shown in Figure 4.

Figure 3

Select the date range (variants screen)

Figure 4

Select the green traffic light button in the D column

Select the green traffic light button under the column D (dynamic date calculation) for the Data selection: From field and select the down arrow icon.

Here you get various options to default the dates. To meet the requirements illustrated in my example, select the option Current date +/- ??? days (Figure 5) and click on the Choose button.

Figure 5

Select the date calculation

Considering that you plan to run the query every Tuesday, enter the number 3- so that the start date for the selection period can set as a Saturday (Figure 6).

Figure 6

Set Saturday at the start date

Follow a similar procedure to designate Friday as the end date for the selection period. The screen will look like what you see in Figure 7. Save the variant.

Figure 7

Dynamic date selection screen

To verify that the dates are populating correctly, execute the transaction SQ01, and select the variant that you have set up to dynamically default the dates. The selection dates should automatically default as shown in Figure 8.

Figure 8

Selection dates automatically default

When the query is run automatically every Tuesday, the system updates the selection dates to provide the data from the previous Saturday to the following Friday.

Various variant options are available on the variants screen that can be used effectively for background reporting. Among them are:

• Only for background processing: If you select this check box, then the variant can only be executed in the background. Otherwise, it can be run both in the background or foreground. You can select this to prevent other users from viewing the variant in the drop-down menu on the query selection screen.
• Protect variant: If you select the field Protect variant, then the variant can only be changed by the person who created it or last changed it. It is useful when you have a scenario in which one power user is creating queries for a certain location or department. By choosing this option, other users can execute the query with the variant but are not be able to change it. This is useful from an authorization standpoint.
• Only display in catalog: Select this field if you only want the variant name to be displayed in the variant catalog via transaction SE38 (and not when the user calls the F4 value help). This is an additional level of security for the variants that are used to run in background.
• Protect Field (Field attribute type P): Select this check box for each selection field that you want to write-protect on the query selection screen. These fields are visible on the query selection screen when the user starts the query with the variant, but do not accept user input.
• Hide field (Field attribute type I): If you select this check box, the system hides the corresponding field on the query selection screen. This allows you to change the appearance of the query selection screen. When you display the variant in the variant catalog, these selection criteria are displayed under Protected/Invisible parameters column on the Attributes section.
• Selection variable (Field attribute type L): As discussed in detail in my example.
• Switch GPA off (Field attribute type L): This attribute only appears if you created the corresponding selection criterion using PARAMETER ID xxx. You can switch the SET Parameter/ GET Parameter (SPA/GAP) handling on and off in the variant. You can use this field in conjunction with the Selection variable (Field attribute type L) to default User Defined Variables.
• Required field (Field attribute type O): Select this check box for each selection field that you want to make mandatory on the query selection screen.

Step 2. Write the ABAP code to put the file on the shared location. To write the ABAP code, you first create a project ZSQUE001 by calling a CMOD transaction (Figure 9).

Figure 9

Create a project

Enter a short description for the project and select the Enhancement assignments button.

Assign SQUE0001 as the enhancement and select the Components button.

You see two components on the screen (Figure 10). They are the Function exit EXIT_RSAQEXCE_001 and the Menu exit RSAQEXCE.

Figure 10

Two components available

Double-click on the Function exit EXIT_RSAQEXCE_001. This takes you to the function module editor (SE37).

Double-click on INCLUDE ZXQUEU01. This is where you can write your own logic to send the output to the shared location in different formats such as XLS, CSV, etc. Figure 11 provides sample logic to ftp a tab-delimited file to a defined shared location.

** Include ZXQUEU01 starts here****
Data: Filename(128).
** < Internal Table for Data Transfer >
DATA: BEGIN OF I_TAB_FINAL OCCURS 0,
        LINE(2000) TYPE C,
 END OF I_TAB_FINAL.
** < Header Record >
DATA: V_HEADER(2000) TYPE C.
DATA: Tab TYPE X VALUE ‘09’.                          “Tab Delimiter
**>>—————————————————————————————————**
**  Section 1 TRANSFER COLUMN HEADINGS to Internal Table I_TAB_FINAL  **
**——————————————————————————————————**
** LISTDESC has all the Column Headings                               **
Loop at LISTDESC.
  Concatenate V_Header ListDESC-FDESC Tab into V_Header.
Endloop.
Move V_Header to I_TAB_Final-Line.
Append I_TAB_Final.
**<<————————————————————————————————-**
**>>————————————————————————————————-**
**   Section 2          CONVERT DATATAB to TAB OR CSV Format         **
**—————————————————————————————————-**
** Here you can use your own Custom FM to create TAB Delimited Format
CALL FUNCTION ‘Z_CREATE_CSV_FROM_INTERNAL_TAB’
     EXPORTING
          I_SEPARATOR               = ‘ ‘
     TABLES
          SOURCE_CONTENTS           = DATATAB
          CSV_TABLE                 = I_TAB_FINAL
     EXCEPTIONS
          NESTED_STRUCTURE_IN_TABLE = 1
          UNABLE_TO_PRESERVE_SPACE  = 2
          OTHERS                    = 3.
**<<—————————————————————————————————**


**>>—————————————————————————————————**
**   Section 3          File Transfer Details                         **
**——————————————————————————————————**
DATA: LOCAL_PATH(40) Value ‘/us/tst/’,
      LOCAL_FILE(128) value ‘local_file .txt’,
      REMOTE_HOST(20) value ‘RHOST’,
      REMOTE_FILE(128) value ‘remote_file .txt’,
      REMOTE_PATH(40) value ‘S:tmp’,
      Multi_File(1) Value ‘N’.
** Define Variables for FTP
      DATA: ld_comline  LIKE sxpgcolist-name,
      ld_param    LIKE sxpgcolist-parameters,
      ld_status   LIKE extcmdexex-status,
      ld_output   LIKE btcxpm OCCURS 0 WITH HEADER LINE,
      ld_subrc    LIKE sy-subrc.


** << Create Local File on UNIX >>
Concatenate LocaL_Path Local_File into Filename.
  Open Dataset Filename for Output in Text Mode.
  If Sy-Subrc <> 0.
    Write: / ‘Error opening File’, Filename.
  Else.
    Loop at I_TAB_Final.
      Transfer I_TAB_Final to Filename.
    Endloop.
    Close Dataset Filename.


  REFRESH ld_output.
  MOVE ‘FTP_DATA_IN’ to ld_comline.        “Maintained using trans SM69
 CONCATENATE LOCAL_PATH
             LOCAL_FILE
             REMOTE_HOST
             REMOTE_FILE
             REMOTE_PATH
             ‘ascii’
             MULTI_FILE
             INTO ld_param SEPARATED BY SPACE.
* Execute external command, contained in ‘ld_comline’
  CALL FUNCTION ‘SXPG_COMMAND_EXECUTE’
       EXPORTING
            commandname                   = ld_comline
           additional_parameters   = ld_param  “Params passed to script
*           operatingsystem               = ‘UNIX’
       IMPORTING
            status                        = ld_status
       TABLES
            exec_protocol                 = ld_output
       EXCEPTIONS
            no_permission                 = 1
            command_not_found             = 2
            parameters_too_long           = 3
            security_risk                 = 4
            wrong_check_call_interface    = 5
            program_start_error           = 6
            program_termination_error     = 7
            x_error                       = 8
            parameter_expected            = 9
            too_many_parameters           = 10
            illegal_command               = 11
            wrong_asynchronous_parameters = 12
            cant_enq_tbtco_entry          = 13
            jobcount_generation_error     = 14
            OTHERS                        = 15.
  IF sy-subrc NE 0.
  ENDIF.
Endif.
**<<—————————————————————————————————**
**>>—————————————————————————————————**
** Additional Information: The Parameter PROGRAM has Name of User Group
**                        from Char 5 to Char 16 (12 Char Long) Query
**                        Name from Char 17 to Char 30 (14 Chars long).
**                        If the names are shorter than the defined
**                        length, they are padded with char ‘=’. The 
**                        following can be used to create a Custom Table 
**                        where we can maintain the Local and Remote System 
**                        information 
**——————————————————————————————————**
Data: user_group(12),                     “User Group
      Query_Name(14),                     “Query Name
      Variant(14).
user_group = Program+4(12).  “From 5 to 16 Chars has name of USER GROUP
Query_Name = Program+16(14). “From 17 to 30 Chars has Query Name
Shift: user_group Right Deleting Trailing ‘=’,
       Query_Name Right Deleting Trailing ‘=’,
       user_group Left Deleting Leading Space,
       Query_Name Left Deleting Leading Space.
       Varient = SY-SLSET.
**<<—————————————————————————————————**

Step 3. Set up the job to run in the background. To set up the query to run in the background, execute the transaction SQ01, select the query for which you want to set up the job, and then click on the Execute button. Open one more session and execute the transaction SM36. Enter the Job name or short description to identify your job and the Job class to classify the background job according to priority. Click on the Step button (Figure 12). Note that the options for Job class are A, B and C, indicating high, medium or low priority, respectively.

Figure 12

Enter the job name and the job class

The generated program name for the query automatically defaults on the following screen (Figure 13). Select the variant that you would like to automate and click on the save icon. Click on the Start condition button you saw in Figure 13 to define the periodicity for the job.

Figure 13

Name generated from the default

On the next screen, select the option Date/Time. Enter the start date and time for the job, check the option Periodic job, and select the button Period Values. Click on the Weekly option and then on the save icon.

Save the start condition and then click on the save icon on the Define Background Job screen to release the job. You should get the message Job Z_QUERY_SHARED_DRIVE_OUTPUT saved with status: Released at the bottom of the screen.

Now the query is set to run in background at the defined frequency/time and the output will be sent to the shared location as defined in the Function exit EXIT_RSAQEXCE_001.

Atul Bakliwal

Atul Bakliwal is a seasoned, pragmatic SAP ERP HCM/SuccessFactors leader with over 20 years of deep expertise in solutioning, planning, managing, and delivering multi-faceted global implementations. He currently works for IBM India as a Service Delivery Leader for SuccessFactors/SAP ERP HCM, managing multiple implementations and AMS projects of various sizes and complexities in the SuccessFactor/SAP HR space.

You may contact the author at bakliwalatul@hotmail.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.