Creating a SAP Query & Ad-Hoc Query

1. Summary

1.1 What is a SAP Query?

In order to create a SAP Query in SAP R/3, one has to do the following:

1. Create an User Group (SQ03)
2. Create a Functional Area/ Infosets (SQ02)

In the functional area, one has to mention how the data in the query is coming from (i.e. through a logical database or from an individual table using a program or joins etc.). Within the functional area you also have the functional groups categorizing fields from the tables into different groups.

1. Assign the User Group to the Functional Area (SQ03)
2. Create the Query based on the Functional Area (SQ01)

1.2      What is an Ad Hoc Query (or Infoset Query)?

The InfoSet Query is a useful tool for maintaining queries within the SAP Query and is suitable for developing queries and for ad-hoc reporting. This is available in SAP R/3 version ECC.
InfoSet Query is suitable for reporting in all areas of the SAP R/3 system. A special feature is the Human Resources (HR) component. When InfoSet Query is used in HR for ad-hoc reporting, the name Ad-Hoc Query is used instead of InfoSet Query.

Ad-Hoc query provides three different kinds of reports:

Basic List        – Simple reports
Statistics         – reports with statistical functions such as Averages, percentages etc
Ranked List     – for analytical reports

2.    Step I  – Create Functional Area

The first step in the process of creating a Ad-Hoc query is to create a Functional Area. Function area provides special views of logical databases. They determine which fields of a logical database can be evaluated in queries.
Note: The Functional Area is known as Infosets in SAP R/3 version 4.7 and higher.

i.            You can assign one Functional Area to many User Groups.
ii.            The functional are can be created with a logical database or without a logical database. For creating a functional area with a logical database you have to mention the logical database name and then you can select the fields from the tables in the logical database.
iii.            You can create a functional area without a logical database with the following choices
– With a single table e.g. PA0000
– Using a ABAP/4 program
– Using table joins e.g. PA0000+PA0001

2.1   Creating a Functional area/ Infosets With a Logical Database

– Transaction SQ02
– Give the Functional Area / InfoSet name and click on Create button
– In the next screen give a brief description of the Functional Area / InfoSet.
– Specify the name of the logical database, for e.g. PNP
– You can also assign the Authorization Group at this point.

Authorization group for starting queries:-
If you specify an authorization group here, only users who have this authorization group   are able to start query reports for all queries using the InfoSet.
Checks are carried out, for example, before a program is started, to see which authorization group a user belongs to (–> authorization object S_DEVELOP).
Important: If you want to use queries in Web reporting, it is necessary to assign an authorization group to the InfoSet. Maximum eight-character authorization group.
– Save the Infoset and generate it. Don’t forget to generate the Infoset.
– You can also add extra nodes by selecting Go To à Extra Nodes
– The InfoSet determines the objects that you can select with InfoSet Query. The following scenarios are possible
– The InfoSet is based on logical database PNP

InfoSets based on this logical database enable you to use InfoSet Query to select employees. You can use data from Personnel Administration and Time Management and payroll infotypes as selection criteria. From a technical perspective, this means you can use fields from infotypes 0000 to 0999 and 2000 to 2999 and payroll infotypes as selection criteria. For example, you can use the InfoSet to run a report that determines which employees have a particular place of residence.

Furthermore, you can create InfoSets on the basis of this logical database that enables you to report on the infotypes of related objects using InfoSet Query. For example, you can select persons who participated in a particular business event, and output the qualifications of the persons selected. To do this, you must select infotypes from Personnel Planning as well as infotypes from Personnel Administration when you create the InfoSet.

– The InfoSet is based on logical database PAP
InfoSets based on this database enable you to use InfoSet Query to select applicants. You can use data from Recruitment as selection criteria and for output. From a technical perspective, this means you can use fields from specific Personnel Administration infotypes (such as 0001 and 0002) and fields from infotypes 4000 to 4999 as selection and output fields.

– The InfoSet is based on logical database PCH
Provided that object selection is switched on, InfoSets based on this database enable you to use InfoSet Query to select objects of one object type, such as business events, qualifications, and positions. You can use all of the fields of infotypes allowed for the object in question, and all of the object types and their allowed infotypes that can be related with the selected object type, as selection criteria and for output.

2.2      Creating a Functional area/ Infosets Without a Logical Database

You can create an Infoset/ FA without a logical database using

i.   Based on a single table
ii.  Using an ABAP/4 program
iii. Using Table Joins.
iv. Using Sequential Dataset

