|
SAP HR Ad-Hoc Query
One of the key requirements of a Human resources management
system is the ability to facilitate decision-making use of
the information in the system. The reporting functionality
within SAP HR is one of the main reasons why clients have
chosen SAP rather than another HRIS - one of our former research
articles takes a closer look at the different reporting tools
available and how to make most use of them.
Reporting can become very challenging when the differing
needs of many decision-makers - human resources, payroll and
benefits professionals, line managers, and executives - must
be met. SAP HR has responded to this challenge and offers
a variety of access methods designed for the needs of different
users. This article focuses on the functionality of Ad-hoc
Query, a tool that still isn’t used by all companies
who have SAP HR. Some of these organizations are still unfamiliar
with the full potential of Ad Hoc query and are afraid to
use it. This article has a closer look at how easy it is to
use ad hoc query and how this simple but powerful tool can
make your reporting so much more efficient and less time consuming.
AD HOC QUERY AS AN IDEAL TOOL FOR
DIFFERENT TYPES OF USERS
The advantage of Ad Hoc Query is that it enables occasional
system users or people with little experience to define their
own reports according to their personal requirements in an
easy and quick fashion. It is an ideal query tool for anyone
who needs to obtain human resource information when and as
needed.
An overview of different types of available HR information
(for example Personal data, Addresses, Basic pay, Organizational
assignment etc.) is displayed in a list, which includes all
the infotypes and the relevant fields, users need to pull
the information from. This list is obtained by creating a
functional area. Users simply select the information they
want by marking checkboxes in the list. Report results can
then be formatted to users' needs and preferences; for example
the presentation style or the sequence of columns in the report
can be changed. Users can create and then format the output
of the desired report all in one screen and save the query
to run it whenever needed.
HR Professionals
The Human resources, payroll, and benefits departments need
to run a great array of reports – SAP provides reporting
tools supporting both regulatory reporting via a series of
standard reports as well as query options that support customized
reports and strategic analysis tools. While the legally required
reports are included as a standard part of the SAP HR system
and have some degree of selection options (usually organizational
assignment selection options), these reports cannot be altered
by the user to suit his or her specific requirements –
they might not include all the data users need to obtain,
might not have the option to be downloaded to an Excel spreadsheet
or might not have the right format. This is where Ad Hoc Query
is so significant. HR Ad Hoc Query enables users to create
company-specific reports that respond to their ever-changing
needs for information about employees and the organization.
The user simply selects the fields he needs to report on from
a check-box screen. This report creation process requires
minimal training and allows users to perform detailed analysis
of human resources, payroll, and benefits information to support
the needs of their organization. Integration with Microsoft
Word and Excel allows users to format and modify the output
to suit their needs or to save their reports in HTML format
in order to e-mail them to other users.
Occasional Users
Even individuals such as Managers outside the HR/Payroll/Benefits
departments who have no previous experience with SAP HR can
use Ad Hoc Query to define the reports they need. It is critical
for these managers to be able to report on information about
their staff. It happens all too often that they need a specific
report and have to ask HR to produce the right data –
this is both frustrating for the Line Manager having to wait
and for HR who might have other critical deadlines. Ad-hoc
reporting, which provides occasional users with easy access
to the right information for their specific decision-making
needs is an ideal solution. And since users can run and create
their own reports, it frees both HR and the User considerable
amounts of time.
Line managers are able to access information about their
specific organizations when they need it, in the formats that
they require and can easily access data in the SAP HR system,
as well as information from other business applications, to
obtain the company-wide information they need for analysis
and planning.
ACCESS
As will all other SAP applications,
flexible security and access authorization can ensure that
access to information is restricted to defined areas for each
user. HR reports almost always contain confidential data and
special attention needs to be brought to setting up proper
activity groups. Whereas regular HR users will use the Ad
hoc screen within the HR module to create and run their reports,
Line Managers – rather than using the HR module should
be using Manager’s desktop which provides access to the
same standard and ad hoc reporting capabilities as described
above but also include cross-application functions such as
workflow tasks or CO reports, the display of Intranet and
Internet pages etc...
AD HOC QUERY IN PRACTICE
Prerequisite Activities
Before users can create and run their own reports, an administrator
must create a functional area and a user group. These are
created using the ABAP query component and necessary to set
up the appropriate working environment for end users. The
R/3 System contains hundreds of thousands fields in logical
databases and tables and a logical database is simply a special
ABAP program that combines the contents of specific database
tables. For example the logical database “PNP” contains
all tables related to HR Master data.
Since a user cannot be presented with thousands of fields
to choose from – even from one particular logical database,
to create a report, functional areas are created. When creating
a functional area, first a logical database is selected, such
as PNP which contains all the relevant tables for a particular
module or sub-module. When selecting PNP which is relevant
to HR master data, the administrator needs to pick which infotypes
should be included in that particular functional area from
the total number of infotypes attached to the logical database.
A functional area determines which fields of a logical database
can be evaluated in queries.
A functional area also allows defining auxiliary fields.
In many cases, the information that a logical database supplies
is insufficient. For this reason long texts are normally stored
in tables that are not part of the logical database. These
long texts are, however, useful when evaluating queries, for
example when you want to display the full text of an advertisement
(within recruitment) rather than just its number. Both additional
tables and additional fields can be added to a functional
area. Any table can be added as additional tables, provided
they are defined in the ABAP Dictionary and when adding a
field, the ABAP code must be specified and the field assigned
to a functional group.
By creating functional areas and assigning them to user groups,
the system administrator determines the range of reports the
end-users can access.
Four essential questions have to be answered before creating
a functional area in order to determine the reporting requirements:
Once these questions have been answered, the administrator
can create the functional area using transaction SQ02.
| Step 1: In the field Functional
area, specify any name and choose Create. First,
you enter a description of the functional area.
Since the end-user sees this name when creating
a query, it should easily identify the functional
area. |
 |
