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.

SAP Ad Hoc Query: A User’s Guide

 

 

Ad Hoc Query, transaction code S_PH0_48000510, is very useful tool when producing reports from Personnel Administration and Organisational Management.

Queries (or reports) in SAP are split into Query Areas, User Groups, Infosets, and Queries.

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.

User Groups define which groups of end users can see different infosets.

Infosets define which infotypes and which fields within those infotypes you can report on. Infosets are generated via SAP Query, transaction code SQ01.

Queries are the actual reports that an end-user can run within SAP, these can be set up in SAP Query or Ad Hoc Query.

When you first enter Ad Hoc Query you need to select which Area, User Group and Infoset you want to use to run your report;

SAP HR Create Adhoc Query

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.

SAP HR infoset selection fields

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 report, the gender key needs to be 2 – female and the date of birth must be less than the exact date 65 years ago.

When a selection is made on the left of the screen the relevant fields will appear on the right of the screen, this is where the values are input.

SAP HR Adhoc Query Set Values

Once you have selected your input values, you must define the time period that you would like to report on. SAP defaults to today’s date, but it is possible to define any period of time. This can be useful when you would like to report on who had an absence in April 2007, for example.

To select the date parameters select the ‘Reporting period’ button at the top right of the screen. By clicking on the drop down box you can select the option relevant to your report. To select a certain period (April 2007 for example, select other period and input a start and end date.

SAP HR Adhoc Query Set Date

When you have defined all the data and the date parameters required for your report, you need to select your output fields. For example, you might need to know the name, employee number and work place of every lady over the age of 65; the outputs to your report would be the fields with this information in them.

SAP HR Adhoc Query Set Output

Before you output your report it is good practice to check how many people have been selected by your selection fields, this normally gives a good indication as to whether your selections are correct – if you expect to have 10 people in your report and you have 150 then it is likely at least one of the selection fields is incorrect.

To check how many people have been selected by your report, select the ‘Hit List’ button.

SAP HR Adhoc Query Hitlist

Here the report has selected one person, i.e. there is one female above the age of 65 in SAP. To see the employee’s name and number, select the glasses, to reset the hit list select the bin.

When you are happy with the report you have built and want to see the output, select the output button at the top of the screen:

SAP HR Adhoc Query Output Button

You will then see the results of your report.

SAP HR Adhoc Query Results

You can now save this report to your PC by selecting the ‘Save to Local File button;

SAP HR Export Button

If the report you are building is likely to be run on a regular basis, for a headcount, then you should save it as a query, simply by selecting the save button at the top of the selection/output screen. You can then open the report and re-run it without needing to make all your selections again.

If you need to run 2 separate reports and compare them to find out the intersection (employees in both reports), the union set (employees in either of the reports) or any employee who is one report and not the other then this can be done through SAP Query.

For example you might need to know anyone who was sick in April 2007 and who isn’t a female over the age of 65, this could not be done simply by running one report.

To compare 2 reports (report A and report B), you first must build report A and store the results and the build report B and store the results.

To do this select the ‘Extras’ menu from the top of the screen and choose ‘Show set operations’. You will notice tabs appear in the right of the screen, and two extra buttons appear next to the ‘Hit list’ button.

SAP HR Adhoc Query Compare Results

These two buttons perform the same function as the ‘Hit list’ button, i.e. they work out which employees are selected by a report; but they also store the results of the hit list, meaning you can have a hit list for report A and a hit list for report B and compare them. The blue button stores employees into Set A and the yellow button stores employees into Set B.

To store the 2 hit lists simply build report A and select the blue button, then build report B and select the yellow button.

Now choose ‘Set operations’

SAP HR Adhoc Query Set Operations

As you can see, report A (females over 65) has 1 person in it, and report B (anyone absent in April 2007) has 3 people in it. You can now choose one of the operations and select the Carry out operation button. The result of the operation will be stored in the ‘Resulting set’ - from here you can see that only 2 people were absent in April 2007 not including females over the age of 65.

You can save the results to set A / set B again and do another iteration if required by selecting the ‘Copy resulting set’ button.

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

You can change the column order of the output by using the up and down keys of the Output tab. Select the relevant field and push it up or down.

The old transaction code for Ad Hoc Query is PQAH. Don't use in on versions 4.6b and above. Rather use the new transaction codes S_PH0_48000513. The new transaction code gives you an option to choose your user group and infoset and furthermore allows you to open and save different queries.

In version 4.7 there is another functional area for PA. It is called PNPCE - you will recognise it. If you try and use the PNP InfoSet, the system prompts you to use the new one instead.

You can use the Complex Operations which allow you to choose 2 different data selections and report on the differences/union etc. Complex Operations can be chosen by using the menu path View > Expert Mode within Ad Hoc Query.

If you are using 4.5 you can create the "explorer" look and feel of 4.6 by changing the settings in menu path Help > Settings. Change the radio button from System Defaults to Active X. The changes take place from the next transaction call.

If you are using version 4.6 and would prefer to use the old view - you can change the view format - under settings on the menu path.

You will also notice, that any query set up in SAP Query, can be run from within Ad Hoc query.

SAP Ad Hoc Query: Default Parameters

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

AQB Default User Group
AQS Default Functional Area

 

 
 
Search Query


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.
 
Bookmark and Share
Copyright © 2009 Strategic Data Solutions Ltd. All rights reserved   W3C valid xhtml