– Give the functional area name and click on Create button
– In the next screen give a brief description of the functional area
– In the Without a logical database area give the base table on which to form the ABAP query
– To form the query with two or three tables, we need to create a join. For this check the Table Join Check Box and click on the Table Join button.
– The tables to be used in the join should have at least one field in common i.e. it should have the same name, domain or data element. Mention the tables that you want to include in the join condition in the Joined Tables area and press ENTER.
– A checkbox will appear against the table names on the left side, and three radio buttons will appear against the tables on the right side specifying the join type.
– Base table name remains grayed out. Select the two tables to be involved in the join by clicking the check boxes against them. Use menu path Edit => Join => Define condition to create the join. A mapping between the tables will be displayed on the right hand side. Click on the button provided (with + and down arrow symbol) to specify the join.
– Click YES on the Proposal Requested message that gets displayed.
– If the join tables have a foreign key relationship then the common fields in the tables will be marked with 00 to indicate that the join has been created. User can create the join by specifying 00, 01 etc. against the fields that qualify for the join. Complete the join condition by adding more tables if necessary.
– Remaining concepts for creating the functional area without a logical database are same as the concepts for creating the functional area with a logical database.
After finishing everything save and generate the functional area and click on the BACK icon to come out.

3.    Step II – Creating User Group

Here you specify the users who should be authorized to run the query. A user group is always associated with a Functional Area.
– Go to the menu path Environment => User Group or SQ03 à Give a user group name in the screen. Specify the sap logins of the users whom you want to authorize for using the functional area and click on the Create button.
– Use the menu path Assign Functional Area to assign the functional area to the user group.
– Save the user group and back out of the screen by clicking the Back button.
– Note: One user group can be assigned to any number of functional areas with logical database or without logical database.
The final step is to create the actual query.

4.    Step III – Creating the SAP Query

– In order to create the query use the menu path Environment => Queries or SQ01
– Give a name to the query and click on the Create button
– Give the description of the query in the next screen. Specify
– List Format
– Special Attributes
– Table Format (Default 200)
– Print List
– Output Format
– Click on the Next screen icon. All the field groups created in the functional area / Infosets are displayed. Select the groups that you desire i.e. fields from only these groups / Infotypes will be displayed in the output. Click on the respective check boxes and click on the Next Screen icon.
– The Select Field screen gets displayed. Select all the fields from the Infotypes that you need to display on the output of the query. If required, specify the short names for the fields using the menu path Edit => Short Names => Switch On/Off or you can also change the selection text contains in the order you want to appear on the selection screen. You can also maintain column headers for the fields by using the menu path Edit => Column Header => Maintain.

– Click on the Next Screen icon to get the Selections Screen. Here you can check against the fields that you require to be shown on the selection screen.

Field SV  – If you put a checkmark in this checkbox, only one entry field is prepared on the selection field. The pushbutton for choosing the “Multiple Selection” additional field is available, enabling you to use this function to make multiple selections.
Field 1Z – If you select this checkbox, you are able to specify a single value or an interval on the selection screen. The pushbutton for selecting the extra screen “Multiple Selection” is not available, effectively preventing you from making multiple selections. If you want to restrict the selection so that you are permitted to only select single values, also put a checkmark in the first checkbox.
– Now we need to specify the output type for the query as Basic List, Statistics or Ranked List. Choose the option Basic List.
– On the Basic List line structure screen the following things can be done:
– Specify the report layout in detail – lines on which the fields will appear.
– Order in which the fields will appear in the output
– Sort order for the fields – this is optional.
– For the numeric fields you can check against the fields for which you require totals in the output.
– Beautify the output according to the options provided.
– Click on the next screen icon, to specify the control levels as mentioned below
– Specify the sort order. The default sort order is ascending and can be changed to descending if required.
– Totals for each field selected for sorting can be displayed
– To display the output of a field in a box click on the check box against box. To display a line after the output of a field, click on the check box against blank.
– To display the output of a field on a new page click on the check box against New Page

– Click on the next screen icon to get the List Line options Screen. Here you can specify the background color for displaying the output.
– Click on the next screen icon to get the Field Output Option screen. In this you can specify the following:
– Change the output length or the display positions of the fields
– Specify the display position of the unit for quantity or currency fields. Click left radio button to display it before the figure, middle radio button to display it after the figure while last radio button to hide the unit altogether.
– Specify color for the column of every field under the Format option.
– Specify the label against the output of sort fields.
– Click on the Next screen option to go to the Basic List Header screen. Here you can specify
– Give page header and page footer for the output
– Include user name and date by specifying &N and &D respectively.
– Do a consistency check by clicking the  button before executing the query. If the consistency check is without error this query is ready to execute.
– After providing all the above options you can save the query and execute it by clicking the ‘Test; Button .

