Introduction:
The Ad-Hoc query is a tool for building reports and queries on basic data in all areas of the SAP system. When used in HR for Ad-hoc reporting, it offers access to data of all SAP infotypes.
Terms use:
1) Infotypes: SAP HR uses infotypes to store all relevant employee data required for administration purposes. Ex: Personal Data (Infotype 0002) stores the employee's personal data (i.e., first name, last name, birth date, marital status).
2) InfoSets: semantic layer over the data sources that provide special views of logical databases and determine which fields of a logical database or data source can be evaluated in queries
3) User group: each user can be assigned to several user groups and with appropriate authorization user can change queries or define new ones. User are not allowed to modify queries from other user groups but under certain circumstances queries can be copy and execute.
Procedure to create Ad-hoc reporting
1. Creation of user group
2. Creation of infoset
3. Creation of queries
The transaction codes associated with ABAP Query are
• SQ01 - SAP Query
• SQ02 - Infoset
• SQ03 - User group
Sample creation of the Ad-hoc query:
A) Create/maintain user group: SQ03
1) Execute TCODE: SQ03 -> Select "Environment"->"Query areas"
2) Select "Global area"
Explanation on work areas:
- Standard area which is client dependant
- Global area which is client independent.
- Global area will requires transport request to create any object in Global area, meaning no separate process for transport request required. If any change is required in any component in future, it can be done in development client and then moved along the landscape.It ensures that all queries under the infoset created in global area will also have to be created in global area.
3) Enter a new user group name and click "Create" button
4) Enter the descriptions
5) In this example we create as a local object
6) system prompt the group created successfully
B) Create new InfoSet: SQ02
1) Execute TCODE: SQ02, enter a new InfoSet name and click "create" button
2) In this example, select "Table join" and enter an table name that will be use in query in the data source option and enter the InfoSet name, Click the "check mark" button
3) Select "Edit" -> "Insert Table"
4) Enter/select additional table that will be use in the reporting
5) Repeat step 4 to add any other relevant tables for the reporting, click the "back" button or "F3" once all the tables added
6) Select "Create empty field groups" when prompt appear.
7) The InfoSet builder screen appear
8) Begin InfoSet building by select the relevant node on the right panel and follow by right click on the required field to be add into the field group panel
9) Example of fields been add into the fields group
10) Click "local object" when saving the InfoSet.
11) New InfoSet been created successfully
C) Assign InfoSet with User Group: SQ02
1) Execute TCODE: SQ02, enter the newly created InfoSet name and click "Role/User Group Assignment" button
2) Select the relevant user group to assign with.
3) Assignment complete.
D) User group and user assignment for InfoSet: SQ03
1) Enter the InfoSet name and click "Change" button to verify the group been assign
2) User group been assign, select "back" button or "F3".
3) Enter the user group name and click "Assign users and InfoSets" to assign user access for the particular InfoSet
4) Enter the relevant username that can access the InfoSet and click the "Save" button.
E) Assigning InfoSet to Query and Create SAP Query: SQ01
1) Enter a new query name and click the "Create" button.
2) Select the InfoSet.
3) Enter the query title
4) Select the relevant options with the wizard builder.
5) Select the options required
6) Select the options required
7) The sample of the report build
8) Click the "Test" button to view the outcome
9) Depends on options select previously the "program selections" will be vary
10) Sample report of using ABAP List
11) Sample report of using SAP List Viewer
