on 10-15-2015 9:36 AM
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
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:
The main Ad Hoc query screen is then displayed.
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.
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).
This then shows the available options that have been configured for that field:
Option
Once an entry is made in this field, the icon in the '‘Option’ column will default (initially) to ‘equals’
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:
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.
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.
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
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.
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: Are able to show either Value or Text, or both. Right clicking on the field name and choosing output will show the options: Selecting one of these will change the display information:
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: Are able to show only Value. Right clicking on the field name and choosing output confirms this: |
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.
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.
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:
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.
Once this is confirmed, the descriptions displayed for the query change from the SAP default:
Main screen
Output
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:
Selecting this option, displays an additional page tab, and 2 extra buttons next to the [Hit list] button:
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,
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.
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):
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’:
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]:
Run the query to see the results:
Ad Hoc Query: Additional Tips and Tricks
Default User 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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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’.
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:
Once a suitable name has been chosen, clicking [CREATE] shows the following dialog:
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
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:
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 field | Value |
D_SREPOVARI-REPORTTYPE | AQ |
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.
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.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.