Modifying SAP Query Reports

Objective

After completing this lesson, you will be able to execute reports that include specific and local fields using SAP Query

SAP Query Output

The system guides you through the following screens for creating a basic list:

  1. Basic List Line Structure:

    This screen enables you to arrange fields in single or multiple lines, and determine the field sequence. It also enables you to determine the sort sequence and other global field characteristics.

    Control level processing can be defined for sorted fields. Summation and field counting are possible for each control level.

  2. List Line Output Options:

    This screen enables you to determine output options for each line. The appearance of the Output depends on whether another line exists, output for blank lines (before and after a line), page breaks, and output in the page header.

  3. Field Output Options:

    This screen enables you to determine output options for each field, such as output length, output position, and output with template (in this case, using an additional Field Templates screen).

  4. Basic List Header:

    This screen enables you to enter text for the footer and header, and to change the text for column headers.

  5. Graphics:

    This screen enables you to determine the graphic type that is used if the list is output as a graphic.

Creation of Control Level Lists

If a sort sequence is specified for the line structure, the screen sequence branches to control level definition.

The system guides you through the following screens for creating a control level list:

  1. Control Levels

    This screen enables you to determine the following attributes for each control level:

    • Ascending or descending control level sorting
    • Use of an introductory control level text
    • Summation
    • Counting
    • Frame
    • Blank line
    • New page

    If you use the summation function for a field, the total is output in the same column as the field, that is, with the same output length. This means that the output length is sometimes too short to output the total, which causes an overflow (an asterisk at the first position of the value).

    To avoid such overflows when totals are output, you can simply extend the output length of the field for which the summation function is used.

  2. Control Level Texts (Totals Texts)

    This screen enables you to enter a text that is then output at the beginning of a control level (for a total). In the standard system, the text of the corresponding field is used.

The system takes you from this screen to the List Line Output Options screen.

Creation of Statistics

Statistics are used to perform analytical reporting for numerical data. The system does not output the values of numerical fields. Instead, it includes a summation function for the contents of fields, counts the number of processed records, and can output mean and percentage values.

The system guides you through the following screens for creating statistics:

  1. Statistic 1 Structure (Statistic 2, Statistic 3, and so on)

    This screen enables you to define the following settings:

    • Basic settings such as field sequence, sort sequence, calculation of subtotals, field length, and use of a field text for outputting a graphic
    • Settings for processing numerical fields such as counting processed data records, mean values, the value as a percentage of the total, and rounding

  2. Headers:

    This screen enables you to enter text for the footer and header. You can also change the text for column headers.

The list includes the conversions performed by the system. If errors occur, the conversions that could not be performed are logged. The affected currency amount fields or quantity fields are also highlighted within the statistics.

Depending on the definition, subtotal lines can occur within statistics. As a result of summarized statistics, the system only displays subtotals and the total.

Creation of Ranked Lists

Ranked lists are special types of statistics that are sorted in the standard system by the 10 highest values of a numerical field. This means that you can only define one numerical field as a ranked list criterion for a ranked list.

The system guides you through the following screens for creating ranked lists:

  1. Ranked List 1 Structure (Ranked List 2, Ranked List 3, and so on):

    This screen enables you to define the following settings:

    • Basic settings such as the field sequence, field length, and use of a field text for outputting a graphic
    • Settings for processing numerical fields (as a ranked list), such as specifying a ranked list criterion and rounding

  2. Headers:

    This screen enables you to enter text for the header and footer. You can also change the text for column headers.

Additional Fields

The term additional fields covers all the fields that do not exist in the database table of the corresponding infotype, but that are available for reporting purposes.

Additional fields are classified as follows:

Standard additional fields:
Standard additional fields are additional fields that are required by the majority of customers. For this reason, they are available in the standard system when InfoSets are created.
Additional fields from Customizing or InfoSet:
 Additional fields can also be defined by customers to meet special, company-specific requirements. They can be created in HR Customizing or when an InfoSet is created.
Local fields:

Local fields are similar to additional fields and are used to meet specific requirements. They are defined within a query and are available only for that query (for instance in SAP Query).

When these additional fields are created in Customizing, they are always available when InfoSets are created. If they are created in an InfoSet, they are only available in that particular InfoSet. To create additional fields from Customizing or an InfoSet, choose Personnel Management → Human Resources Information System → HR Settings for SAP Query → Additional Information for Maintaining InfoSets (Functional Areas) → Define Additional Fields.

Date Selection

Many times, the reporting period as of a single (key) date or multiple (From and To) date is sufficient to return the data you have requested. The reporting period enables you to determine the period from which objects are retrieved. The system searches for objects with valid infotype records that meet the selection criterion in the period you specify.

However, because the reporting period also affects data output, there may be cases in which you need to separate the Person Selection Period and Data Selection Period on the report selection screen.

