Ad Hoc Query
Ad-hoc query is a tool to report on data in HCM. You can reporton different areas of HR by choosing different infosets with different logical databases.
- Personnel master data, Data from Time management, payroll results - logical database PNP or PNPCE. PNPCE logical database is an extended version of PNP which contains data from Concurrent Employment as well.
- Applicant master data - logical database PAP
- Data from Personnel Planing, Organization Management, Training and event management - logical database PCH
Ad-hoc query is characterized by simple selection of output and selection fields.
There are three ad-hoc query transactions PAAH, s_ph0_48000510, s_ph0_48000513. It is recommended to use the latest, newest transaction s_ph0_48000513.
After starting s_ph0_48000513 you can choose which infoset do you want to use as a base of your query. You have to choose the query area (standard or global) and the user group the infoset is assigned to. After selecting the infoset you will see all fields assigned to this infoset. You can choose if you want to use them as a selection or as an output field or both.
The initial screen is divided into three areas, containing all the necessary information to create, change and execute a query. Aside from a few dialog boxes, this is the only screen for the InfoSet Query.
The current InfoSet is displayed in the top left-hand corner of screen. You can choose between three display options:
- An InfoSet overview tree, containing the field groups that belong to the InfoSet (default setting)
- Structure of the logical database
- A field catalog
You can open another screen area under the InfoSet display, to display technical information on a selected field.
The selections used as options for reading the dataset are displayed in the top right-hand corner of the screen. Selections are criteria according to which the report evaluates the database and generates a result list.
The output preview is displayed in the lower area of the screen, as long as output fields have been selected. You can edit the output at this point. For example, you can change the order of the columns, form totals and subtotals or display the results list of the query.
After pressing output the query result appears. You can change the settings from this screen from menu 'Edit' - 'Settings' - 'Start via selection screen'. If this box is set after pressing output the selections screen appears.
You can save these ad-hoc made queries into a SAP query as Ad-hoc Query has been integrated with HR information systems to enable you to perform ad-hoc reporting. However, there is no transport link to the Workbench Organizer. In this context, InfoSet Query is known in HR as Ad Hoc Query.
There are two ways of using InfoSet Query in HR:
- With object selection:
Before data is output, a set of objects is selected for which you can output data as required. You can edit the selected set of objects before output. For example, you can display the list of objects, display details on individual objects, use the set of objects as a new reporting set, or relate two sets to each other.
This method of working has the advantage of good system performance because objects are selected using a selection routine that is particularly suitable for Human Resources.
- In basic mode (object selection is switched off):
In basic mode, the report's list of results is output immediately without a hit list being generated first.
This method of working has the advantage of enabling you to use all InfoSet fields for selection purposes. Working with object selection restricts these options.
The following sections describe how to work with object selection. Always use object selection to create reports for HR. If you require further information on how to work with InfoSet Query in basic mode, see the documentation on maintaining queries.
- You can use InfoSet Query to continue processing and report on sets of persons selected using Manager's Desktop or HIS. You can start InfoSet Query directly from these applications. The selected set of persons is transferred to InfoSet Query as a reporting set.
- You can branch from InfoSet Query to general reporting. In this case, you only use the first InfoSet Query level, that is, you select an object set. To report on this hit list, you use a standard report.
- You can enhance queries created using InfoSet Query by adding features that are only supported by SAP Query. If you require further information, see InfoSet Query.
If object selection is switched on, working with InfoSet Query consists of two steps:
- In the first step, you select a set of objects (= hit list) in accordance with selection criteria that you can specify as required.
You can then process the hit list. You can also use InfoSet Query to create two hit lists. You can add them together, subtract one from the other, or use them to create intersections.
2. In the second step, you output data as required for the objects selected in the first step.
You can display and transfer reports as follows:
- You can select a type of output list (basic list, statistics, or ranked list).
- You can select an output type for full screen output (such as SAP List Viewer, standard list, spreadsheet, word processing).
On the InfoSet Query screen, data is always output to SAP List Viewer. Furthermore, the preview of output includes formatting options such as summation and sorting.
You can create an appropriate ad hoc query to specify parameters for Web target iViews.
In logical SAP R/3 Systems of Release 4.6B or higher, transaction SPO0 contains an Ad hoc Query tab. This uses the concept of InfoSets, which are usually based on table joins or logical databases, to create abstract definitions for fields that the system can read. You have to enter parameters for the individual fields. The ad hoc query delivers values that can be passed to the portal.
Transaction SPO0 also contains a buffer for ad hoc queries. This improves system performance by avoiding the need to put together the query every time it is used.
You can delete the contents of this buffer by choosing Delete Buffer for AdHoc Query on the start screen of transaction SPO0. This removes the index entry and the generated queries from the system. We recommend doing this if you change the InfoSet that the query was generated from, for example.
For more information about this function, see the documentation on the InfoSet Query in the SAP R/3 System documentation.
The prerequisites to create SAP, Ad-hoc queries are:
- infosets and user groups are created (SQ02, SQ03)
- infoset is assigned to a user group (SQ03, SQ02)
You can create infosets, user groups in the global or in the standard area. Standard SAp queries are in the global area. Global area objects are cross-client and linked to the Workbench Organizer. Standard area objects are client-specific and can not be entered and transported using customery correction and transport procedures.
SQ02 is the transaction to maintain infosets. Mainly in HR we use infosets based on the logical databases PNP, PNPCE, PAP, PCH.
From the menu 'Edit' - 'Change infotype selection' we can define the infosets we would like to report on. The fields marked blue are the fileds which are already assigned to a filed group on the right side. These are the fields you can use in the query. If necessary to add other infotype fields to the field group you can drag and drop the necessary fields there.
After starting the query transaction SQ01 the user group and the query area can be changed on the initial screen: 'Environment' - 'Query areas', 'User groups'
After choosing the desired one you can add a query name and press 'create'. On the next screen all infosets from the selected user group will be listed.
The first step is to name the query and change some basic attributes if necessary.
After pressing next the assigned infotypes/field groups are displayed and can be choosen:
After choosing some we can see all the fields from these field groups (the infotype fields).
Entering TECH in the transaction field here will result in displaying the technical name of these fields.
You can use the function Jump ® Variant Maintenance to maintain variants for a query.
The Goto ® Report assignment function can be used to maintain entries in the Report-Report-Interface.
The query loaded as a template (and not saved) or the last one used is always the one to be maintained. This means that you can make the entries straight after loading a query.