SAP query provides useful functionality that allows reports to be linked. This means that the output from one report can be passed to the following report so that users can drill down into the details. One feature that many users want is to be able to drill down in an SAP query to view the underlying infotype record.

A subject that Greg Newman covers in his article (mentioned above) is how to drill down from a query to an underlying or related infotype record in display master data (transaction code PA20). While it is possible to add a report assignment to view PA master data via transaction PA20 directly, this doesn't provide true drill-down functionality. This is because users have to enter some data on the view master data screen, such as infotype and subtype. I have seen many questions about how to solve this shortcoming in various blogs and forums, but I have never come across any complete instructions on how to do this. Because how to pass parameters or data between drill-down queries and transactions is not well documented, many SAP ERP HCM users struggle to get the most out of drill-down functionality.

I show how to drill down from an SAP query to view infotype level data directly (i.e., by bypassing the display master screen [transaction code PA20 view screen]). A simple standalone custom utility report is required, but no modifications to the standard SAP system or existing InfoSets or queries are necessary.

The main steps are:

1. Update the InfoSet: Update your InfoSet so that the key fields that define each infotype record are included and available for use in the query.
2. Create a utility report:  Create a small utility report that works as an interface to pass the key fields from the query into transaction code PA20.
3. Add the utility report assignment to the query: Add a report assignment to call the utility program from the query
4. Add key fields: Add the key fields for each infotype record to your ABAP query.
5. Test the new query: Finally, test and execute the new query.

Step 1. Update the InfoSet

The first step is to update your InfoSet so that the key fields (such as personnel number, infotype number, subtype, and start and end dates) that define each infotype record are available for use in ABAP queries. You may have to change your InfoSet so that these fields are available for each infotype to which you want to drill down.

For example, starting in the SAP query maintain InfoSet screen (transaction code SQ02) (Figure 1) there is an existing InfoSet called ZHR_INFOSET. Click the change button to modify this InfoSet.

Figure 1

Change the InfoSet

This InfoSet has been set up with only one infotype—infotype 0006 (addresses). In Figure 2 you can see that some of the infotype fields available on the left side of the screen have been included in Field Group/data fields 01 (Addresses) in the screen on the right. The exact fields available in your InfoSet vary according to system customization or country-specific fields.

Figure 2

Update the field group information with key infotype fields

For each infotype that you want to drill down to in transaction code PA20, you must make sure that the personnel number, infotype, subtype, and start date and end date fields are included in the field group. For example, for drill-down reporting on infotype 0006, the following fields only must be included in the field group: P0006-PERNR, P0006-infty, P0006-subty, P0006-begda, and P0006-endda (Figure 2). Make sure that these key fields are included for each infotype that you want to drill down to.

When you have added these fields to all infotype field groups, save the new InfoSet by clicking the save  icon and then generate it by clicking the generate icon . This completes the necessary changes to the InfoSet.

Step 2. Create a Utility Report

The next step is to write a small ABAP utility or helper program whose sole purpose is to pass the five parameters (which uniquely define each infotype record) when executing transaction code PA20 (display master data). In the ABAP editor (transaction code SE38) create a new report. In my example, it’s named ZCALLPA20FROMQUERY (Figure 3).

Figure 3

Create a new ABAP report

Click the create button to set the program attributes. In the program attributes sub-screen (Figure 4) set the program type as an executable program. The Package field entry depends on system-specific customizing and practice completed by a Basis or ABAP development team. This team knows which system setting to use to enable this report to be transported. Click the green checkmark icon.

Figure 4

The utility report attributes

Next a blank source code editor screen is displayed. A full copy of the source code is shown in Figure 5, and Figure 6 shows the screen as it appears once the code has been pasted into it.

Figure 5

A complete copy of the source code to be copied and pasted

Figure 6

Source code display of the utility report

Once you’ve pasted in the code, click the save, check , and activate  icons in order.

How This Works

You always execute this report from the report assignment functionality in SAP query; never run it on its own. Here are the details:

1. Define parameters for all the infotype key fields (e.g., pernr or infty). Choose the parameters to exactly match the parameters passed from the line of the ABAP query.
2. Convert the four-character field for the infotype number (infty) to a different type (choice) in order to be consistent with a subsequent call to a function module.
3. Use a standard function module to execute transaction code PA20.
4. Note that this code is completely generic. There are no specific mentions of any InfoSet or query. This means that you have to create this report only once, and then you can assign it to multiple different drill-down queries.

Step 3. Add the Utility Report Assignment to the Query

In this step you create a link between a query and the utility report via transaction code SQ01. You can either create a new query or modify an existing one. In my example (Figure 7) I have selected an existing query (HR_ADDRESSES) used for displaying employee address information. This query is based on the ZHR_INFOSET that was modified in step 1. Click the Change button to modify the query. This opens the screen shown in Figure 8 where you can change query title and format.

Figure 7

