Creating Complex Queries with Ad Hoc Query

Objective

After completing this lesson, you will be able to create complex queries with additional options in Ad Hoc Query

Complex Queries

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 specified.

The reporting period also affects data output (persons and data selection period). You can separate these time periods on the selection screen, if required. You can create complex queries by adding additional reporting criteria. You can also run statistical reports, for example, the number of people who live in a city.​

The following examples show how to determine the reporting period:

Hint

The system always selects all objects for which a valid record exists at any point within the reporting period. For example, if you report on an entire year, the system selects employees who belonged to organizational unit 1000 for at least one day during that year.

Time Constraints

The time constraint of an infotype affects the contents of the output list. When you make a selection, the system determines whether there are any objects that meet the selection criterion during the reporting period.

When you execute output, the system outputs all the valid records that exist for the selected objects. If you report on data for a key date, different numbers of records are output depending on the time constraint specified for the infotype and possibly its subtypes.

Infotype time constraints include the following reporting results:

Time constraint 1:
One row is output per object because the system always contains just one valid infotype record (for example, infotype 0002 Personal Data).
Time constraint 2:
One row is output per object because the system can only contain one valid infotype record (for example, infotype 0050 Time Recording Info).
Time constraint 3:
The system can contain several valid infotype records at the same time. For this reason, the number of valid records determines the number of Rows that are output for each object (for example, infotype 0023Other/Previous Employers).
Subtypes with different time constraints:
If an infotype has subtypes, the time constraints for the subtypes can  determine that different number of records exist for each subtype. For this reason, the number of records that exist determines the number of rows that are output for each object.
For example, you select all the employees who live in New York. Your selection lists all of the persons who reside in New York. If you then include the temporary residence for employees, a row is output for each valid temporary residence; for example, the permanent residence of New York (subtype with time constraint 1) and the temporary residences of London, Berlin, and Walldorf (subtype with time constraint 3).

Reuse of Queries

When you create queries, you can switch between query areas, user groups, and InfoSets. If you are assigned to a user group, you can save queries. The query is automatically available to all users who belong to the same user group.

If you often perform reporting using the same selection criteria but different values, you are advised to save queries without specifying values. Do not enter data in the value and option fields before saving.

If you often perform reporting using the same selection criteria and the same values, you are advised to save queries with values.

You can access saved queries from the following sources:

Ad Hoc Query:
If you access saved queries from Ad Hoc Query, you can execute, change, or save them in their changed form, or with a new name.
SAP Query:
SAP Query enables you to continue editing saved queries. You are advised to continue editing in SAP Query if you want to use particular output functions in SAP Query, such as summation levels or colors in the output list.
Menus:
You can access saved queries from role menus assigned to users.

Caution

The technical name of a query must not exceed 14 characters, and must not contain any special characters with the exception of underscores and hyphens. For example, queries that contain a period can be saved but cannot be accessed again.

Create a Report with a Specific Reporting Period

ExerciseStart Exercise

Business Example

You need reports for HR master with a specific reporting period because your requirements are not met by any of the standard reports.

Create a standard employee list report by using the Ad Hoc Query.

