While working on different user requirements, we often feel the need to see how data will look like if we extract data using multiple tables. Normally what we do, we extract data from one table using Transaction SE16 or SE11 and use that data to extract data from the next table to check what will be the output. What a pain…..
SAP has provided tools to ease out this problem. Excited to hear this!!! I too was when I came to know about it for the first time.
There are two options:
Creating a report using transactions SQ01, SQ02 AND SQ03.
Ø SQ02: To Create the Infoset to select the data from.
Ø SQ03: To create and assign user group to the Infoset.
Ø SQ01: To create a query to display the data extracted from the Infoset as a report.
Seems like a lot of effort. Isn’t it!!! But actually it is very simple. Don’t worry SAP has provided the second option which is even easier then the above one and is…
Creating a quick report using Transaction SQVI
In this blog, we will focus on Transaction SQVI.
Functionality of SQVI: It’s a Quick Viewer and is used to generate a quick report combining multiple tables and selection criteria. It combines the functionality of Transaction SQ01 & SQ02.
Let’s begin with how to create quick reports using SQVI.
I will take the example of Purchase Info record data to be shown in report from table EINE & EINA.
- Open Transaction SQVI. You will see a screen as shown below. Enter the name of the report “ZPIR_DATA” and press Create.
You have the options to display or change the already existing reports as well
as shown above.
- On pressing the create button a pop will appear as shown below.
Enter the Title to the Report and select the Data source. Data source can be anyone of the following:
- Logical Database: You need to specify the Logical database name.
- Sap Query Infoset: You need to specify the Infoset name created using transaction SQ02.
- Table: You need to enter
- Table Join
Select the mode you want to see the screen: You can anytime navigate between basic and layout mode, so nothing to think while selecting the mode.
Here I will take the Case of Table join as we generally need to link multiple table to generate a quick report.
Select the basic mode and then press the green ‘Yes’ symbol and you will see a screen as shown below.
On the application bar you can see the options to Insert Table, Join conditions, Delete etc.
- Click on the Insert Table Icon and you will see a pop up to enter the table name as shown.
Enter EINA as the table name and press enter. Again click on the insert table icon and this time enter EINE. You will see a screen as shown below. You can see the system automatically created the join condition based on the matching Key fields.
Make sure that the join condition is correct as sometimes system creates the wrong links based on the nearly matching fields. You can delete the link by right clicking on the link and selecting ‘Delete Link’. You can create links as well using the Join conditions icon.
Press the back Button and you will see the screen , as shown below, to select the Fields:
Ø List Fields: Fields to be displayed in on the output.
Ø Sort Order: Fields to display data in the required sort order.
Ø Selection Fields: Fields to be displayed on Selection Screen.
Select the Required Fields either by expanding the tree on the left window or by selecting the fields from the right window of available fields into different tabs.
Say, I want to display Material, Vendor, Plant, Purchase Org, Purchase Info record Number.
So I will list these fields under “List Field selection” Tab as shown:
I want to select only for Plant ‘US07′ i.e. I want plant as my selection field.
And you are done with the report creation. Press the Execute button on top. Enter the plant in the Selection screen appeared and you will get the desired Output on the screen as shown.
So this is how you can quickly link multiple tables to see the result and can also take the dump of the same in the local file.
Hope this will be helpful for the beginners!!!