We have already explained what SAP queries are and how you can authorize them in the blog post “SAP Query: Reports from database tables”. Today we are rounding off the topic with a “how to”.
This is the right post for you if you want to know
- How to create and use an SAP query
- How to create infosets and link tables
- How to set up user groups
Our working example is a very simple case. You would also get the data if you simply switch between two tables or also use SU01. It is not a case for which you would normally create a query. But how queries work can be better explained using a simple example. And: You can get your results faster and more conveniently via the query, even in such a simple case.
Our example: You are always looking for the assignment of roles, user, user name and expiration date of the roles. Often you only have the name of the user, but not their user ID/user name. You now want to build a query that shows you the following information using only the first name or surname search parameter, for example:
- User name
- Full name of the user
- Roles assigned to the user
- Start and expiration date of the roles
- whether the role comes from a composite role or not
1. Create user group
First create a user group. To do this, call up transaction SQ03.
Important: Pay attention to the work area: It is advisable to work in the global area if you want to use a query in all clients. You work in the standard work area if you only need the query in one client that is logged on. The global workspace is connected to the transport system, while the standard workspace is independent of it.
You can change the workspace in the menu bar under “Environment”.
Now enter a name for your user group, click on “Create” and add the users who are to work with your query later on in the following screen. You can tick a small checkbox next to the user name if the user should not only execute the query but also be authorized to change it.
2. Create info set
A query evaluates data. It does not work without a data basis. In the next step, you select this data basis, i.e. create the so-called infoset.
Reminder: We want to create a query that conveniently displays the relationship between the user ID, the full name of the user, their roles and their validity period.
To do this, we create a join from the AGR_USERS and USER_ADDR tables as a database. These tables contain all relevant data. By joining the tables, we bundle them together. We can then display them in the query with one call instead of having to constantly switch between the two tables.
To create the infoset, call up transaction SQ02 and check that you are in the correct work area. Then enter a name for your Infoset that is as unique as possible and click on “Create”.
A pop-up opens in which you can select a short description and the type of infoset. We need data from two different tables and therefore select the “Table join” option. Enter the name of one of the two required tables – in our case AGR_USERS – and confirm your selection by clicking on the green tick.
The overview screen appears, which initially only shows the first table. To complete the link, click on the “Insert table” icon in the menu bar, enter the name of the second table (here: USER_ADDR) and confirm. Both tables are displayed on the screen, automatically connected by a line if the system recognizes the fields as key fields. However, you can also adjust these later.
The table join is now complete. Click on the “Infoset” button, confirm in the next window that all table fields are to be included and generate the Infoset.
Then call up the start screen again (transaction SQ02) and assign the Infoset you have just created to your user group.
3. create Query
Only now can you create the actual query. But this is done quickly. Call up transaction SQ01 and, as always, check that you are in the correct workspace.
Then use the “Change user group” icon to specify which users are allowed to execute and edit the new query. Add the user group you created earlier here. Then give your query a name that is as comprehensible as possible and click on “Create”.
A pop-up opens with all infosets assigned to the selected user group. Select the Infoset you have just created and link it to the new query. Enter a short description for the query in the next screen and click on the “Basic list” button.
The most important screen now opens: At the top left, you will see the two tables that form the basis of your infoset and therefore also your query. Open the tree structure to see the individual table fields. In the column to the right of this list, you can now select which table fields are to be used in the query.
You have two options:
- Selection fields: These are the table fields that you can later use as search criteria.
- List fields: These are table fields that are displayed as search results for the query.
In our case, we are generous when it comes to the selection fields because the query is to be used for various authorization requirements, for example:
- There is an authorization problem. However, they only know the user ID/the name of the employee/the roles and also need the other information.
- The specialist department needs an overview of users and assigned roles, but including the full names of the employees.
We therefore allow the users of the query to select by full name, USER ID and role when they start the query. We select these fields as selection fields.
At the same time, we also mark the same fields as list fields. They are therefore always displayed in the query evaluation. We also select the start and end date of the roles and the Col flag as list fields. The latter marks single roles that are not directly assigned but originate from a composite role.
The query is now ready: you only need to save it and can either test it directly from the screen (simply leave the pop-up asking for variants empty). Alternatively, you can start the query from transaction SQ01 or SQ00.