The following example shows how to determine the reporting period:

  • You want to generate a list of addresses to be used for a mailing to all persons who were active last year in a specific Personnel Area and Personnel Subarea.
  • You want to use the most current Addresses infotype record, subtype 1 for Permanent Residence.

In this case, the Person Selection Period would be from the first of last year through the end of last year, and the selection criteria would be based on an Employment Status of 3 (Active), along with the appropriate Personnel Area and Personnel Subarea.

The Data Selection Period would be today, and the selection criteria would be based on subtype 1 (Permanent Residence) of the Addresses infotype.

Any employee who was active in that Personnel Area and Personnel Subarea last year will then be included in the output of the data; however the address for any employee who moved after last year would show the record intersecting with "today" rather than the address record that was valid last year.

Results

The resulting list of addresses will be exactly what you requested.

You should keep in mind that the data record(s) returned in the output of your report will also depend on the time constraint of the infotype or subtype. In the above example, subtype 1 Permanent Residence of the Addresses infotype has a time constraint of 1, which means that the record must exist without any gaps or overlaps in its’ existence. Therefore, as of a specific date, the system will always return one, and only one record per employee.

If the time constraint of the data record is a 2, however, such as subtype 3 Home Address of the Addresses infotype, the results as of a specific date may be different. This is because a time constraint of 2 means that the record may exist, and gaps are allowed, though overlaps cannot occur. In this case, an employee may or may not have a record of this subtype as of a specific date. If they do have a record of this subtype, however, there can only be 1 record for the specific date.

If the time constraint of the data record is a 3, such as subtype 2 Temporary Residence of the Addresses infotype, the results of a specific date may be different again. This is because a time constraint of 3 means that the record may exist, gaps are allowed, and overlapping records are allowed. In this case, an employee may have no records, 1 record, or multiple records of this subtype as of a specific date.

Generate a List Query with a Control Level

ExerciseStart Exercise

Business Example

As part of your job, you need to generate reports on all employees within the organization by creating queries with the SAP Query tool and using a basic list with one control level.

With the InfoSet S4HR58PNPMINI, define a query Q2S4HR58## with a basic list, which outputs the Organizational unit, Last name, First name, For period for payroll, Total gross amount, and Employer SI expenses fields.

Payroll records for <text for OU1>
Last name (L: 10)First name (L: 10)For-period (L: 6)Total Gross (L: 15)Employer SI Insurance (L: 15)
MillerJohn20041213.0006.000
.........  
.........  
Total for OU <text for OU1>500,000 EUR300,000 EUR

Information on the output format (sample list) is as follows:

  • Define the Organizational unit field as a control level.
  • Display totals for the numerical fields by using the Organizational unit field.
  • Adapt the text for the control level and the total for the organizational unit in accordance with the sample list.
  • Adapt the length of the fields in accordance with the sample list.

Steps

  1. Execute the query for personnel area 1000 and 1300 and the evaluation period January to December of 2024. Output the query in the ABAP List, SAP List Viewer, Display as table output formats

    1. On the Query from User Group S4HR58##: Initial screen, enterQ2S4HR58## in the Query field  and choose Create.

    2. On the Restrict Value Range screen that appears, select the InfoSet S4HR58PNPMINI and choose Copy.

    3. In the Title field, enter Q2S4HR58##, choose ABAP List in the output format screen area, and choose Next Screen.

    4. Select the Organizational Assignment, Personal data, and Payroll Results field groups and choose Next Screen.

    5. Select the Organizational Unit, Last Name, First Name, For-period for payroll, Total gross amount, and Employer SI expenses fields and choose Next Screen.

    6. Choose Basic List to access the screen for basic list definition.

    7. In the Line field, enter 01 for the Last Name, First Name, For-period for payroll, Total gross amount, andEmployer SI Expenses (single-line basic list), and define the sequence provided in the table.

    8. Enter sort position 01 for the Organizational unit field. Select the Total checkbox for the gross amount and Employer SI expenses fields and choose Next Screen.

    9. Select the Text and Total checkboxes for the organizational unit control level and choose Next Screen.

    10. Enter Payroll records as an alternative text for the control level. Enter Total for OU as the subtotal text.

    11. Choose Next Screen twice and enter the field lengths according to the table provided.

    12. Choose Save.

    13. Choose Query → Execute → Execute, and start the query for personnel area 1000 and 1300, and a Reporting Period of Other Period from 01 January 2024 to 31 December 2024. Execute the query for the specified output forms.

    14. Return to the query from the User Group S4HR58##: Initial screen.

Generate a Basic List Query with Two Control Levels

ExerciseStart Exercise

Business Example

As part of your job, you need to generate reports on all employees within the organization by creating queries with the SAP Query tool and using the basic list with two control levels.

