Skip to Content
avatar image
Former Member

Adhoc Query for HR/HCM Functional


Can any one help me to understand what is Adhoc -Query, as I am HR Functional Consultant, I need to understand how to write Adhoc query and the purpose of it.

What is logical Database PNPCE

I know Adhoc query provides 3 different kind of reports (Basic, Statistical and Rank)

Please explain me step by step process from functional point of view, and where and why technical consultant is required in it ?



Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Oct 15, 2015 at 08:45 AM


    Please check very detailed explaination Adhoc Query

    The maintain transaction for Ad Hoc query are SQ01, SQ02,SQ03. Using ad hoc query you can fetch data from various infotypes fields, that can be stand alone or combination of infotypes.

    You would require technical help in case you need to create selection screen for the ad hoc or you want to access ad hoc query using a Z transaction ( this is one of the scenario there may be other scenarios where  you need to abap developments)

    Ad Hoc uses/supports 3 logical databases PNP,PCH,PAP ( employee data, org data , payroll data) PNPCE is the latest version of PNP, PNPCE supports concurrent employment model.


    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 15, 2015 at 09:05 AM

    Below is information  Might be useful

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

    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


    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

    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

    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:


    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.


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

    Main screen

    main screen title


    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), theunion (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 Extrasmenu:

    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 GroupAQSDefault Functional Area
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      In Human Resources (HR), the following logical databases can be used as a data source for HR InfoSets:

      PNP (PNPCE)



      By selecting a logical database, you determine the HR data that can be reported on using an InfoSet.

      Please refer below thread