Steps

  1. Define a new query using the InfoSet PA_##, the Standard area (client-specific) work area, and the S4HR58## user group. The query must enable you to determine which employees in personnel area CABB were hired in 2024 (Action type Z0, Reason for action Z0). The list must include the personnel number, first name, last name, and entry date. Use Other period as the reporting period (01 January 2024 to 31 December 2024). Save your query with the name GR##_AHQ_4.

    1. On the SAP Easy Access screen, choose Human Resources → Information System → Reporting Tools →  Ad Hoc Query.

    2. On the InfoSet Query screen, choose Query → New. On the Create New Query – InfoSet Selection screen, choose Standard area(client-specific) in the Work Area field and choose S4HR58## in the User Group field. Choose the InfoSet PA_## and choose Continue.

    3. In the Field group/fields section, open the Personal data field group, and select Last Name and First Name as output fields.

    4. Open the Actions field group and select Entry Date as a selection field and output field and the Action Type and Reason for Action as selection fields.

    5. Open the Organizational Assignment field group and select Personnel Area as a selection field.

    6. Enter the following values:   

      Field NameValue
      Personnel AreaCABB
      Action typeZ0 Hiring Basics PA
      Entry date01.01.2024 to 12.31.2024 
      Reason for actionZ0 (New Hiring)

    7. Select the Reporting Period pushbutton and choose Other Period. Enter 01.01.2024 and 12.31.2024. To select the appropriate persons, choose the Hit list pushbutton.

    8. Choose Output.

      Return to the InfoSet Query (Query Group S4HR58##) screen and choose Query → Save As. On the Save Query screen, enter GR##AHQ4 in the Name and Title fields and choose Continue.

    9. Return to the SAP Easy Access screen.

Create a Statistical Report

ExerciseStart Exercise

Business Example

You need special statistical reports for HR master data because your requirements are not met by any of the standard reports.

Define a new query that allows you to report on all employees who belong to personnel area CABB. Use the personnel area to select objects in this exercise; do not select objects through the organizational structure. Save your query with the name GR##_AHQ_5.

Hint

  • Work in the Standard area (client-specific) in your user group S4HR58## for these exercises.
  • Use InfoSet PA_## unless instructed otherwise. Use Today as the reporting period unless instructed otherwise.

Steps

  1. Display statistics that show how many employees belong to Personnel Area CABB.

    1. On the SAP Easy Access screen, choose Human Resources → Information System → Reporting Tools → Ad Hoc Query. ​

    2. To create a new query, choose Query → New.

    3. On the Create New Query: InfoSet Selection screen, the work area and user group are entered by default. Choose the InfoSet PA_##. Choose Continue.

    4. In the Field group/fields section, open the Organizational Assignment field group. Select Personnel Area as selection field and output field. Enter CABB in the Value field of the Personnel Area field name. Start the selection by choosing the Hit list pushbutton.

    5. In the Organizational Assignment field group, select Personnel Subarea as an output field.

    6. Choose Edit → Settings. In the Setting dialog box, on the Output tab page, choose Statistics as the Type of output list.

    7. Choose Continue.

    8. Choose the Output pushbutton.

    9. Return to the InfoSet Query (Query Group S4HR58##) screen and choose Query → Save As. On the Save Query screen, enter GR##AHQ5 in the Name and Title fields, and choose Continue. ​

Generate a Report with a Ranked List

ExerciseStart Exercise

Business Example

You need special reports with a ranked list of HR master data by using Ad Hoc Query.

Define a new query that allows you to report on all employees who belong to the personnel area CABB. Use the personnel area to select objects in this exercise; do not select objects through the organizational structure. Save your query with the name GR##_AHQ_6.

Steps

  1. Display a ranked list that specifies the five most common cities of residence for employees who belong to personnel area CABB. Save this query under the name GR##_AHQ_6. Display the Ranked List option. Which is the most popular city and which is the second most popular city in the ranked list?

    1. Go to the initial screen of the Ad Hoc Query for your last query. Delete the Personnel Subarea output field.

    2. In the Field group/fields section, open the Addresses field group and select City as an output field.

    3. Choose Edit → Settings.

    4. In the Settings dialog box, choose the Output tab page. Choose Ranked List as the Type of output list.

    5. On the Stats/ranked List tab page, enter the specified number of rankings as 5.

    6. Choose Continue.

    7. Choose Output.

    8. Return to the InfoSet Query screen, choose Query → Save As and enter the name GR##AHQ6 to save the query.

    9. Remain on this screen.​

Create a Query with an InfoSet from Logical Databases PNPCE and PCH

ExerciseStart Exercise

Business Example

You need to prepare reports for HR Master data based on InfoSets from the Logical Databases PNPCE and PCH  by using Ad Hoc Query.

Steps

  1. Change to InfoSet PAQ_## for this exercise. Output a list of employees who belong to personnel area CABB. The list should contain the following information:

    • Personnel number
    • Last name
    • First name
    • Qualification (Name) 

    Hint

    Sort the list according to last name or qualification. Save the query as GR##AHQ7.

    1. To switch InfoSets, choose Query → New. The Create New Query – InfoSet Selection dialog box appears.

    2. Select InfoSet PAQ_##.

    3. Choose Continue. The selected InfoSet is displayed in the selection tree and you can use it to define queries.

    4. In the Field group/fields section, open the Organizational Assignment field group and select Personnel Area as a selection field. In the Value field for the Personnel Area field name, enter CABB.

    5. Open the Personal Data field group and select Last Name and First Name as output fields.

    6. Open the Qualification field group and select the Qualification Name (Name) as an output field. Check the sequence of output fields in the output preview. If necessary, add the Personnel Number (Only Value) as the first column.

    7. Choose Output. Return to the PAQ_## screen.

    8. Save this query by choosing Query → Save as and in the Save Query dialog box, enter the name GR##AHQ7 and choose Continue.

    9. Return to the SAP Easy Access screen.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn