Welcome to the tutorial about SAP Ad Hoc Query and creating Ad Hoc reports using this useful tool provided by SAP ERP system. After completing this tutorial you will learn how to create Ad Hoc reports in SAP using the query tool. This tutorial is written around an example from SAP HCM module.
SAP Ad Hoc Query is an operational reporting tool which is widely used in the SAP HR world. It is based on the notion of InfoSet. An InfoSet is a group of Infotypes on which you can report.
An InfoSet is always specific to a submodule of SAP HCM, either PA (Personnel Administration) or OM (Organizational Management). Building reports crossing both PA and OM InfoSets in SAP Ad Hoc query is possible but not the easiest endeavour.
How to Create SAP Ad Hoc Query?
Several InfoSets can be available when you launch SAP Ad Hoc query transaction: S_PH0_48000513 – Ad Hoc Query. It can be also be started from SAP menu.
You may have access to different InfoSets depending on your profile authorizations. Additional new InfoSets can be created according to your requirements. Just contact your system administrator.
After choosing the appropriate Working Area and User Group you can see and choose from the existing InfoSets. Next, click OK button to proceed. When the transaction is started, your screen should look like shown on the picture below.
SAP Ad Hoc query is rather easy to use and can answer many requirements. In the list of Infotypes included in the InfoSet, you need to choose:
- Selection criteria
- Output fields
Those do not need to be the same, and one field which is used as selection criteria does not necessarily need to be shown, and vice-versa.
In our example, both Company Code and Employee Group will be used for selection but only the Company Code will be part of the output list.
Once all the selection criteria have been ticked, they appear in the top right panel. From there you can set details of your selection, which are values for each selection criteria. This is also where you can set the reporting period, default being ‘Today’.
Tip: Most often you will report on Today. However, it is interesting sometimes to use other options such as a key date, a period, etc.
The Hit List button shows the number of records matching your selection criteria. It is very useful as it is much quicker than actually getting the full output. It therefore gives you a flavor as to whether your selection criteria are fine, before getting the full output. You can also display only the list of selected employees using the glasses icon.
Then, to get the full output list, you either click on the Refresh button in the bottom panel or click on Output in the top toolbar. In the second case, the output will be displayed in full screen.
Tip: For output, some fields are marked with the icon . This means they can be output as code, text, or both.
It is of course possible to extract your Ad Hoc query results to a file such as in MS Excel. To do so use the Export button and select the Local File option. You will then be able to choose the export format and the location where you want to save the file.
Note that during output, the system checks your authorizations so that you are not able to display via SAP Ad Hoc queries data that you do not have access to usually.
Ad-hoc query offers an advanced functionality named Reporting Set. The idea is to be able to select a list of employees according to some criteria, and then to apply other criteria to get the output list. For instance, you want to list employees which as of today belong to a personnel area 0001, but which belonged to another personnel area on the 1st of January.
To do so, you need to:
- First select the employees which belong to the personnel area 0001 as of today
- Put them in the reporting set
- Apply the other criteria (1st of January and personnel area other than 0001) to the reporting set
To extract the data from the report to a local file, you can use save to local file functionality.
Here, file type can be defined and the output will be saved at the selected location.
Did you like this tutorial? Have any questions or comments? We would love to hear your feedback in the comments section below. It’d be a big help for us, and hopefully it’s something we can address for you in improvement of our free SAP HR tutorials.
Go to next lesson:
Go to previous lesson: SHD0 – Create a Transaction Variant in SAP
Go to overview of the course: SAP HR Training