| Step 2: You can also enter an
authorization group at this point – which is
not to be confused with user groups. Authorization
groups can be assigned for any reports and determine
a user’s right to execute a program whereas
user groups help you to control access and change
authorizations for queries. |
|
| Step 3: Next specify the logical
database, |
|
| Step 4: In the Selection screen
version field, you can enter an ID for the version
of the selection screen. If you leave the field
blank, the system calls the standard selection screen
when the query is executed. |
|
| Step 5: Save the functional area,
choose the infotypes you want to include –
this list should have been determined previously
and execute to fill the functional area with data.
|
|
| Step 6: The system will display
the screen for defining functional groups. This
screen is divided into 2 trees: the first one “Functional
groups” will display all the infotypes that
were included into the functional area. By opening
one functional group, users can display what fields
are currently selected and decide which fields are
needed and which one’s are not. Adding and
removing fields for each functional group is carried
out via the second tree “Logical database”
which lists all the fields that can be included
for selection for each infotype. Determine which
fields you want to include into the functional groups
in the second tree. If there are certain fields
that will almost always be used in a report, such
as personnel number or employment status, these
should be added into the functional group “key
fields”. |
|
| Step 7: Save and generate your
functional area. |
|
| Step 8: Functional areas are
available to an end-user for each user group of
which he is a member. In these functional areas,
the end user can define queries and execute them
to generate the desired lists. A functional area
therefore needs to be assigned to one (or several)
user-groups. Go to the user group screen (via environment
> user groups) and assign the functional area
to the chosen user groups. Each user group should
contain user names. |
|
Now that you have generated your functional area and assigned
it to a user group, users can create and manipulate their
own Ad hoc queries. Unlike ABAP query, in which you users
have to define reports, Ad Hoc query only requires to choose
the selection (e.g. report should be run for an employee subgroup,
or job or personnel area or cost center etc) and output fields
(what should the report display and in what order?) by flagging
them in a tree structure reflecting those infotypes chosen
when creating the Functional area.
CREATING A QUERY
Users can access Ad hoc query via: Human resources -> Information
system -> Info tools.
From the initial Ad hoc query screen, users must decide if
they want to run the report they are about to create for either
individual personnel numbers or use the function Selection
via Organizational structure to restrict the display to those
personnel numbers that belong to the organizational unit selected.
If no organizational unit is selected, the system will select
all employees that correspond to the selection criteria.
The next step is to select both the selection fields and
the output fields. By clicking on the field “Field selection”
– users will be presented with the functional groups
inherent to the functional area attached to their user group.
They simply need to open up the tree structure, select the
appropriate fields. For each field, users can decide whether
they want to select it as a selection field or as an output
field. For output fields users can further decide if they
want to display the value (i.e. ethnic origin code “50”)
or the actual text of the field (i.e. “White non-Hispanic”)
Once all the fields have been selected, those marked as Selection
fields will appear on the “selection fields” column
of the screen and those marked, as output fields will appear
under the output column. Choose a value for your chosen selection
criterion (or leave it empty to select all employees) and
proceed to the “define output” tab. The order of
the output fields can be changed here as well as the layout
of the report – users have the choice between a basic
list, a spreadsheet, word processing document, statistics,
ranked list etc
Now the report is ready to be run. By clicking on the “Select”
button, the report will select all the relevant personnel
numbers found. Personnel numbers, which includes employees’
names, can be displayed by clicking on the “list”
button. If required, users can mark personnel numbers to be
excluded from the report field.
The expert mode enables users to request more complex reports
providing enhanced functions for the Selection and Output
fields. It is particularly useful when reporting on specific
subtypes or wage types which is only possible using expert
mode – in “Simple mode” the report will select
all subtypes or wage types of a particular infotype. The expert
mode needs to be turned on each time before running the report
– saving the report in expert mode does not suffice.
Finally, by clicking on the “output” field, the
report is run and will display all the found data in the chosen
format. From this screen, the report can be emailed, graphical
statistics can be run and the list can be sorted according
the various criteria.
Before exiting, the system will prompt the user to save the
Query. It makes sense to save a query so that it can be run
again at any time in the future. A title and a name (proposed
by the system but it can be overwritten) should be given at
this point. The tab “Saved queries” allows to access
all saved queries.
Users can also report on the selected personnel numbers using
other reporting tools. By choosing Goto > Standard report,
a dialog box appears in which they can either enter a report
name directly or display a list of possible reports.
CREATING A QUERY
R/3 Release 4.6C has introduced “InfoSet Query”,
a new tool based on SAP Query technology that enables end
users to quickly and easily generate or modify ad hoc reports
on their own. Via this new tool, which uses the SAP role concept,
users can create and publish Web reports that can be accessed
via a browser, and export reports to Microsoft Word and Excel.
That's good news for end users, and even better news for IT
departments, which are often overrun with end user requests
for these types of requirements. This new tool is called InfoSet
Query and includes the following new functions:
Reporting on Personnel Planning
Data
Up to now, Ad Hoc Query only enabled to select sets of persons.
No Personnel Planning data could be used to make initial selections.
As of this release, users can select objects from Personnel
Planning by using an InfoSet specially created for selecting
a specific object type, such as business events or positions.
Deactivating Object Selection
The object selection such as the person selection can be deactivated
to work in "basis mode". Thus, users can access
all basis mode functions - for example, they can select according
to texts.
Drag & Drop
As of release 4.6, users can “drag & drop” to
select fields. They simply need to drag one or more selected
fields to the required area. They then serve as selection
and/or output fields.
Displaying the Last Queries
Used
Up to now, users could only load former queries using a dialog
box accessed by choosing Query -> Open – which is
still available. But the Query menu now includes the last
five queries used. A simple mouse click enables users to load
them.
Processing Fields With Text
Up to now, you could select entries in separate columns for
fields with text to determine whether the value and/or its
text was output.
In 4.6, each InfoSet field is flagged with an icon that indicates
whether it is a simple field, or a field with text.
Displaying Field Characteristics
The InfoSet Query screen enables you to show an additional
screen area containing technical information on a field. If
you want to show the screen area for a field or display technical
information on a different field, access the context menu
for the field in question and choose Display field information.
User Settings
Up to now, Ad Hoc Query was always started using the standard
settings (for example, the basic list was always used as the
type of output list).
As of this release, your settings are saved and then reloaded
the next time InfoSet Query is started. For example, if you
create a statistic, exit InfoSet Query (or log off the system),
and then re-access InfoSet Query later, statistics is used
again as the type of output list.
New Maintenance for HR InfoSets
Up to now, texts on particular field values were presented
as additional fields, which had to be selected explicitly
when a functional area was created.
As of this release, texts are determined automatically. That
is, if you select a value field for an InfoSet, its text is
automatically selected too.
The selection of infotypes in an InfoSet can be changed as
required later. Up to now, infotypes could only be added.
As of this release, it is also possible to remove infotypes
later (provided that none of the fields is used in a query).
Simulated infotypes
These infotypes enable users to run evaluations and reports
using data from different data sources (infotype data, time
evaluation results, personal work schedule data) available
in an overview using the infotype concept. For example, users
now have the option of defining time infotypes by combining
any relevant time types, absence and attendance types, or
wage types to create specific balances they looking for. The
system reads the relevant tables in cluster B2 and stores
a balance in a “simulated” infotype for each day.
These balances can be queried as though they are infotypes
(EE times = IT2502, Quota statuses = 2502) These simulated
infotypes can be used similar to regular infotypes for Ad
Hoc Query reports because they are provided in the same manner
as other infotypes in the logical database PNP.
With the improved functionalities in release 4.6 there is
really no excuse for not using Ad hoc Query – which is
all about empowering the users and giving them instant access
to information without having to involve HR or IT departments.
Ad hoc query is the most powerful and user-friendly tool for
evaluating Human Resources data. Its simple range of selection
and output fields enable users to access data from all areas
within HR - and this without any programming skills.
|
