Creating ABAP Queries
Okay, now we’ll see an important topic, Creating ABAP Queries.
Thank you for reading this post, don't forget to subscribe!
This is ABAP queries I use to create custom reports. If the users want to create some custom reports, most of the reports are available here. If the users want to create some customer reports, which is not available in the standard reports, then they can use this function, ABAP queries to create the custom reports according to their own taste. Okay, you’ll see how to do that. The menu path to create a custom reports using ABAP queries is over here. ABAP workbench, development, utilities, and subquery. It’s under tools, ABAP workbench, development, utilities, subquery queries, InfoSets.
Before you create a SAP query, you need to create the InfoSet, and the InfoSet needs to be assigned to the SAP query. Something like this to create an InfoSet first. InfoSet has to be to be created, then the InfoSet needs to be assigned to a user group, then from the user group, the InfoSet should be assigned to the subquery. Just save this.
Okay, let’s do the InfoSet now. Here, SQ02, we can use InfoSet. You have to give a name, right? So we give a name called Sun, and create. We give a name, Sun Query. Okay. The report that we are going to create is that we are going to display all the invoices for customer. We are going create a report which will display all the invoices that is posted to a customer, and we are going to make sure the report can be selected based on the customer and the company code.
Okay, for that let me just show you what is the report I am going to create. Okay, this is the report that I am going to create. I need a selection fields in the report selection screen. I want to select by customer number and company code and the report will display the respective customer numbers, document number, document date and document amount. Okay, the data will be displayed here, in this area here.
Let’s do it now. In order to create this report, you need to know the tables in which this information will be stored. You can get the information about the tables from the basis guys. You just tell them which table you can find the customer number and company code, and which table you can find the documents that are posted to a customer. They will know it, or you can also ask your FICO consultant. If you are a FICO consultant, then you need to know about it. Alright, but now, I can tell you that this customer number and customer code, company code comes from the table called KNB1, so I’m going to enter at least one table here. You need to enter at least one table here to get started, so I’m going to enter this table here. There’s one more table, this KNB1 is the table from which you can extract this information, and then, you need another table where you can get this document information. That table is over here. It’s called BSID. So you choose insert table, you enter BSID. This is the table where this information is stored. Document number, document amount and so forth. Once you select SAP, it will automatically assign the link between the two tables.
The link between the two tables is customer number and company code. See this line here, it’s automatically assigned. You don’t need to do anything about it, alright. Just go back. Just choose this create empty field groups. Click OK. Alright, you see the field groups here.
These are the tables. And then, if you want to see the fields under the table, you just expand this and you will see all the fields under this table. You’re going to select two fields from this table. It’s a KNB1 I selected, right? The KNB field. KNB table. This is the technical field name, and this a description. This is the actual field name in the table, okay? Select and drop it here, and the company code, select and drop it here.
So we come here, we have company code and customer number which is identical to this.
Now, to select this information here, we need to come here, open up the BSID table, we need to find document date, right? Document date is here. Select and drop it, then you need the document number, accounting document, and then this document amount. Document amount should be this one, amount in document currency. Select and drop it. Drag and drop. So you have selected all the fields that is required for this report. Okay, now save it. When you save you to select this local object, select the local object saved. Now go back. When you press the back button, the system ask whether you want to generate the InfoSet? Click yes.
It has to be generated, otherwise it won’t be available for reporting. Then, see, this thing is created, the sun query is created. You need to assign the user group, right? The next thing you need to do is this. You need to assign the InfoSet to the user group. So this is still the InfoSet which is not assigned to any group.
Select the user group assignment, go down to this group, test query. This is the test group that we have. Just click this and save it. Assignment has been saved.
Now we can close this screen, come out. Now we can go to queries, so we come here, queries, sq01, we select the InfoSet query, see, our user group is test query, right? Test query, under the user group test query, you can find this InfoSet that we created which is Sun. You have many user groups, make sure you select the user group in which you have assigned the InfoSet. Select it, okay, now you have customer code. This check box means that if you select this, then this field will be available in the report selection screen. So we need these two, right? We need these two fees in the report selection, therefore, you need to check these two. You can also select accounting document if you want to, so it’ll appear in the report selection screen, but I don’t want to do that.
So output meaning that you want to display this feld in the report display. I don’t display the company code. I want to display the customer number, this and this.
1, 2, 3, 4. See all are displayed here. Customer number, amount, or I don’t want to display the amount, right? If you want to change the order, this the way you change it. You have to select in the order you want to display. Document date comes second, right, so select document date second. Document number is third, so select document number. Document amount is fourth, select document amount. Okay. The currency is automatically displayed.
Alright, so you have this section which corresponds to report display, and you have this section which is a report selection field. Section corresponds to this. Now you can save it. You have to give a name to save it. Just give, Sun Query. Sun Query. Saved.
Now we can run it. Go back. I go to Sun Query here, just run it. Here we go. So you can select the output format here. I recommend ABAP list. The format should be ABAP list so that you can have many options to filter the report.
You can leave this blank to select all the customers. See, you have all the customers. Just choose list, above list view. Alright. See this. You have customer number.
I need to show you the report selection screen. This is the report selection screen. Company code and customer number corresponds to this. Alright. And this one, you execute, you have customer number, document date, document number, and document amount all displayed here. Very easy. Okay, now that’s about it.