5.     Ranked List and Statistics

Same process mentioned above can be used for generating Ranked list and statistics. The only difference is that you should choose Ranked list or Statistic instead of Basic List.
5.1        Ranked Lists:  are special outputs where numeric values are summed for key terms and displayed in the table, and the sorting is always by a numeric value called Ranked List Criterion. Only a certain number of additional items are displayed on the output.
5.1.1     One query can have many ranked lists – hence every ranked list should be assigned a title. The default entries in the ranked list can be 10 but user can also change the number.
5.1.2     One of the fields should be defined as the ranked list criterion. The default sort sequence for this field is descending but ascending order can also be specified. An output length and a rounding factor can also be defined.
5.2             Statistics:  a reference currency or reference units to convert all the amounts for the currency fields and quantity fields. You can also maintain the header and specify graphic parameters for statistics.
5.2.1           Each statistic must have its own title, since there can be several statistics.
5.2.2          You can specify the sequence in which you want to output the fields and whether they should be in ascending or descending order.
5.2.3          Totals are always calculated for numeric fields. For this reason, you can also determine average values, the number of selected records (summands) and percentages.
5.2.4          You can define up to 9 statistics for each query. The Next statistic allow you to proceed to the statistics screen where you can define another statistic.

6.     Retrieving Data Using Program

Sometimes a situation arises when an ABAP query’s automatic data retrieval facility is not enough to get the desired results. In that case, the steps up to creating the functional area are the same. Only difference is that on the Title and Database screen specify a structure in the field Table and select the field Data Retrieval Using Program.
The default report name given by the system can be overwritten.
This report needs to be defined before hand – because it is used as a model while generating the query report. Thus the report itself remains unchanged but based on that another one is created.
Note: Ensure that the report is free of syntax errors and has the same fixed point arithmetic settings as the functional area.

7.     SAP Query Authorizations

To use a query, the user must have appropriate authorizations. Two ways of providing authorizations to the users are as follows:

7.1      User groups

The user should be a part of at least one user group to run the corresponding ABAP query. This automatically restricts the access of the user to specific functional areas, and thus the corresponding underlying logical databases.

7.2      Authorizations

The authorization object S_QUERY should be used to give proper authorizations to the user for a query. This authorization object has a field named ACTVT, which can take values 02 for Change, 23 for Maintain and 67 for Translate.
This value determines whether the user can create and modify the query. The possible authorizations in the object are as follows:

• S_QUERY_ALL          Change, maintain and translate query
• S_QUERY_UPD         Change and Translate Though the general concept of an ABAP query is moderately difficult, the results and the long term use of the ABAP query is worth the effort.

8. Creating an Ad-Hoc Query

Start Ad-Hoc Query from the Easy Access menu with a double-click.

Easy Access ->Human Resources -> Information System -> Reporting Tools -> Ad Hoc Query

**If you are assigned to a role that supports Reporting with InfoSet Query and you have started InfoSet Query from the role menu, InfoSet Query starts with an InfoSet and possibly a query. This means you can start directly to create or edit a query.
*If you are not assigned to a role and have started InfoSet Query from a menu, you get to a dialog box first, where you have to select the *Query Area, a User Group and an InfoSet. This brings you to the InfoSet Query screen in which the InfoSet Query is displayed but no selections have been made. You have to make the selection by clicking the Open Query  icon.
There are some special functions in upgraded version of R/3.

8.1     Switch Object Selection On/Off

You are advised to use object selection in Ad-Hoc Query when working with HR InfoSets. The advantage of object selection is that it ensures good selection performance. Furthermore, the following functions can only be used in Ad-Hoc Query if you choose to work with object selection:
* Hit list determination
* Hit list editing
* Set operations
* Restriction of the reporting setChoose Extras ® Switch on object selection

8.2         Overview of Functions

• In the top left section of the initial screen, the current InfoSet is displayed with its field groups in an overview tree. Each field group corresponds to an HR infotype. This overview tree enables you to choose selection fields and output fields.

• In the top right section of the screen, the system displays the reporting period, reporting set, list of selection fields and options, and number of hits in the hit list. This screen section is used to determine selection criteria, and execute object selections. You can open and close the Reporting period and Reporting set screen sections
• Once output fields have been selected, a preview of output with example data is displayed in the lower section of the screen. You can format output at this point. For example, you can change the order of columns.

9. QuickViewer

Please refer to QuickViewer