Queries in SAP are report-building tools, and functional SAP consultants can use them to develop simple ad-hoc reports. This blog post will talk about the transaction codes SQVI, SQ01, and SQ02, which can be used to create these queries.
It’s important to know how to use these T-codes since it will help streamline the reporting process and provide queries to business users in a timely manner. Let’s dive right in and see how they work.
SQVI
SQVI is a simple ad-hoc report that can be written directly in the production system, meaning there is no need for transporting information between systems. Any functional consultant (or even business user) with a good understanding of SAP tables can write a query with SQVI.
Let’s consider this use case: The client has requested from you a report that displays billing information for both the customer and customer group.
To get this report, go to transaction code SQVI. Type in a unique name for your QuickView (the term QuickView inspires the “QVI” in SQVI) and hit the create button.
Next, fill in the title and comments fields. There are three options for picking a data source; in this case, you would pick table join, select the radio button for Basis Mode, and then hit the green checkmark.
On the next page, you’ll hit the Insert Table option, or hit Shift + F1. (Pro tip: always insert the primary table first and then the other tables. SAP will not allow you to delete a primary table, but you can delete secondary tables if needed.) For our example we’ll add VBRK first, then VBRP and KNVV.
SAP automatically creates joins between the tables. You can delete or change the definition of the join by right clicking on it. For this example, let’s delete the joins between VBRP and KNVV, and manually create joins between VBRK and KNVV. We will link the Sold To field from VBRK to Customer Number in KNVV, and link Sales Org from VBRK to Sales Org in KNVV.
Once the joins are validated, it is a best practice to click on the check link condition option, or hit F8. Next, click on the green back button which will take you to the following page.
We normally suggest to use the data fields section to add fields to the selection screen and output. For this example, we want to use Billing Document, Billing Item, Sales Org, Billing Date, Sold To Party, and Customer Group.
We can move the fields around in the order we like by selecting the field and using the up and down arrow keys, as shown in the figure below.
To sort, for example, by Billing Date, follow these instructions: find the sort field tab, select Billing Date, and move it to the “Select Sort Order” side by using the left arrow key.
To change a label, toggle to layout mode, or hit F5. Select the field on the right-hand side of your screen and relabel it. Note that this would only change the label in the output but not on the selection screen.
Then hit save.
You will see a few warning messages that can be validated, and then hit the green checkmark. Next, click on the execute button, or hit F8. The program will compile for a few seconds as this is the first time it’s run, and we’ll see the screen as shown below.
Doesn’t this report look exactly like one your ABAP developer would have created? You are on the way to becoming a techno-functional consultant!
Lastly, add your selection criteria and execute the report.
SQ02/SQ01
I intentionally write T-code SQ02 before SQ01 and you will see why in this section. One of the key differences between SQVI and SQ02/SQ01 is that SQ02/SQ01 queries are transportable. In the SQ02 transaction code, we set up the InfoSet and perform the role/usergroup assignment. With SQ01, we set up the field selection for input and output, do variant determination, and add titles and notes.
Let’s look at this use case: the procurement team requested a complete lifecycle dashboard for PR to PO. The dashboard should contain dates, approvals, and GRs. And it can be in the form of a report.
You can develop this dashboard through T-codes SQ02/SQ01. We always start with SQ02 and then move onto SQ01. That is why I refer to this query as SQ02/SQ01 and not SQ01/SQ02.
To get started, let’s go to transaction code SQ02 and create InfoSet “ZPRPODASH.”
Click on the join option and add tables EKKO, LFA1, T001, EKPO, EKKN & EBAN. Modify the joins as shown in the figure below.
Next, we’ll create field groups and add data fields per the requirement.
For our use case, we are not doing an alias table or extras. So, we can just hit the generate option, or press Shift + F6.
You’ll once again see a log of warning messages. Validate them and hit the green checkmark.
The system will prompt you to create a workbench request or transport, as shown below.
Create the transport and save.
Next, select your InfoSet and click on the role/user group assignment button.
For this use case, we will assign ZPRPODASH to user group ZO2C.
Hit save and exit the transaction.
Next, go to transaction code SQ01, click on the other user group button (or hit Shift + F7), and find your user group (in our case ZO2C).
Type in the query name; we would suggest to use the same name as the InfoSet. We’ll enter ZPRPODASH and click on the create button.
Match query PRPODASH to the InfoSet PRPODASH (created earlier with SQ02) and click the green checkmark.
Next, go into change mode for the query and modify any things that need updating per your unique requirement. In this case, we will modify title and field names, and we will sort the output based on purchasing documents. We will also choose the fields we want to show in the selection and output.
After entering change mode for our query, the first page gives us the flexibility to modify the title:
Next, click on the basic list option and modify the field labels as needed. Add purchasing document to the sort field bucket as shown below.
In the data fields section, choose fields for selection and list (output).
Once done, hit save and go back to the home page for SQ01.
On the home page, select the query and click on execute, or hit F8.
Enter the selection and click execute.
It works like a charm!
Z Transaction Codes
For the report we just created, you shouldn’t give end users access to T-codes SQ02 and SQ01. It is not a good user experience to have your business folks play around with these technical transactions. So, for a complete end-user experience, you should create a report in SQ02/SQ01 and then associate a Z transaction code (custom code) with it. SAP allows you to create these Z transaction codes, which can be used like any other standard transaction code.
To do this, first go to T-code SQ01 and follow this path: Query > More Functions > Generate Program.
Copy the program name. Next, go to T-code SE93. Enter a unique transaction code starting with Z, enter a name in the short text field, select the radio button for “program and selection screen (report transaction).”
Enter the program name you copied from SQ01 and fill out the classification fields as shown in this figure.
Hit save. The system will prompt you to enter the package name which you can get from your ABAP or Basis person. Save the changes to a transport and ta da! Your z transaction code is ready—in this case, we named it ZPRPO.
Type ZPRPO into your command prompt, hit enter, and you will be directed to your report POPODASH.
Not too hard, am I right?
Conclusion
When working as an SAP consultant, you’ll need to work with queries. This post talked about the transaction codes SQVI, SQ01, and SQ02, which can be used to create such queries
Comments