Create a report with the QuickViewer (SQVI)

Published on | Updated on

Often you just want to create a query because you can’t get the information from a standard SAP report. This can then be created relatively easily with the QuickViewer (SQVI) or the Query Designer (SQ01).

In the specific case, I want to create a query about users and display their email address and SNC configuration. The e-mail addresses can then be used, for example, for an e-mail to a dedicated group of people. The SNC configuration is needed to check which users do not have an SNC configuration yet.

To create queries, SAP offers the QuickViewer for user-specific queries and the Query Designer for specific groups of people.

Important for the queries is to know which tables we have to address. In this case, the required information is equally distributed over four tables. In addition to the e-mail address and SNC configuration, the SAP user name, first name, and last name should also be displayed.

Create report with QuickViewer (SQVI)

Therefore we need the tables: USR21, ADRP, ADR6 and USRACL. We start with transaction SQVI and create a new query.

create a new query in transaction SQVI
create a new query in transaction SQVI

In the query, we then select a table join, since we want to link multiple tables together.

Select "Table Join" data source
Select “Table Join” data source

The tables are then inserted individually. We will start with the USR21, as this is the table to which the other tables will link. When adding the individual tables, the system links the tables independently. This will be adjusted when all tables have been inserted.

Insert SQL tables
Insert the necessary SQL tables.
Table overview with automatic links
Table overview with automatic links

As you can see, the tables are essentially linked via BNAME (SAP user name) and PERSNUMBER (unique ID). The USRACL is an exception here for now, which we will correct.

The links should all be created directly with the USR21 table. Therefore, the links between ADRP –> ADR6 and ADR6 –> USRACL must first be resolved. To delete these links, right-click on a link and use the context menu to delete the link.

Customize table links

The table link must then be recreated. Once for USR21 –> ADR6 and then for USR21 –> USRACL.

Create new table link
Create new table link
Revise table link
Revise table link

The links are set automatically again. In this use case, they are also correctly associated with BNAME and PERSNUMBER. But you could also correct the link manually with the “handle”.

The tables are then displayed as follows:

Table overview after adjusting the table link
Table overview after adjusting the table link

Now one is almost ready. But we still have a problem with the current configuration. We would only get back the records that meet the selection condition on both sides, since an INNER join is used in the standard.

Or in practical terms, if we search for users without email address, the query will not work.

Therefore, we need to change the join conditions for USR21 –> ADR6 and USR21 –> USRACL to left-outer join. Thus, we also get users without e-mail address from the ADR6 table and users without SNC configuration from the USRACL.

The join of the table links must be set to Left Outer Join.
The join of the table links must be set to left-outer join.

The join to the ADRP does not need to be adjusted in this example, since the first name and the last name are selected here later and I assume that these are given.

The join to ADR6 only needs to be adjusted via a link. The final result looks like this:

Table overview after adjustment of the joins
Table overview after adjustment of the joins

This completes the table configuration. You can’t save it at this point. Go back here with F3 and we come in the QuickViewer to the initial screen and the basic mode.

Here we define which data we want to be displayed after running the report (display fields) and which data are available as selection fields. This does not have to be a 1:1 match.

Selection and display definition
Selection and display definition

Important to know, the order of selection also determines the order in which it is displayed in the report. This means that if you want the username to be displayed first, then you should select it first.

The order of the individual fields coming from a single table can be rearranged using the display fields. However, it is not possible to change the complete order.

Completion of the selection and display definition
Completion of the selection and display definition

At this point you can save and run the report.

Run created report
Run created report

The test should now output all users. Here you should test with a known user group to check the join conditions.

In this case, the output of the users is as expected.

Run and check report
Run and check report

To now display users that do not have an SNC configuration in the user master, set the SNC name selection to =.

Search for users with empty SNC configuration.
Search for users with empty SNC configuration.

SAP user without SNC configuration
SAP user without SNC configuration

Change table configuration

If something still doesn’t fit and you want to adjust the table configuration, you can do it in Basic mode under the Data Source tab. This can also be used later to extend or adapt the report.

Change table configuration
Change table configuration

A small disadvantage of transaction SQVI is that this report is only available to the user who created it.

You can do this as follows.

Release report

For each report you can generate a program in QuickViewer. This program name can be displayed in the same menu item.

Generate program
Generate program
Display report name
Display the report name of the query

You can then execute this program name in the TA SA38, for example, or also create a transaction from it.

Provide report with the Query Designer (SQ01)

Another variant is to make this report available to a user group using the Query Designer. Often underestimated and yet it is very helpful.

SQ01 – Basic configuration

First of all, the environment from which the queries are to be displayed must be specified.

Set Query Area
Set Query Area

Since the previously created user query is client-specific, the working environment must also be set to be client-specific.

Query Area - client-specific
Query Area – client-specific

The next step is to specify a user group. If this does not yet exist, it must be created.

Edit user groups
Edit user groups
Create user groups
Create user groups

Once the user group has been created, it is still necessary to assign the appropriate users to this user group.

Assign users to the user group
Assign users to the user group
Add user
Add user

After the users have been saved, go out of this menu as well as the menu of the user groups with F3 and select the corresponding user group via the menu item “Edit”.

Select user group
Select user group

SQ01 – Convert QuickView

After the basic configuration is done, the actual QuickView can be converted and made available to the user group.

Convert QuickView
Convert QuickView
Create report with QuickViewer (SQVI) or Query Designer (SQ01) 1

In the following dialog, the query name can then be specified under which this will be displayed to the users of the SAP_BASIS group.

Convert QuickView and specify new query name
Convert QuickView and specify new query name

With this last step, everything is done. To use this query, users must call transaction SQ01, set the query area to client-specific, and select the correct user group.

With F8 the query is executed and the query mask appears again

Select and execute new query
Select and execute new query
SQ01 - Execute query
SQ01 – Execute query

I hope that with this example I could show how relatively easy it is to create reports with the Query Designer and make them available to other users.

More information: help.sap.com: SAP Query

Comment on the post

Please write a comment.

Your comment will be moderated by an admin before the activation.