cancel
Showing results for 
Search instead for 
Did you mean: 

Adhoc Query for HR/HCM Functional

Former Member
0 Kudos

Hi,

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 ?

Regards

Janes

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member182306
Active Contributor
0 Kudos

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


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), 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 Group
AQSDefault Functional Area
former_member182306
Active Contributor
0 Kudos

Overview
Once a query has been created and saved, either through Adhoc QUERY, it may be necessary to run it on more than one occasion. Monthly or weekly verification reporting on employee communication data, quarterly reporting on organizational assignments, for example. Normally a user will go through the lengthy process of calling up either SAP Query or Ad Hoc Query, confirming the user group and infoset, then searching for the query to finally be able to re-run it.

It is possible, however, (and very simple) to assign a specific transaction code to such a query. This transaction code can then be assigned to a user's favourites in the Easy Access menu as with any other transaction. When the transaction is run, the user is presented the selection screen for that report so that it can be run.

Create Transaction
Assuming that the query has been created and saved, the first step to linking it to an transaction is to create the transaction code. This is done via the SAP-standard transaction SE93 ‘Maintain Transaction’.

create transaction

Enter a suitable transaction code that will be used to run the query. Some things to keep in mind when deciding on a suitable option are:

  • Make it memorable - if the user is not going to add it to their favourites, then they are going to need to enter the transaction code manually. If it is not a very frequently run report, they may have difficulty remembering transaction codes that don't seem to make sense (e.g. S_PH0_48000513). It is a good idea to include some reference to the query in the transaction. For example. If the query is looking at employee communication data, the transaction could include terms such as ‘EE’ for employee ‘COMM’ for communication or ‘COMMDATA’.
  • Keep it short - similar to the above point, but more geared towards trying to reduce he frustration from mistyping, if the transaction code is kept short (ideally avoiding scrolling in the transaction box) this make it easier for the user to enter. For example EMPLOYEE_COMMUNICATION_DATA_REPORT (apart from being too long for the max allowable 20 characters), whilst describing the report, is too cumbersome for users to enter. It might be better to restrict it to EE_COMM_DATA. This uses recognisable abbreviations and is much quicker to enter. It does not need to reflect the report contents to be short and memorable, PA20, PA30, PA40 are all good examples of short transactions which give no indication of what they are for.
  • Naming conventions - whilst it is possible to have almost any code for the transaction within the constraints of character length (max 20), some possible considerations in terms of the naming conventions used are:
    • Start with ‘Z’ - since this is a custom transaction code, SAP normally recommends starting the naming of custom objects with 'Z' to easily differentiate them from SAP-standard objects.
    • Alphanumeric - as users normally think in terms of words and not numbers, limiting the use of numbers in the transaction code is likely to make it easier to remember. That said, some numbers may be useful identifying the reason for the report, such as cost centres (for cost reporting), Year (for period reports within the same year). Bear in mind also, that SAP will default the transaction code to BLOCK CAPITALS, so any attempts to enter terms such as CoCode, will be changed to COCODE automatically
    • Readability - if multiple terms are to be used in the transaction code, it is a good idea to separate these to aid typing and understanding. For example PC00_M08_RTI_FPS is more easily understandable that it is for the UK (M08) related to RTI and runs the FPS report, than if it had been created as PC00M08RTIFPS

Once a suitable name has been chosen, clicking [CREATE] shows the following dialog:

transaction attributes

Enter a short text description for the transaction and choose Transaction with parameters (parameter transaction). Continuing will then allow you to finally link your report to this transaction.

Linking the Report
The next screen is split into 2 sections

Default values (top and bottom of screen)
Classification

Default Values

default values top

In the Transaction field, enter: START_REPORT (for more details on this transaction see below). Since values for the parameters needed for Start Report to run the correct program are going to be entered, tick the box next to Skip initial screen. This ensures that the initial screen of Start Report (shown below) showing the parameter fields, is not shown the transaction leads the user straight to the Query.

The other Default values section is found at the bottom of the screen:

default values bottom

This permits the definition of the values for the parameters of Start Report. It is not mandatory to enter these, but doing so makes it easier for the user. Enter the following:

Name of screen fieldValue
D_SREPOVARI-REPORTTYPEAQ
D_SREPOVARI-REPORT[enter the User Group to which the infoset/query is assigned]
D_SREPOVARI-EXTDREPORT[enter the name of the report to be run]

Classification
Select Inherit GUI attributes.

select gui attributes

Save the changes, provide appropriate 'package' and Workbench transport details, and the link is complete. The transaction can be transported to other systems for use provided that the appropriate user group and queries are present in the target system.

Entering the transaction (in this case Z_COMPANY_CODES) in the standard transaction box, will lead the user to the associated report -either to the selection screen, or straight to the output as defined in the query. Alternatively the report will run in the background as determined by the selections made above.


Start Report
The SAP-standard program SAPLSRT2 'Start Report' (which is accessed via transaction START_REPORT) can be used to enter details for running a report created using SAP Query or Ad Hoc Query.

The parameters on the initial screen are used to enter values to determine the report to be run.

start report

In the example being worked above,

Report Typ= AQ
Report= HR_REPORTING (this is actually the User Group to which the report has been assigned)
Ext. Name= Z_PNPCE_QUERY (the name of the report)

A variant could also be entered and options chosen to go straight to the output (omitting the selection screen), or even run the report in the background.

Former Member
0 Kudos

Thanks a lot for the document,

I have a doubt why we use, logical database, PNP and PNPCE

Regards

Janes

ChrisSolomon
Active Contributor
0 Kudos

Care to credit the source you just copy/pasted this from?

Matt_Fraser
Active Contributor
0 Kudos

"Alert Moderator" time?

former_member182306
Active Contributor
0 Kudos

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

PNP (PNPCE)

PAP

PCH

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

Please refer below thread

0 Kudos

Hello,

Would you know how to show the infotype numbers in the adhoc query transaction? I used to see them and I know there is a specific setting I need to change but I cannot find it now.

Thanks,

anne-petteroe
Community Manager
Community Manager
0 Kudos
Hello, While we're happy that you've come to SAP Community to get an answer to your question, you posted your question as an answer in an old thread.Posting in older threads is not the best way to get guidance.If you're looking for help, you should ask a new question: https://answers.sap.com/questions/ask.html.Kind regards,
Anne
former_member47879
Active Contributor
0 Kudos

Hi,

Please check very detailed explaination

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.

Thanks