This blog will focus on the capabilities of SAP Query (a.k.a. Adhoc Query) and Creating Custom fields in infosets, with emphasis on HR Module.
Most of the reporting needs of an organization can be addressed through Adhoc Queries. For some specific requirements we will need custom ABAP reports, and to create custom fields ABAP knowledge is required.
First let’s refresh some concepts, processes and transactions involved in the process.
Here we will include all of our tables or infotypes that will provide us data for our queries. If you are trying to create queries in HR Module then it is strongly recommended to use a Logical Database to create your queries, because if you are using HR Tables then joining them will be a problem because of date fields (BEGDA/ENDDA).
Logical database will always use inner join and will skip the rows that will not match in corresponding infotypes. For example: You are trying to create a query with Pernr and name from infotype 2 and Work Address from infotype 6. Then the report will not include the persons that do not have a work address. Only employees shown on the report will be those that have a work address subtype of infotype 6 populated. This constraint is usually overlooked and it results in wrong reports.
Steps to create a new infoset using logical database PNPCE:
Once you have created the infoset you have to assign it to a user group. To create a new user group go to transaction SQ03 and create a new user group and assign users to that user group. If you already have a user group then you do not need to go to SQ03, on the main screen of SQ02 you can click on button ‘Role/User Group Assignment’ to specify the user groups tied to that infoset.
Ad-hoc queries (a.k.a. SAP Queries) are used for reporting off the infoset created in previous steps. So go to transaction SQ01 and create a new query:
NOTE: You cannot change the infoset later, so be sure which infoset you want to choose, otherwise you will have to create a new query to choose a different infoset.
Now, we will come back from refreshing memories of creating queries and jump into a little technical area.
We can create fields that will fetch data according to out own logic, fields that are not provided by SAP. For that, you will need ABAP access to the system, so if you are working in Production or Quality system then most probably you will not have authorization for that. If you need custom development then best place is the Development environment. We can then either transport the infoset from Development to next systems or we can download and upload it in production (which will again need production system to be opened for some time).
To create a custom field:
Local Fields are used in queries, if you already have your infoset developed and want to include some simple logic then Local Fields can be useful. They are usually a calculation on another field, like for example if you want to add a field for membership status depending on field Age (Assuming you already have a field Age in your infoset, or Age is another calculated local field).
We want a field that will return values based on following logic:
If Age is less than 21, then return ‘Junior’.
If Age is between 21 and 65, then return ‘Adult’.
If Age is more than 65, then return ‘Senior’.
Go to menu item ‘Edit -> Local Field -> Create’
Field Label will be visible inside SQ01 query creation so we can say it is for SAP HR Person who will be creating or modifying the query, and Field Heading will be visible at the top of the field when the query output is displayed i.e. it is for end user.
You will find rest of the items on the above screen pretty self explanatory, click on ‘Complex Calculation’ (there is nothing complex here, do not worry).
Here is a screenshot on how to use the Complex Calculation screen:
If you want to add more conditions then play with + and – icons on the above screen. And, you are done with creating local field.
A very very interesting area that is mostly ignored and has a great potential for interactive reporting. To use this feature, go to menu item ‘Goto -> Report Assignment’.
Lets say we are developing a simple report in HR, that displays Personnal Number, First Name and Last Name. Your report lists a lot of record and you want the user to have power to double click on any employee row and then open up the details in PA20. Isn’t that fascinating?
Another example would be that when a user double click a query row, the data of that employee is passed to an ABAP Program and that triggers some interface or whatever is coded in that program.
Report Assignment is in fact making the reports interactive. SAP provides various options for that. It will take many pages to go through all of them, for the sake of understanding we will go through the simplest of them.
Goto menu item ‘Goto -> Report Assignment’.
Click on ‘Other report type’ and you will see the available options. Here are the actions that can be done when user double clicks the row:
Select ‘Transaction’, on next screen you will be asked to enter the TCODE.
Save the program, and run the report. And, double click on any person’s record and you will be brought to transaction PA20 with that person’s data populated on the screen. You can also choose to go to PA30 so that user can change the data quickly.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
5 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 | |
1 | |
1 | |
1 |