Creating Reports Using Ad Hoc Query

Objective

After completing this lesson, you will be able to create queries by using Ad Hoc Query

Ad Hoc Query Principles

The basic mode of operation of Ad Hoc Query consists of the following steps:

Define one or more selection criteria and then start the selection. The result of this selection is a set of objects, such as persons, applicants, business events, or positions.
Output the data as required for the selected objects.

Ad Hoc Query Screen

The Ad Hoc Query screen is divided into the following main areas:

When working with Ad Hoc Query, proceed as follows:

The field groups and fields of the current InfoSet are displayed on the left of the screen. This part of the screen is used to select the selection and output fields.
The right side of the screen enables you to determine selection values and execute your selection.
The lower part of the screen contains an output preview.

Select the selection and output fields in the overview tree.
Enter a value and choose a selection option, if necessary.
Execute the selection (the hit list is determined by selecting objects that match the selection criteria).
Format the output in the Output preview.
Access the output.

InfoSets for Ad Hoc Query

The InfoSets required for Ad Hoc Query are created and managed in SAP Query. When you create an InfoSet, you select the logical database on which it is based, and determine the infotypes that it includes. The infotypes are subsequently displayed in the InfoSet as field groups. Once you have selected your infotypes, you determine the fields of each infotype to be included in the field group.

The following scenarios illustrate how the InfoSet determines the objects that you can select with Ad Hoc Query:

InfoSet based on PNP or PNPCE
- The InfoSet based on the logical databases PNP or PNPCE enables you to use Ad Hoc Query to select employees and then output data on them.
- You can include Personnel Planning infotypes in these InfoSets. Consequently, you can use them to output person-related personnel planning data on the persons selected.
- You can also use these InfoSets to report on payroll results.
InfoSet based on PCH
The InfoSet based on the logical database PCH enables you to use Ad Hoc Query to select objects of one object type, such as business events, qualifications, or positions. When you create the InfoSet, you determine the object type.

Choose Selection and Output Fields

To access data in the system, Ad Hoc Query uses an InfoSet that provides a view of data in specific areas of HR. The view is structured by infotype. The InfoSet is displayed as an overview tree on the initial Ad Hoc Query screen.

You can select the selection and output fields by using drag and drop. To do so, select one or more fields and then drag them to the Selection or Output area.

If the selection and output fields have values and texts, you can use the text, the value, or both in the following ways.

You can choose the selection field in the Selection column of the overview tree. The field is then copied to the list of selection criteria on the right of the screen. If you use more than one selection criterion, they are linked by the AND logic. For example, by using the City and Age fields, you can select all persons who live in London and are 25 years old. If you select a field in the Selection column, you can use it as a selection field.

You also choose output fields in the overview tree. The fields are then copied to the output preview as columns. You can choose output fields either at the same time as you choose selection fields or afterwards. You can choose the output field from field groups as required.

You can output field contents as values (value output) and sometimes as text (text output). For example, you could specify the form of address key as value: 01, text: Mr; value: 02, text: Ms. If in doubt, specify both. The text often conveys more meaning. If you are a power user, you may require only the values.

The number of selection criteria and output fields that you have chosen is specified in the overview tree for each field group.

Treatment of Text Fields

If object selection has been switched off and values and texts exist for a field, you can select objects using values or texts. For example, you can select personnel area Frankfurt instead of personnel area 1000.

Note

The absence of the Hit List function and its output field indicates that object selection is switched off.

You can treat the text fields in the following ways:

Include user-specific settings for using text fields
Override settings for field selection

For the output, you can always use the value and the text, when available.

You can choose a selection or output field by using the following methods:

Select using Drag and Drop → User setting
Select using Checkboxes → User setting
Select using the context menu and choose between Value, Text or Value, and Text

Ad Hoc Query Selection Options

To restrict selection, you enter values and, if necessary, select options for the selection criteria. You select the required selection option from input help. You can enter values directly or determine them using standard input help. Input help enables you to select an input value or use multiple selections to enter any required number of single values or intervals.

The values that you can enter depend on the type of selection field, such as numeric, alphanumeric, and date. You can also use patterns as values. For example, if you want to find all of the employees whose names begin with S, enter S* as your value.

Editing of Selected Set of Objects

The result of each selection is a set of objects, such as persons or business events, for which you can output data.

You can also use the following editing options for each object set:

Output the selected objects in a list.
Sort the list.
Remove specific objects from the list, if you do not want to output data for those objects.

Use pushbuttons to branch directly to the following data:

HR master data for a set of persons.
Detail maintenance for other sets of objects.

Selection Using Organizational Structure

Ad Hoc Queries are often required to report on employee data from one or more organizational units. To do this, you can select persons using the organizational structure if the InfoSets are based on the logical database PNPCE.

To display the organizational structure, use the Reporting Set pushbutton. Choose Persons along organizational structure, then choose the filter icon. You can select the organizational units you require from the overview tree that appears.

When you confirm your selection, the persons who belong to the organizational units are selected immediately. These persons are written to Ad Hoc Query as a reporting set.

If you make your selection using the organizational structure, the system takes the specified reporting period into account and uses the current plan version.

Note

If you use the organizational structure to select a higher-level organizational unit, the selection includes all of the persons who belong to the selected organizational unit or one of the lower-level organizational units. To achieve the same result, you must use the Organizational Unit Selection field and specify all the organizational units explicitly using multiple selections.

You can switch off object selection and work in Basic Mode by choosing Extras → Switch Off Object Selection. This gives you the advantage of being able to use all basic mode functions, such as selections using texts.

