SDS homepage follow SDS link to SDS SDS on facebook
 Home arrow Knowledgebase arrow Query Tools arrow Ad Hoc Query  
  If your browser does not support the SDS Knowledge Base menu, click here for an alternative.

Ad Hoc Query: A User’s Guide

 
last updated: 19.March.2015

 

One of the easiest ways for a user to create a report on PA and/or OM data is to use the SAP-standard reporting tool Ad Hoc Query

Transaction: S_PH0_48000510
SAP Menu: Human Resources Information System Reporting Tools Ad Hoc Query

The front-end capabilities of Ad Hoc Query are, generally, quite basic. The user selects the fields from PA and/or OM infotypes that are to be output and makes a suitable selection (from the SAP-standard selection options) to cover the employees to be analysed. The Output is then displayed across the page for each employee. More complex reporting can also be performed by comparing the output of two separate queries - this is covered below .

In addition, it is possible to have custom fields as part of the standard field selections, which can have complex ABAP code behind them. This configuration is carried out in the background during the preliminary stages of setting up the Infosets.

Query Areas
Query Areas define whether the report is available cross client (Global Area) or only in the client that it is built (Standard Area). If SAP is set to Global Area, then it is not possible to see any reports built in the Standard Area and vice versa.

Once the query area has been decided upon, User Groups and Infosets are set up. The Infosets define which infotypes and which fields within those infotypes can be included in an Ad Hoc query. User Groups define which groups of end users can see different infosets.

The first step when accessing the Ad Hoc Query transaction is to select which Query Area (called ‘work area’ at this stage), User Group and Infoset are to be used to create the query:

initial selections

The main Ad Hoc query screen is then displayed.

main screen

Selections
When running a report in Ad Hoc Query, you must specify which fields you would like to use as a selection and which fields you would like to output.

The selection fields define which employees are going to appear in your report. For example, if you wanted to find a list of females above the age of 65 within a company you could use gender and date of birth as selection fields.

selection fields

Here, both fields that need to be selected are held in IT0002, Personal Data. By opening up the folder named‘Personal Data’, the required fields within that infotype can be chosen. To use a field for selection, tick the check box next to field in the ‘selection’ column.

The next step is to define what values of the gender field and date of birth are valid for the report.

In the case of the above example report, the value for ‘Gender key’ needs to be 2 (female). If the SAP key for gender (or other fields) is not known, place the cursor in the field and either press f4 or click on the small drop down at the right side of the field (circled below).
selection values

This then shows the available options that have been configured for that field:

selection values list

Option
Once an entry is made in this field, the icon in the '‘Option’ column will default (initially) to ‘equals’

option default

Confirming that only employees with a Gender Key equal to ‘2’ will be selected. The option for any field can be changed by right clicking on the option icon and choosing one of the many other selection options available:

option choices

The buttons [Select] and [Exclude from selection], can be used to switch between the options for including or excluding the chosen field values from the selection. The [Delete Row] button removes any Options and clears the selection field of values entered.

In order to correctly output all female employees aged 65 or over, the value in the 'Date of Birth' field must be set to a date 65 years form the day the report is being run, and the selection Option set at less than or equal to or less than depending on the exact date entered.

option other

Reporting Period
Once the selection parameters have been set, it is necessary to confirm the time period relevant for these selections. On first opening Ad Hoc Query, SAP sets the reporting period to Today by default.

reporting period

This means, in the example report being worked here, that the report will select all female employees who, as at Today (the day the report is being run) have the Gender Key set at ‘2’ and have a date in the Date of Birth field such that their age is calculated as at least 65. This period refers to the Start Date and End Date of the master data records being analysed. Any records falling outside the determined ‘Reporting Period’ are not selected.

It is possible to define any other period of time or key date for reference. This can be useful if there is a requirement, for example, to report on absences in April 2015, for example.

Clicking on the [Reporting Period] button, and then on the drop down arrow, displays the available options

reporting period choices

Output
Once all selection fields have been chosen and values set, and the reporting period confirmed, the output fields need to be identified. This is done in the same way that fields were chosen for selection, except that the tick needs to be placed in the box under the Output column.

In the example report (all females over 65) the requirement is to show Personnel Number, First Name, Last Name and Personnel Subarea. These fields, therefore, need to be marked for ‘output’. As before choosing field for selection, open the correct infotype folder to find the required fields.

output fields

All fields chosen for selection are displayed in the bottom section of the screen in the order (Ieft to right) that they are selected). This shows how the date will look once the query is run. The data shown initially is dummy data. This order can be changed by clicking on a column header and dragging it to the desired new place.

The screen shot above shows that ‘Last Name’ and ‘First Name’ have been selected for output from ‘Personal Data’ (IT0002) and that there is also 1 field in each of ‘Actions’ (IT0000) and ‘Organizational Assignment’ (IT0001) selected for output. In this case these are ‘Personnel Number’ and ‘Personnel Subarea’ respectively. Once the fields were selected for output, the folder was re-collapsed to tidy up the screen a bit!

