How to Create Reports by joining various tables in SAP
I am working in to SAP MM- SRM Support services. Majority of what I do consists of reporting of various things according to user requirement. And most of what I do is to get certain data from one table pass it onto others and get some other data and join them into excel and present it to client.
For few weeks i use to do it Manually and didn’t know that SAP has anything/program to help me get of exactly what I want.
Because most the system is customized and even for standard report and analysis was not producing the data my client required for further analysis.
So after few research here and there, reading many new functionalities and even understanding the creation of BAPIs for certain data creation and background jobs, but I was not satisfied as the client requirement would vary a lot on within few set of filters and stuff and I, being a Functional consultant side, didn’t know on how to create BAPIs and it was also way smaller job so as to create a BAPI for the same.
But in all this mess of a research I came across the SAP Functionality of query Creation SQVI, It was exactly what i was looking for !!
It was simple to understand and easy to work with. Just few mins into the functionality and I know I have found my cancer’s cure.
So for all those who are still suffering here is all you need to know for all the table related reports you were generating.
I would like to explain it with an example so for our better understanding.
My Client came up with the requirement that, they want a list of Shopping cart from the given list of PO in SAP SRM
MANUAL/PAST METHOD :
Into SE16 Transaction
PART 1: Insert PO data into table BBP_PDBEI
PART 2: Get ‘Object GUID’ from the above table and insert it in the table CRMD_ORDERADM_I
PART 3: Get ‘(Parent) Object GUID from the above table and insert it in the table CRMD_ORDERADM_H and get the list of required SC
PART 4: We have to combine all the sheets so as to know which Shopping Cart is for which Purchase Order, using VLOOKUP Functionality in Microsoft Excel.
Now instead we can use Transaction SQVI and proceed as follows:
1. Open the screen and in the “Quick View” Box, Enter the name of your query, can be anything, let say ” PO_LIST_FROM_SC”
2. Click Create.
3. Choose a title. ( can be same PO_LIST_FRM_SC ). NOW CHOOSE “TABLE JOIN” in the data source, as we want to join few tables to get our report. and Basic or Layout mode choose any,
4, Now a blank screen will open with the title ” Create QuickView ( your name of query, in our case PO_LIST_FRM_SC) : Choose data source
5. As you can see few icons on the top of the page, Click on the second Icon of “Insert Table”. and insert the table you want to join, in our case ,
Repeat the process and insert the tables “CRMD_ORDERADM_I” and “CRMD_ORDERADM_H” as well.
All the tables can be seen as a box in the screen with their titles
6. As you can see there is small link( thin black line) between the tables. This line is how the system would match the data between tables and would
create a common table. see that there are no two lines in our case because we just want to match the list of ‘Object GUID’ into first two tables
“BBP_PDBEI” and “CRMD_ORDERADM_I”.
If there are 2 lines then we can simply click one of them and right click and use “delete link” to delete the link. we can also pick and drop the link on the
fields that we like to match between tables.
7. Now do the same between the tables “CRMD_ORDERADM_I” and “CRMD_ORDERADM_H” and join the “(Parent) Object GUID” from the first table to
“Object GUID” in the second.
8. You can also click the first Icon on the menu bar and can check the link joined between all the tables are defined correctly or not.
You can see the tables joined in the navigation view in the lower center of the screen and can also see the area covered by our screen.
9. Now go press the back button and come on the initial screen. This screen is mainly divided into two parts, but basically both of them would perform the
same thing to our table. i.e. that it would help us with the field that we want to be on display and the we fields we want to hide.
As you can see on the LEFT hand side of the screen, the names of our tables listed , drop down them (double click) and choose the view that you want to
be it ‘Listed’ in the result and fields that are present to enter the data or selection fields.
In our case the listed fields would be Shopping Cart numbers and Purchase order numbers
And selection field would be Purchase order numbers.( we can use as many fields as we want from all the listed tables)
10. Execute and you would reach a new program. This is what we require.
Now insert the PO numbers and execute and you would get List of shopping cart related to it.
WE have to create only once this query and every one can use it from the ‘SA38’ – Program executor.
Please Note: For others to use the program, you need to write down the name of the program when you in step 10, in the above process and share it manually with everyone in the team for them to use it.
So this was a simplest example that I can come up with for using this extraordinary function ” SQVI”. You can join various tables and other data sources given in the option and explore this option.
It worked for me as a life saver from daily reporting of various things.
I hope it does the same for you.
Please share your thoughts and experiences on the using this function.
PS: Remember this, as only the user who have created the query can edit it into the system , no one else can. And also you can always use the same transaction ‘SQVI’ to make any changes in the queries you have created.