Modify an existing query

Figure 8

Maintain your new title and notes, as well as the default formatting options

In my example, I have updated the Title field (e.g., Address Listing) and Notes field (e.g., double-clicking any line in this query calls transaction code PA20), but all other fields have the default formatting options assigned (Figure 8). In the same screen you also add the report assignment. Follow menu path Goto > Report Assignment. A pop-up screen appears (Figure 9). 

Figure 9

Add the receiver report

This is the core of the drill-down functionality in which you link the main query (called the sender report) to the drill-down reports (called the receiver reports). Click the add-line icon  (Figure 9), which opens the pop-window in Figure 10. 

Figure 10

The add query default option

Leave all the fields blank and click the Other report type button. A list of other options is shown (Figure 11). Under the Restrictions tab choose the fifth option (RT ABAP Report Program) and double-click.

Figure 11

Add your ABAP report program

In the Add ABAP/4 report pop-up screen (Figure 12) enter the name of the Report (e.g., ZCALLPA20FROMQUERY) and leave the Variant field blank. Click the green check mark icon, which returns you to the Assign Reports sub-screen (Figure 13). You have now assigned the report as a receiver report.

Figure 12

Add the name of the helper report

Figure 13

Display of the assigned receiver report

Click the save icon to save this new drill-down report assignment.

Step 4. Add Key Fields

After you save your new drill-down report assignment, you are returned to the title and formatting options screen (Figure 8). You now need to add the personnel number, infotype, subtype, start date, and end date fields that you added to the InfoSet (in step 1) to the query output fields. Click the forward-arrow icon  (Figure 8) to get to the field group selection screen (Figure 14).

Figure 14

Update the field groups in your query

Choose the field groups for the infotypes that you want to display in the query. In my example, I want to add to the addresses field group, so I check the Addresses check box. Then click the forward-arrow icon, which results in the screen shown in Figure 15.

Figure 15

Update the fields in your query

In the screen in Figure 15 select the fields that you want to be shown in each line of output of the new query. For example, in the Addresses query, you choose the street, house number, city, and postal or Zip Code fields (not all of these are shown in the screen in Figure 15, but they are options). In addition, select the check boxes next to the Personnel Number, Text:Personnel Number, Infotype, Subtype, Start Date, and End Date fields to enable the infotype to drill-down to transaction code PA20.

Then click the Basic List link in the toolbar (Figure 15). In the next screen (Figure 16) you can define the order of the fields that are output by your query. This query uses a basic list line structure so that all fields are displayed as columns on the same line: Line 1. Ensure that the key infotype fields (the personnel number, infotype, subtype, start date, and end date fields) have been assigned to the same line (Line 1). They also should be given a sequence number defining the order in which they should appear in the line. For example, the infotype field has been assigned to sequence number 3; that means that the infotype number appears in the third column of each line when the query is displayed.

Figure 16

Add key infotype fields to the query output

Once you’ve made all your selections, click the save icon to save the new query.

Step 5. Test the New Query

Now you are ready to test and run your new query. Click the back arrow icon to return to the SAP query maintenance screen (Figure 7), via transaction code SQ01. Double-click the new query that you want to test and click the execute icon. The query selection screen is shown in Figure 17.

Figure 17

The selection screen for a new query

Enter a small set of selection data to test the new query; for example, enter one personnel number and Today as the key date (Reporting Period). Click the execute icon to run the query. Two address lines are displayed for this employee as he has two address subtypes—a home address and an emergency contact address (Figure 18).

Figure 18

Test a query

Double-click the first line to execute the drill-down utility report. This report enables the key parameters to be passed to transaction code PA20. The infotype record on which this line of the query is based is displayed (Figure 19).

Figure 19

Display the test infotype record in transaction code PA20

Here are some key points to keep in mind when you use this functionality:

• Make sure that the infotype, subtype, start date, and end date are included for each infotype to which you want to drill down.
• The utility report is generic and can be used with any other infotype. You can assign it to multiple queries. You need to create this report only once.
• You can drill down to only one infotype per line using this generic report. However, it is possible to create a variation of the utility report with the infotype hard-coded (e.g., to basic pay infotype 0008). You then can have multiple infotypes drill down from any one line of the query.
• You don’t have to display the infotype and subtype Begda and Endda fields in the query. These fields can be hidden if required. Initially, include all the fields for display and then run the query and show the output. Create a display variant that hides the fields that you don’t want and then set the query to run with the display variant pre-chosen in the initial selection screen.

Owen McGivney

Owen McGivney is a senior consultant at iProCon Ltd., part of the iProCon group, based in London, England. He has worked on implementing SAP HR and payroll systems since 1998. Owen has delivered UK, Irish, and multi-national payroll solutions for a wide range of private- and public-sector clients. He has a special interest in combining ABAP programming with configuration to create innovative and effective solutions.

You may contact the author at o.mcgivney@iprocon.com.

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