SAP Ad Hoc Query – How to Create Ad Hoc Reports?

Advertisement

SAP Ad Hoc QueryWelcome 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.

Advertisement

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.

SAP Ad Hoc Query Transaction in SAP Menu
SAP Ad Hoc Query Transaction in 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.

Create New SAP Ad Hoc Query - Select InfoSets
Create New SAP Ad Hoc Query – Select InfoSets

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.

Create New SAP Ad Hoc Query - Main Screen
Create New SAP Ad Hoc Query – Main Screen

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.

Choose Selection and Output Fields of Infotypes
Choose Selection and Output Fields of Infotypes

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.

Advertisement

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.

Choose Reporting Period and Adjust Values for Selection Criteria
Choose Reporting Period and Adjust Values for Selection Criteria

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.

Hit List Button and Glasses Icon
Hit List Button and 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.

Advertisement

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.

Export Button
Export Button

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.

Reporting Set

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
Select Employees that Belong to Personnel Area 0001 Today
Select Employees that Belong to Personnel Area 0001 Today
Reporting Set with Intersection of Two Sets of Employees
Reporting Set with Intersection of Two Sets of Employees

To extract the data from the report to a local file, you can use save to local file functionality.

Export to Local File Button
Export to Local File Button

Here, file type can be defined and the output will be saved at the selected location.

Choose Where to Save Exported Report
Choose Where to Save Exported Report

Advertisement

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.

Navigation Links

Go to next lesson:

Go to previous lesson: SHD0 – Create a Transaction Variant in SAP

Go to overview of the course: SAP HR Training

Page last updated by Cleo Isco on

1 thought on “SAP Ad Hoc Query – How to Create Ad Hoc Reports?”

Leave a Reply

Do you have a question and want it to be answered ASAP? Post it on our FORUM here --> SAP FORUM!

Your email address will not be published. Required fields are marked *