Creating reports using SAP Query
The SAP ERP system contains many reports with a huge number of fields. To customize the display of the required fields, there is a mechanism that allows you to hide unnecessary fields, change the order, sort, summarize and summarize. In addition, the system allows you to store several options and quickly select the desired settings.
Over time, the end user will need reports that contain both standard fields and additional fields. Examples include such reports as the history of the ledger account, open invoices from suppliers, customer list.
If the reports are quite specific or have some non-standard functionality, you have to contact the programmers to implement them. In other cases, it is enough to use SAP Query.
')
The SAP Query tool allows you to create reports without significant effort.
Below is an example of creating a report using SAP Query.
Basic steps to create a report:
From time to time there are situations in which the user can not figure out the causes of the error "The contract value has been exceeded" in purchase orders. In most cases, the cause of this error is easy to find.
But if the contract has 1000 positions, and for some of them the cost in the receipt is greater than the cost in the contract position (the cost in the contract is equal to the cost in orders), then it becomes problematic to identify this position.
The system has reports showing the value of the order items to the contract item or receipt documents with reference to the purchase order. There is no single report.
You must create a report that displays the specified contracts for positions. Each position should display its own cost, the cost of items on purchase orders, the cost of receipts, the cost of invoices. All costs must be displayed in local currency.
It is also necessary to add a sign that allows you to display only problem positions.
The info set should contain a list of fields that you want to display in the report. In our set, we will create fields such as the contract number, the contract position, the cost of the position in the contract, the cost in the purchase orders, the cost of receipts, the cost of invoices.
Start transaction SQ02.
SAP offers several sources for creating an info-set, these are:
Each of the data sources has its own characteristics, which are not discussed in this article. We chose a table, since the entire report will be built on this table, and we will get the rest of the data through setting up additional fields.
Further we mark the item “Include key fields” and confirm the creation.
Now we are on the first screen of the info-set.
The screen is divided into 3 parts:
If the same item with key fields is selected on the previous screen, then the purchasing document number and position should be displayed in the field group. If not, turn on these fields (right-click on the field from the table and select the item “include the field in the group of fields”), or exclude unnecessary ones.
Then you should add the remaining fields required for our report. To do this, click the button on the "Additional Information" toolbar (F5). To switch to the initial position, press the “Field Groups” button (F6).
The right side is completely transformed. The first screen contains additional fields, tables, structures and links to the code.
Create the required fields. To do this, click on the "Create" button (the button in the form of a white sheet). In the window that opens, specify the name of the new field and select the item "Additional field"
Then you should fill in the name, the text of the title in the report and a link to the sample field “EKPO-BRTWR”. The format of our field will be copied from the sample.
The following field is created similarly, with the exception of the sequence number of the field.
The cost of receipts and the cost of invoices will be taken from the purchase order history from the EKBE table.
Added fields are displayed in the left box in the "additional fields" branch.
These fields should be added to the field group. To do this, switch to the field group mode (the “Field group” button) and right-click on the item “Add to the field group”.
Then we return back to the mode of additional information.
We define the rules for the formation of the value of the contract position.
To do this, set the mouse cursor to the desired position and click the "Encoding for additions" button.
Let's set the following code for the “BRTWR” field:
We determine the variable for the exchange rate in which the contract is conducted. Then we get its value.
If the positions of the contract are not deleted, then the cost is equal to the product of the quantity and price, taking into account the quantity of the unit price and the exchange rate. If the contract is in accounting currency, then the system is automatically equal to 1.
If the position is deleted, the cost is 0.
The code for the field "ORDER_BRTWR":
Everything is similar, taking into account the fact that there may be several orders for one contract item.
The code for the "DMBTR" field:
For receipt documents, we immediately take into account the cost in local currency. For this reason, there is no need to recalculate the currency. In the receipt documents there is no sign of deletion, since for accounting entries only reversal is possible. In this regard, the field “EKBE-SHKZG” defines a direct document or a reversing one.
The code for the "F_DMBTR" field:
The invoice amount is calculated in the same way as the receipt. In this case, for invoices should consider such operations as ext. debit and add. crediting.
Next, go to the tab "Selection Criteria". There we will create the field “Contract number” and the sign “only positions with excess”.
When creating, you should take into account that the “Selection Criterion” is the field on which the restriction is fulfilled, and the “Parameter” is a certain feature that should be assigned a certain functionality.
Create a selection criterion "Contract Number".
and the parameter "Only positions with excess"
Please note that the parameter in the “Additional Data” field contains the text “AS CHECKBOX” in order to be displayed as a check box.
For the selection criteria "Contract Number", there are no additional settings. The report will be limited to the field specified in the settings. For the parameter, we will make restrictions in the generated report. To do this, go to the "Encodings" tab and select a fragment of the encoding "5 Processing Records."
and adding code:
With this code we restrict the output of only those records for which BRTWR <DMBTR, i.e. The cost of the contract position is less than the value of the receipts We also restrict entries by type "Contract".
Please note that at the time of verification, these fields must be calculated. It is for this reason that for this test the sequential number was set equal to 10 (any number that exceeds the numbers of the previous fields).
Now save the info set and generate it.
.
User groups provide access control to info sets and queries.
Go to the “Environment” -> “User Groups” menu from the first screen of the info-set maintenance.
The request is the next step to generate a report after creating the info-set and user group. At this step, the final composition of the fields to be included in the report, the order of the fields, the appearance of the report, sorting, totals, and so on are determined.
We start transaction SQ01.
We check that we work with the user group created earlier. (see group name at the top of the screen). If not, press the button “Other user group” (Shift + F7) and select the desired group.
Enter the name "Z_SUPPLY" and click the "Create" button. The system will display a list of info-sets associated with this user group. Select our info-set and confirm the entry.
The format of the list should be compared with the existing print format, otherwise the report cannot be printed. Common formats: 65x132 and 65x80. Leave the default format: 0 rows (no limit) and 83 columns.
We call the report “Admission to contracts” and proceed directly to the “drawing” of the report by clicking the “Main list” button.
On the main list screen in the left part there are fields that can be added to the report. The bottom left shows the properties of the selected item, and the main part is a graphical representation of the future report.
Open the list of fields on the left and check the fields that should be included in the report. Fields are added in the order in which we mark them. In the future, the order can be changed by dragging the field to the desired place in the graphic block.
We make minor adjustments. Cost fields are displayed with add. field "Currency" whose value we do not need. To remove the currency display, select the value field and select the item "Without a currency field" in the lower left of the screen. Then click “Apply”.
On this our report can be considered formed. Save the report.
Here you can test it by clicking the "Test" button.
After ensuring that the report fully meets our needs, a transaction should be created to run it.
Now you can generate a report using a transaction.
Over time, the end user will need reports that contain both standard fields and additional fields. Examples include such reports as the history of the ledger account, open invoices from suppliers, customer list.
If the reports are quite specific or have some non-standard functionality, you have to contact the programmers to implement them. In other cases, it is enough to use SAP Query.
')
The SAP Query tool allows you to create reports without significant effort.
Below is an example of creating a report using SAP Query.
Basic steps to create a report:
- Creating an info set
- Creating a user group, assigning it an info-set
- Creating a query based on the info-set.
Formulation of the problem
From time to time there are situations in which the user can not figure out the causes of the error "The contract value has been exceeded" in purchase orders. In most cases, the cause of this error is easy to find.
But if the contract has 1000 positions, and for some of them the cost in the receipt is greater than the cost in the contract position (the cost in the contract is equal to the cost in orders), then it becomes problematic to identify this position.
The system has reports showing the value of the order items to the contract item or receipt documents with reference to the purchase order. There is no single report.
You must create a report that displays the specified contracts for positions. Each position should display its own cost, the cost of items on purchase orders, the cost of receipts, the cost of invoices. All costs must be displayed in local currency.
It is also necessary to add a sign that allows you to display only problem positions.
Creating an info set
The info set should contain a list of fields that you want to display in the report. In our set, we will create fields such as the contract number, the contract position, the cost of the position in the contract, the cost in the purchase orders, the cost of receipts, the cost of invoices.
Start transaction SQ02.
- Enter the name of the InfoSet “Z_SUPPLY_TO_CONTRACT” and click the “Create” button
- Define the description of the “Admission to Contracts” info-set
- Enter the name of the EKPO table (see Figure 2). This is a standard table with items of purchasing documents, including contracts.
SAP offers several sources for creating an info-set, these are:
- join of tables
- read table directly
- logical database
- data collection program
Each of the data sources has its own characteristics, which are not discussed in this article. We chose a table, since the entire report will be built on this table, and we will get the rest of the data through setting up additional fields.
Further we mark the item “Include key fields” and confirm the creation.
Now we are on the first screen of the info-set.
The screen is divided into 3 parts:
- On the left - tables and additional fields
- Top right - a group of fields that should be in the query and, further, in the report
- Lower right - detailed information on the fields from the group of fields. To display, double-click on the required field from the field group.
If the same item with key fields is selected on the previous screen, then the purchasing document number and position should be displayed in the field group. If not, turn on these fields (right-click on the field from the table and select the item “include the field in the group of fields”), or exclude unnecessary ones.
Then you should add the remaining fields required for our report. To do this, click the button on the "Additional Information" toolbar (F5). To switch to the initial position, press the “Field Groups” button (F6).
The right side is completely transformed. The first screen contains additional fields, tables, structures and links to the code.
Create the required fields. To do this, click on the "Create" button (the button in the form of a white sheet). In the window that opens, specify the name of the new field and select the item "Additional field"
Then you should fill in the name, the text of the title in the report and a link to the sample field “EKPO-BRTWR”. The format of our field will be copied from the sample.
The following field is created similarly, with the exception of the sequence number of the field.
The cost of receipts and the cost of invoices will be taken from the purchase order history from the EKBE table.
Added fields are displayed in the left box in the "additional fields" branch.
These fields should be added to the field group. To do this, switch to the field group mode (the “Field group” button) and right-click on the item “Add to the field group”.
Then we return back to the mode of additional information.
We define the rules for the formation of the value of the contract position.
To do this, set the mouse cursor to the desired position and click the "Encoding for additions" button.
Let's set the following code for the “BRTWR” field:
data: ii_WKURS like EKKO-WKURS. SELECT SINGLE WKURS INTO ii_WKURS FROM EKKO WHERE EBELN = EKPO-EBELN. CLEAR BRTWR. IF EKPO-LOEKZ IS INITIAL. BRTWR = EKPO-NETPR / EKPO-PEINH * EKPO-KTMNG * ii_WKURS. ELSE. BRTWR = '0'. ENDIF. We determine the variable for the exchange rate in which the contract is conducted. Then we get its value.
If the positions of the contract are not deleted, then the cost is equal to the product of the quantity and price, taking into account the quantity of the unit price and the exchange rate. If the contract is in accounting currency, then the system is automatically equal to 1.
If the position is deleted, the cost is 0.
The code for the field "ORDER_BRTWR":
data: s_NETPR like EKPO-NETPR, s_PEINH like EKPO-PEINH, s_MENGE like EKPO-MENGE, c_BRTWR like ORDER_BRTWR, i_ebeln like EKKO-EBELN, i_WKURS like EKKO-WKURS. ORDER_BRTWR = '0'. select NETPR PEINH MENGE EBELN INTO (s_NETPR, s_PEINH, s_MENGE, i_ebeln) from EKPO as e where e~KONNR = EKPO-EBELN AND e~KTPNR = EKPO-EBELP AND e~LOEKZ = ''. SELECT SINGLE WKURS INTO i_WKURS FROM EKKO WHERE EBELN = i_ebeln. CLEAR c_BRTWR. c_BRTWR = s_NETPR / s_PEINH * s_MENGE * i_WKURS. ORDER_BRTWR = ORDER_BRTWR + c_BRTWR. endselect. Everything is similar, taking into account the fact that there may be several orders for one contract item.
The code for the "DMBTR" field:
data: s_SHKZG like ekbe-SHKZG, s_DMBTR like ekbe-DMBTR. DMBTR = '0'. select DMBTR SHKZG INTO (s_DMBTR, s_SHKZG) from EKBE as e INNER JOIN EKPO as z ON e~EBELN EQ z~EBELN AND e~EBELP EQ z~EBELP where z~KONNR = EKPO-EBELN AND z~KTPNR = EKPO-EBELP AND e~VGABE = '1'. if s_SHKZG = 'S'. DMBTR = DMBTR + s_DMBTR. else. DMBTR = DMBTR - s_DMBTR. endif. endselect. For receipt documents, we immediately take into account the cost in local currency. For this reason, there is no need to recalculate the currency. In the receipt documents there is no sign of deletion, since for accounting entries only reversal is possible. In this regard, the field “EKBE-SHKZG” defines a direct document or a reversing one.
The code for the "F_DMBTR" field:
data: s_SHKZG_f like ekbe-SHKZG, s_DMBTR_f like ekbe-DMBTR. F_DMBTR = '0'. select DMBTR SHKZG INTO (s_DMBTR_f, s_SHKZG_f) from EKBE as e INNER JOIN EKPO as z ON e~EBELN EQ z~EBELN AND e~EBELP EQ z~EBELP where z~KONNR = EKPO-EBELN AND z~KTPNR = EKPO-EBELP AND ( e~VGABE = '2' OR e~VGABE = '3' ). if s_SHKZG_f = 'S'. F_DMBTR = F_DMBTR + s_DMBTR_f. else. F_DMBTR = F_DMBTR - s_DMBTR_f. endif. endselect. The invoice amount is calculated in the same way as the receipt. In this case, for invoices should consider such operations as ext. debit and add. crediting.
Next, go to the tab "Selection Criteria". There we will create the field “Contract number” and the sign “only positions with excess”.
When creating, you should take into account that the “Selection Criterion” is the field on which the restriction is fulfilled, and the “Parameter” is a certain feature that should be assigned a certain functionality.
Create a selection criterion "Contract Number".
and the parameter "Only positions with excess"
Please note that the parameter in the “Additional Data” field contains the text “AS CHECKBOX” in order to be displayed as a check box.
For the selection criteria "Contract Number", there are no additional settings. The report will be limited to the field specified in the settings. For the parameter, we will make restrictions in the generated report. To do this, go to the "Encodings" tab and select a fragment of the encoding "5 Processing Records."
and adding code:
CHECK EKPO-BSTYP = 'K'. IF Z_ONLY = 'X'. CHECK BRTWR < DMBTR. ENDIF. With this code we restrict the output of only those records for which BRTWR <DMBTR, i.e. The cost of the contract position is less than the value of the receipts We also restrict entries by type "Contract".
Please note that at the time of verification, these fields must be calculated. It is for this reason that for this test the sequential number was set equal to 10 (any number that exceeds the numbers of the previous fields).
Now save the info set and generate it.
.Create user group
User groups provide access control to info sets and queries.
Go to the “Environment” -> “User Groups” menu from the first screen of the info-set maintenance.
- Enter the name of the user group "ZMM"
- Push the button "Create"
- We define the name "MM Reports"
- Click the "Save" button. At this point, the system may request a developer key or transfer request.
- Click the button "Assign users and info-sets"
- In the window that opens, enter the users for whom this report is intended.
- Save the changes
- Click the button "Assign Info-kits"
- Scroll down the list to find custom info-sets and mark the previously created info-set “Z_SUPPLY_TO_CONTRACT”
- Save
Create request
The request is the next step to generate a report after creating the info-set and user group. At this step, the final composition of the fields to be included in the report, the order of the fields, the appearance of the report, sorting, totals, and so on are determined.
We start transaction SQ01.
We check that we work with the user group created earlier. (see group name at the top of the screen). If not, press the button “Other user group” (Shift + F7) and select the desired group.
Enter the name "Z_SUPPLY" and click the "Create" button. The system will display a list of info-sets associated with this user group. Select our info-set and confirm the entry.
The format of the list should be compared with the existing print format, otherwise the report cannot be printed. Common formats: 65x132 and 65x80. Leave the default format: 0 rows (no limit) and 83 columns.
We call the report “Admission to contracts” and proceed directly to the “drawing” of the report by clicking the “Main list” button.
On the main list screen in the left part there are fields that can be added to the report. The bottom left shows the properties of the selected item, and the main part is a graphical representation of the future report.
Open the list of fields on the left and check the fields that should be included in the report. Fields are added in the order in which we mark them. In the future, the order can be changed by dragging the field to the desired place in the graphic block.
We make minor adjustments. Cost fields are displayed with add. field "Currency" whose value we do not need. To remove the currency display, select the value field and select the item "Without a currency field" in the lower left of the screen. Then click “Apply”.
On this our report can be considered formed. Save the report.
Here you can test it by clicking the "Test" button.
Creating a transaction for a report
After ensuring that the report fully meets our needs, a transaction should be created to run it.
- Initially generate the program. To do this, go to the first screen of transaction SQ01. In this transaction, select the request we created and select in the menu “Request” -> “Other functions” -> “Generate program”.
- After the program is generated, we get the program name in the menu “Request” -> “Other functions” -> “View report name”.
- We form a transaction through SE93.
- Specify the name of the transaction
- Push the button "Create"
- Specify the name of the transaction
- Mark the item "Program and selection screen"
- Confirm input
- In the screen that opens, in the “Program” field, enter the previously received report name
- Note GUI-ability
- Save
Now you can generate a report using a transaction.
PS
- On some screens, it may appear in the “Change” header instead of “Create.” This is due to the fact that the report was created earlier.
- The code above is hardly the most optimal. If you think your option is better, ready to consider it and replace
- There are several options for generating this report in SAP Query. This option is not optimal. But he reveals the capabilities of SAP Query
- I am always glad to constructive criticism
Source: https://habr.com/ru/post/218581/
All Articles