Creating Queries with SAP Query

Objective

After completing this lesson, you will be able to generate a report by using SAP Query

SAP Query Basics

If you use SAP Query to create queries, you can create one basic list and up to nine statistics or ranked lists for one query, which can be defined using selection and output fields.

When creating queries using HR logical databases, you can use the Line Groups function. This function enables you to group specific lines within multiline basic lists to form line groups.

If infotypes have more than one record, this function ensures that infotype fields that logically belong together are grouped in a logical manner, instead of being output one after the other.

The following table shows an example of different placement of fields from the infotypes with and without Line Groups:

Basic List with Line Groups	Basic List Without Line Groups
Martin Smith	Martin Smith
54333 Musterstadt	54333 Musterstadt
Any Street 4	12456 Anyplace
12456 Anyplace	Any Street 4
Example St. 4	Example Street 4

Field Selection

The system guides you through the following sequence of screens for field selection:

Title, Format:
This screen enables you to assign the title of the query. By entering format data, you can determine the page layout. By entering special attributes, you can determine further characteristics for the query such as change lock and output types).
Field Group Selection:
This screen enables you to select the required field groups. InfoSets are divided into field groups, which correspond to infotypes in HR.
Field Selection (Output Fields):
This screen enables you to select the data fields required for the field groups selected earlier. If you require local fields, you can define them on this screen.
Selection Fields:
This screen enables you to define selection fields with which you can enter further restrictions on the selection screen.

To Create SAP Queries

Steps

Define the name of the query.
Select an InfoSet.
Specify the query title, list format, and design the list.
Select the field groups and their fields.
Define the layout of the list.

Generate a Basic List Query

ExerciseStart Exercise

Business Example

You work in the HR department of your company and need to search the details of employees and display only certain fields.

Hint

Before performing the exercises, access the SAP Query initial screen and deactivate the Graphical Query Painter.
Unless otherwise specified, use the selection fields provided by thestandard selection screen of the report class. Work in the standard work area in your user groupS4HR58## with the Infoset PA_##.

With the InfoSet PA_##, define a query Q1S4HR58 with a basic list, which outputs the last name, first name, nationality, year of birth, and place of residence.

The formatting specifications in the model list are as follows:

Last name (length 10)

First name (length 10)

Nationality (length 2)

Year of birth (length 4)

City (length 15)

Steps

Define the query for personnel area CABB Hamburg by using the output form SAP List Viewer. Ensure that you are working with your user group S4HR58## and the Graphical Query Painter is deactivated.
1. Choose Human Resources → Information System → Reporting Tools → SAP Query.
2. To ensure you are working with your user group, choose Edit → Other user group and in the Query from User Group S4HR58## dialog box, chooseS4HR58## and then choose the Choose pushbutton.
3. To deactivate the Graphical Query Painter, choose Settings → Settings. In the Settings dialog box, deselect the Graphical Query Painter and choose Continue.
  In the Query field, enter Q1S4HR58## as the short name of your query and choose Create.
4. On the Restrict Value Range screen that appears, select InfoSet PA_## and the choose Copy.
5. Enter the title Q1S4HR58## in the Title field and choose Next Screen.
6. On the Create Query Q1S4HR58##: Select Field Groups screen, select the Personal data and Addresses field groups. Choose Next Screen.
7. On the Create Query Q1S4HR58##: Select Field screen, select the Last Name, First Name, Nationality, and Year of birth fields (from the field group 0002 Personal Data). Choose Next Page. Select the City field (from the field group 0006 Addresses). Choose Next Screen.
8. Choose Basic List to access the basic list definition. Do not select any fields as selection fields.
9. In the Line column, enter 01 for all fields (one-line basic list) and define the column sequence as specified in the table. Choose Next Screen twice.
10. Specify the length of the fields in the New column according to the table. Observe how the output preview changes in the lower part of the screen. Choose Save.
11. Choose Query → Execute → Execute, enter Personnel area CABB, and choose Execute.
Change the query so that the basic ABAP list is surrounded by a frame, and the individual columns are separated from one another by lines. After you have made these changes, execute the query once again.
1. Go back to the Create Query Q1S4HR58##: Basic List Line Structure screen and select the appropriate checkboxes. Choose Save.
2. Choose Query → Execute → Execute and enter Personnel area CABB. In the Output format section, choose the Expand pushbutton and select ABAP List. Choose Execute.
Once you have executed the query, display the SAP List Viewer. Set the SAP List Viewer to include only employees whose place of residence is Hamburg.
1. Choose the City column.
2. Choose Edit → Set filter and enter Hamburg as a filter value. Select Continue.
3. Return to the Query from User Group S4HR58##: Initial screen.

Next lesson