The absence of the Hit List function and its output field indicates that object selection is switched off.

Note

The disadvantage of switching off object selection is that you no longer benefit from improved performance (fast selection routine for persons and personnel planning objects). Furthermore, you cannot restrict the reporting set when object selection is switched off.

Create a Simple Query

ExerciseStart Exercise

Business Example

You need to use Ad Hoc Query to define your own reports and make them available to other colleagues.

You require a query that allows you to report on all employees according to the year in which they were born. The query should display the following data for employees in the following specified order:

Personnel number
Last name
First name
Personnel area (as value and text)

Steps

Report on how many employees were born in 2002 by selecting the relevant employees. Save your query with the name GR##_AHQ_1
1. On the SAP Easy Access screen, choose Human Resources → Information System → Reporting Tools → Ad Hoc Query.
2. Choose Query → New.
3. On the Environment section of theCreate New Query – InfoSet Selection screen, choose Standard area (client-specific) in the Work area field. In the User Group field, choose S4HR58##. In the InfoSet section of the screen, choose PA_## and choose Continue.
4. Select Reporting Period: Today.
5. In the Field group/fields section of the screen, open the Actions folder. Select the Personnel Number field as an output field. Right mouse click choose Output → Only Value.
6. In the Field group/fields section of the screen, open the Personal Data folder. Select the Last Name and First Name fields as output fields. Select the Year of Birth field as a selection field.
7. In the Field group/fields section of the screen, open the Organizational Assignment folder. Select the Personnel Area field as an output field. In the output field for Personnel Area, right-click to choose Output → Value and text.
  If required, to change the sequence of the columns in the output preview, select one column at a time and use drag and drop to drag the column to the desired position.
8. Enter 2002 in the Value column of the Year of Birth field name. Start the selection by choosing the Hit List pushbutton.
  In the neighboring field, the number of selected persons is displayed.
Display the list of employees. Select any two employees and branch to master data.
1. Display the list of selected employees using the Display hit list (pushbutton next to the hit list). To branch to HR master data, select the required person and choose the HR master data pushbutton.
Delete these two employees from the list of selected employees.
Start the output.
1. To start the output, choose the Start Output pushbutton.
2. Return to the InfoSet Query (Query Group S4HR58##) screen and choose Query → Save As. On the Save Query screen, enter GR##AHQ1 in the name and title fields and choose Continue.

Create a Query with Multiple Selections

ExerciseStart Exercise

Business Example

You need to use Ad Hoc Query to define your own reports with multiple selections.

Enhance the query you created in the Create a Simple Query exercise so that it determines which employees were born between 1970 and 1999 and have two or more children. Display the exact number of children they have.

Steps

Report on how many employees fulfill these criteria by selecting the employees.
Hint
Extend the query you created in the Create a Simple Query exercise. Save your query with the name GR##_AHQ_2.
1. On the InfoSet Query (Query Group S4HR58##) screen, in the Personal Data field group, choose Number of Children as a selection field and an output field.
2. For the Year of Birth field name, choose the Further Values pushbutton. On the Select Ranges tab page of the Multiple Selection screen, enter a Lower limit of 1970 and an Upper limit of 1990. Choose Copy.
3. For the Number of Children field name, choose the Option pushbutton. In the Maintain Selection Options screen, choose Greater thanor Equal to. Choose Continue.
4. In the neighboring Value field, enter 2.
5. Start the selection by choosing the Hit list pushbutton. In the neighboring field, the number of selected persons is displayed.
Start output and save this query under the name GR##AHQ2.
1. To start the output, choose the Output pushbutton.
2. Return to the InfoSet Query (Query Group S4HR58##) screen and choose Query → Save As. On the Save Query screen, enter GR##AHQ2 in the Name and Title fields and choose Continue.

Create a Query Using the Organizational Structure for Selection

ExerciseStart Exercise

Business Example

You need to use Ad Hoc Query to define your own reports using the organizational structure for selection.

Define a new query that allows you to report on all employees who belong to the Controlling(in Org.Units Training Group, this is in the area Training International) organizational unit. Select using the Organizational Structure. Display the following data for the selected persons:

Last name
First name
Organizational unit

Steps

Start output. Save this query with the name GR##AHQ3.
1. To create a new query, choose Query → New.
2. On the Create New Query – InfoSet Selection screen, Choose the InfoSet PA_## and choose Continue (The User Group and Work area are entered by default).
3. To make a selection using the organizational structure, choose the Reporting Set: Unrestricted pushbutton and then choose the No. of Hits pushbutton. From the values displayed, choose Persons along organizational structure.
4. To display the organizational structure, choosePersons along organizational structure. Chose the Filter icon. In the Choose Organizational Unit screen, navigate by choosing Org.Units Training Group → Training International and choose the checkbox Executive Board and choose Continue .
  Note
  All employees who belong to this organizational unit, including those assigned to the subordinate organizational units, are selected. They are available as a reporting set for additional reports or for display.
5. In the Field group/fields section, open the Personal data field group and select Last Name and First Name as output fields.
6. In the Field group/fields section, open the Organizational Assignment field group and select Organizational Unit as an output field.
7. Choose the Hit list pushbutton and then choose the Output pushbutton.
8. Return to the InfoSet Query (Query Group S4HR58##) screen, and choose Query → Save As.Enter the name GR##AHQ3 in the Name and Title fields and choose Continue.
9. Return to the SAP Easy Accessscreen.

Next lesson