Steps

  1. Copy the query Q2S4HR58## and rename it Q3S4HR58##.

    Define the Last name field as a control level, and use the control level to calculate totals.

    Output the following text for the control level total, Total for employees.

    Execute the query as an ABAP List output format for personnel area 1000 and 1300 for calendar year 2024

    1. On the Query from User Group S4HR58##: Initial screen, enter Q2S4HR58## in the Query field. Choose Query → Copy. In the Copy a Query dialog box, enter Q3S4HR58## in the To field. Choose Continue and choose Change.

    2. Choose Basic List and on the Change Query Q3S4HR58: Basic List Line Structure screen, enter sort position 02 for the Last Name field. Choose Next Screen.

    3. Select the Total for the Last Name control level. Choose Next Screen twice.

    4. Enter Total for Employee as an alternative text for the new control level total. Choose Save.

    5. Choose Query → Execute → Executeand specify the parameters as instructed in the exercise.

    6. Enter Company code 1000 and 1300, and choose a Reporting Period, using the dates 01 January 2024 to 31 December 2024. Choose Execute.

    7. Return to the Query from User Group S4HR58##: Initial screen.

Generate a Basic List Query with One Control Level and Statistics

ExerciseStart Exercise

Business Example

As part of your job, you need to generate reports on all employees within the organization by creating queries with the SAP Query tool and using a basic list with one control level and statistics.

Steps

  1. Copy the query Q2S4HR58## and rename it Q4S4HR58##. Define statistics with which the following data is output:

    • Total of evaluation wage type Gross per organizational unit
    • Total of evaluation wage type ER SI Expenses per organizational unit
    • Percentage of each total evaluation wage type for each organizational unit
    • Average amount within the organizational unit allotted for each employee (for both evaluation wage types)

    A sample list (header) is as follows:

    Organizational UnitsTotal Gross AmountProportion in % Average Employer SI ExpensesProportion in % Average 
    (Length:15)(Length:15)% (Length:15)% 
    • Execute the query as an ABAP Listfor personnel company code 1000 and 1300 and evaluation period All.

    1. To copy your query, on the Query from User Group S4HR58##: Initial screen, double click Q2S4HR58## choose Copy. In the Copy a Query dialog box, enter Q4S4HR58## in the To field and choose Continue. Choose the Change pushbutton.

    2. On the Change Query Q4S4HR58##: Title, Format screen, choose Statistics to navigate to the Statistics 1 Structure screen.

    3. Enter the title Q4S4HR58## and assign sequence numbers and field lengths to the Organizational Unit, Total gross amount, and Employer SI expenses fields according to the information in the table. Enter the reference currency EUR for the numeric fields.

    4. Activate the Average value and Percentage share checkboxes for the Total gross amount and Employer SI expenses fields, and choose Save.

    5. Choose Query → Execute → Executeand specify the parameters as instructed in the exercise, for company code1000 and 1300, and a Reporting Period of Other Period from 01 December 2024 to 31 December 2024. You will need to scroll to the bottom of the query to see the statistics.

    6. Return to the Query from User Group S4HR58##:Initial screen.

Generate a Basic List Query with One Control Level and Local Fields

ExerciseStart Exercise

Business Example

As part of your job, you need to generate reports on all employees within the organization by creating queries with the SAP Query tool and using a basic list with one control level and local fields.

Copy query Q2S4HR58## and rename it Q5S4HR58##. Define a local field called Net for the new query that outputs the difference (Gross - ER SI Expense). Include the field in the basic list.