N.B. when selecting fields for output, it is useful to know what information is available for display.

All fields marked as below:

value and text icon

Are able to show either Value or Text, or both. Right clicking on the field name and choosing output will show the options:

value and text

Selecting one of these will change the display information:

Only textOnly valueValue and text (automatically creates 2 output columns)
text onlyvalue onlyvalues and text

The text output for Personnel Number is the employee’s name. This could, therefore, be used instead of choosing separate ‘Last Name’ and ‘First Name’

All fields marked as below:

value only icon

Are able to show only Value. Right clicking on the field name and choosing output confirms this:

value only menu

 

Before running the query, it is good practice to check how many people have been selected by chosen selection fields. This normally gives a good indication as to whether the selections are correct - if the expectation is that 10 records are shown in the output, and the system identifies 150+, then at least one of the selection fields is probably incorrect (provided the master data behind the query is correct!)

To check how many people have been selected, click on the [Hit List] button under the selection fields.

hit list button

Here the report has selected ‘22 Persons’ person, i.e. there are 22 females over 65 on the day the query is run. To see the employees' names and personnel numbers, click on the display icon (the glasses). If this number (or the selected employees) is not correct, make changes to the selections and click [Hit list] again. Repeat until the selections looks right.

To run the query, click on the [Output] button at the top of the screen, or click Query Output from the menu.

output button

If the option to ‘Start via selection screen’ has not been selected, the results of the query will then be displayed in the format chosen:

output

The title and name of the report will default to the date and time the output was produced as well as showing the user that ran the report. This is changed when saving the query (see below)

If the query is likely to be run on a regular basis, it is a good idea to save it. Back on the main screen, click on the [save] icon and enter a suitable ID and name.

save

Once this is confirmed, the descriptions displayed for the query change from the SAP default:

Main screen

main screen title

Output

output title

Set Operations (Combining Queries)
It is also possible to run 2 separate reports and compare them to find out the intersection (records in both reports), the union (records in either of the reports), or the complement set (records in one report but not the other).

Building on the above example, the requirement now needs to show all employees who were absent on Sickness in January 2015, but who are not females over the age of 65. This could not be done simply by running one report without custom ABAP enhancement.

To compare 2 queries (report A and report B), it is necessary to first build report A and store the results, then build report B and store these results. This requires the additional functionality of Set Operations, which is activated from the Extras menu:

show set operations

Selecting this option, displays an additional page tab, and 2 extra buttons next to the [Hit list] button:

set operations

These two buttons are used to store the results of the hit list - the principle being that two separate hit lists from two separate queries can then be compared and the comparison results displayed. After clicking [Hit list] button, clicking the blue button on the left stores results into Set A,

set A

and (after modifying the selections to search for absences in January 2015) and re-clicking the [Hit list] button, clicking the yellow button stores results into Set B.

set B

Selecting the ‘Set operations’ page tab displays the following fields (it may be necessary to either scroll down or reduce the height of the sample output display pane at the bottom of the screen to see all the fields shown below):

set operation options

The results show that report A (females over 65) contains 22 persons, and report B (absences in January 2015) contains 2 persons. Selecting the various ‘Operations’ and clicking the [Carry out operation] button will show the results. The new requirement was to show Jan 2015 sickness that wasn’t female over 65, so ‘Set B minus Set A’:

set operation result

The results show that one of the employees on Sickness in January 2015 was a female over 65 and has been filter out to leave only 1 employee on Sickness in January 2015 who is not a female over 65.

The options at the bottom of the screen enable this result to be saved to Set A or Set B and this then used in combination with another query.

Alternatively, to run the report and see who the one person is, select in Hit list then click on [Copy resulting set]:

copy resulting set

Run the query to see the results:

resulting output



Ad Hoc Query: Additional Tips and Tricks

  •  You need to create User Groups and Infosets first (before you create any queries) - to which you will have to assign the relevant users.
  •  You can save an Ad Hoc Query and run it from SAP Query (transaction code SQ01) where you have SAP standard selection options.
  •  You can use the Ad Hoc Query to provide selected data for another specific report in SAP. Specify the report using the menu path Goto Start Report within Ad Hoc query, once the Hit list contains at least 1 person.
  •  The old transaction code for Ad Hoc Query (PQAH) shouldn't be used in versions 4.6b and above. The new transaction code (S_PH0_48000513) gives better functionality.
  •  From 4.7 there is an additional logical database for Personnel Admin data. It is called PNPCE and enables reporting on both PA and OM data in the same query.
  •  Any query set up in SAP Query, can be run from within Ad Hoc query.

Default User Parameters
The following items can be assigned to the User Parameters for Ad Hoc Query users to default certain parameters:

AQBDefault User Group
AQSDefault Functional Area

 

 
 
Search


Your Input

We are always updating our articles and adding new ones to the list. Please let us know if there is a particular article you would like to see.
 
Share
Copyright © 2009 Strategic Data Solutions Ltd. All rights reserved   W3C valid xhtml