Steps

  1. Execute the query (Company code 1000 and 1300 and period Calendar Year 2024. Output the query once as an ABAP List, once as a table, and once using the SAP List Viewer.

    1. To copy your query, on the Query from User Group S4HR58##: Initial screen, select your query Q2S4HR58## and choose Copy. In the Copy a Query dialog box, enter Q5S4HR58## in the To field and choose Continue. Choose the Change pushbutton. Change the Columns value to 200.

    2. Within the new query Q5S4HR58##, choose Next Screen to navigate to the Change Query Q5S4HR58##: Select Field screen. Activate the short names by choosing Edit → Short Names → Switch on/off.

    3. Enter short names such as GRS and DED for the Total gross amount and Employer SI expenses fields.

    4. Choose Edit → Local Field → Create.

    5. In the Field definition dialog box, enter a short name of NET, field description of NET, and heading Net. Use the properties to determine that the field has the same properties as the gross amount (GRS) field.

    6. Enter the following calculation formula: GRS—DED (use the short names you entered) and choose Continue.

    7. Choose the Basic List pushbutton  and on the Change Query Q5S4HR58##: Basic List Line Structure screen, assign 1 to the local field line (Net), a sequence number (such as 6), and select the TOTAL checkbox.

    8. Choose Save. Execute the query with the parameters and output forms specified for Company code1000 and calendar year 2024.

  2. Define another local field for the query called Limit, and set it up to output a green, yellow, or red traffic light depending on the amount in the Gross amount field. Include the field in the basic list.

    Execute the query (personnel area 1000 and 1300 and period calendar year 2024). Output the query once as an ABAP List, and once using the SAP List Viewer. Use the following attributes to create another local field:

    ConditionFornula
    NET<=2000ICON_GREEN_LIGHT
    NET>2000 AND NET<=3000ICON_YELLOW_LIGHT
    NET>3000ICON_RED_LIGHT

    1. Navigate to the Change Query Q5S4HR58##: Select Field screen. Choose Edit → Local Field → Create. Create another local field named Limit with the given attributes. In the Properties screen area, select the Icon radio button. Then choose Complex Calculation.

    2. Enter the data from the table, then choose Continue.

    3. On the Change Query Q5S4HR58##: Select Field screen, choose the Basic List pushbutton. On the Change Query Q5S4HR58##: Basic List Line Structure screen, assign 1 to the Limit local field in the Line field, and an appropriate sequence, such as 7.

    4. Go to the Change Query Q5S4HR58##: Field Output Options screen and change the length of the Limit field to 10.

    5. Choose Save and execute the query with the parameters and output forms specified for Company code1000 and 1300and Calendar year2024.

    6. Return to the Query from User Group S4HR58##: Initial screen.

Generate a Multi-line Basic List

ExerciseStart Exercise

Business Example

As part of your job, you need to generate reports on all employees within Personnel Area CABB and Personnel Subarea 0002 who were active last year, along with their current address, subtype 1, by creating queries within the SAP Query tool and using a multiline basic list.

  1. Define a new query Q6S4HR58## based on the InfoSet PA_## with a multiline basic list.

Line 1 must include the First Name (position 1, length = 10), and Last Name (position 2, length = 10) fields. Line 2 must include the Street and House Number (position 1, length = 30) field. Line 3 must include the City (position 1, length = 12), Region (State, Province, County) (position 2, length = 3), and Postal Code (position 3, length = 5) fields. The Address Record Type field should appear as a selection field on the report selection screen.

2. In transaction PA30, select personnel number 540991##, and copy the Address infotype subtype 1. Enter a start date prior to today, but in this year, and change the Street and House No. to reflect data of your choice.

Steps

  1. Define a new query Q6S4HR58## based on the InfoSet PA_## with a multiline basic list.

    1. On the Query from User Group S4HR58##: Initial Screen, enter Q6S4HR58## in the Query field.

    2. Choose Create. On the Restrict Value Range screen, select the InfoSet PA_## and choose Copy. Enter the title Q6S4HR58##, select ABAP List, and choose Next screen. Select the Personal Data and Addresses field groups, and choose Next screen. Select the fields in accordance with the requirements of this activity, and choose Next screen.

    3. On the Create Query Q6S4HR58##: Selections screen, select Address Record Type as a Selection Field. Press Enter. Select the Va Checkbox, so that multiple selection is allowed on the Selection Screen.

    4. Choose the Basic List pushbutton, and on the Create Query Q6S4HR58##: Basic List Line Structure screen, enter values for the line and field sequence as instructed. Choose Next screen.

    5. On the Create Query Q6S4HR58##: List Line Output Options screen, enter 1 in the Blank Line Aft field for Line 3. This will put a space after each employee’s address. Choose Next screen.

    6. On the Create Query Q6S4HR58##: Field Output Options screen, enter the appropriate values for the field length in the New column. Select Save.

  2. In transaction PA30, select personnel number 540991##, and copy the Address infotype subtype 1. Enter a start date prior to today, but in this year, and change the Street and House No. to reflect data of your choice.

    1. In another session, go to transaction PA30. Enter Personnel No. 540991##. Select Addresses. Select Copy. Subtype 1 is selected, so select Copy again.

    2. Change the Start date to a date this year prior to today. Change the Street and House No. field to reflect data of your choice. Select Save and acknowledge any system warnings.

  3. In transaction SQ01, execute your query Q6S4HR58## in accordance with the specifications. You should see the address you selected for your Winnie Chung 540996## in the list.

    1. Go back to your session in transaction SQ01. Select Query > Execute > Execute.

    2. Change the Reporting Period to Other Period. Select Show Person Selection Period. Enter today’s date in both of the Data Selection period fields. Enter the first of last year and the end of last year in the Person Selection period fields respectively.

    3. Enter Employment Status 3, Personnel area CABB, Personnel subarea 0002, and Address Record Type 1.

    4. Select Execute. Find your Winnie Chung and note that his address is the one that you created this year.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn