My previous blog series was about Shortcuts (parts 2 and 3 are here) , and dealt with quickly accessing systems, transactions, and applications with a focus on SAP BW.
In this blog series, we will dive into automating tasks within SAP systems, with a focus on BW tasks (although some of this may be relevant for other consultants). This part discusses how to use SAPGUI scripting to fill 2LIS setup tables. If you already know about SAPGUI scripting you can skip the first part and go to the issue overview.
SAP BW has built-in application generators. It has a workbench (and now, an IDE) in which we build data models, a Query Designer to define queries, and so on. However sometimes those are just not enough. The problems arise when development or maintenance processes need to be repeated, a lot, each time with different inputs and results. As BW deals with large amounts of data in wide and varied data structures, this isn't a rare occasion.
When deciding whether to automate a task, Of course cost effectiveness should be taken into consideration - sometimes it will be the non-automated-tortoise that wins over the hare.
But automation is also creative and fulfilling, may yield benefits in the longer run, and at the very least should be considered when encountering the need for a massive amount of manual operations.
We often call upon ABAP consultants for help with those kind of issues, and even without them writing a simple loop in ABAP can work wonders (but on that, in a future part of this series). But Script Recording and Playback is a hidden gem in SAP systems that allows this kind of automation without the need for ABAP, by understanding a simple work process and then applying it for the problem at hand.
Filling the setup table for the first time is often easy. The transactions in which you fill the setup tables provide means to define time or document number intervals. What they do not provide, however, is a way to fill multiple single documents. This missing feature is a cause for many headaches for BW consultants, especially in a scenario in which one is required to fix data received from 2LIS extractors. As those extractors affect central business functions and are often part of complex models, this may happen once in a while.
Rakesh Kumar offers an ABAP way to do this, while Bhaskar Shanthi mentions a hack that works in some of the 2LIS transactions - unfortunately, not in OLI3BW, that is used for purchasing documents.
I would like to present a way to solve this problem using Script recording and playback. For this issue, it has the advantage of simple deployment in any system, and it is a good, relatively safe case for seeing the strengths of scripting.
Scripting in SAP essentially allows you to record the user's action in dialog mode into a VB script. Once you've done that, you can insert a loop into that script and tell it to get the input for each iteration from a line in an excel file. You then run the script and watch the magic happen. In the above issue, that means creating an excel file with the relevant document numbers and telling the script to read from it. Once you've run it you'll have the setup table filled with the necessary documents.
Do note that different systems and GUI versions sometimes treat scripts differently, which means you'll have to do the recording yourself - the recorded part in the following examples may not work for you. That also means you might need to re-record the script for a specific architecture.
As a precaution, I do recommend that you first try this in a test environment before rushing to use this in production. On the other hand, if you made a mistake, the absolute worst that could happen is that you've put some stuff in the setup table that wasn't supposed to be in there, which is not necessarily that bad (of course - you should make sure it isn't, in your case). You should also definitely read this document, specifically parts 7.2 and 8, to get some idea regarding the safety of the procedure.
As preparation, we need to create an excel document containing the document numbers. This doesn't need to be anything more complex than a one sheet document with the document numbers in column A. Do note the file location and the sheet's name, as well as the total number of rows. we will use those later. I usually just change the sheet's name to Sheet1 if it isn't already the default name.
If you want to be able to test the program with a clear setup table, you should first empty the table using LBWG - make sure you know the correct Application number. You should then enter the transaction for the relevant application - whether you know the exact name of the transaction, or using transaction SBIW.
Once you're ready, open the script recording and playback window through the "customize local layout" button. If this is greyed out, ask your BASIS consultant to make sure scripting is turned on for your environment.
You will need to choose the name and location of the script you want to save. Once you've done that, hit record and use the transaction as you normally would if you wanted to load a single document.
After you've filled the details and clicked execute, stop the recording.
Go to the folder in which you've saved the file, and then right click the file and choose edit.
We will now edit it so that the recorded actions are performed in a loop.
To do so, we will insert the following code just before the session.findById commands:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set xcl = Createobject ("Excel.Application")
Set wbk = xcl.Workbooks.Open("c:\Scripts\doclist.xlsx")
Set sht = wbk.Sheets("Sheet1")
For i = 2 to 6345 ' rows required
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Fill in the correct file name and sheet name, and the number of rows after the "to". The best practice here is actually to test this for a small range at first, so in this example I would probably use "2 to 3" at first (I start at 2 here because 1 was the document I used to create the script with).
These lines tell SAPGUI to open the excel document and perform the following actions for each row.
The part after the ' sign is a comment - commenting your scripts is good practice.
At the end of the script, add the following lines:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Next
set sht = Nothing
wbk.close
set wbk = Nothing
xcl.quit
set xcl = Nothing
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
These actions tell SAPGUI to "close the loop" and go to the next iteration. Once the loop is done, SAPGUI will free the resources it used to run the script.
We're not done yet, as we need to tell SAPGUI to use the cell from the file instead of the placeholder document number. This can be done by replacing the document number with the location of the document number in the excel file. In this case, the line that had the document number in it should look something like like this (the part before the equals sign could be different in your script):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
session.findById("wnd[0]/usr/ctxtBELNR_L").text = sht.cells(i,1).Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
It is now the time to run the script. Again, I recommend running first in a test environment for a small number of rows. Note that you first have to exit the excel file if it's open. Also note that running the script locks the excel file for editing, and if the script crashes (and it may crash until you get everything right) - the lock will remain in place, and you'll have to kill the excel session using the task manager.
To run the script, simply go back to the SAPGUI window with the setup table transaction and reopen the scripting window, only this time click the play button and choose the script you've created. If you've done everything correctly, you should be seeing SAPGUI quickly filling the fields on the screen for each row. You can then test to see that the loading went well using RSA3.
That's all for this time. In the next part I will discuss how to use scripting to maintain BW models, and then in the following parts I will go into loading and modeling automation using ABAP.
I would like to thank hadar.morchi who introduced SAPGUI scripting to me.
In this blog series, we will dive into automating tasks within SAP systems, with a focus on BW tasks (although some of this may be relevant for other consultants). This part discusses how to use SAPGUI scripting to fill 2LIS setup tables. If you already know about SAPGUI scripting you can skip the first part and go to the issue overview.
Automation in BW and Scripting in theory
SAP BW has built-in application generators. It has a workbench (and now, an IDE) in which we build data models, a Query Designer to define queries, and so on. However sometimes those are just not enough. The problems arise when development or maintenance processes need to be repeated, a lot, each time with different inputs and results. As BW deals with large amounts of data in wide and varied data structures, this isn't a rare occasion.
When deciding whether to automate a task, Of course cost effectiveness should be taken into consideration - sometimes it will be the non-automated-tortoise that wins over the hare.
But automation is also creative and fulfilling, may yield benefits in the longer run, and at the very least should be considered when encountering the need for a massive amount of manual operations.
We often call upon ABAP consultants for help with those kind of issues, and even without them writing a simple loop in ABAP can work wonders (but on that, in a future part of this series). But Script Recording and Playback is a hidden gem in SAP systems that allows this kind of automation without the need for ABAP, by understanding a simple work process and then applying it for the problem at hand.
Issue Overview
Filling the setup table for the first time is often easy. The transactions in which you fill the setup tables provide means to define time or document number intervals. What they do not provide, however, is a way to fill multiple single documents. This missing feature is a cause for many headaches for BW consultants, especially in a scenario in which one is required to fix data received from 2LIS extractors. As those extractors affect central business functions and are often part of complex models, this may happen once in a while.
Rakesh Kumar offers an ABAP way to do this, while Bhaskar Shanthi mentions a hack that works in some of the 2LIS transactions - unfortunately, not in OLI3BW, that is used for purchasing documents.
I would like to present a way to solve this problem using Script recording and playback. For this issue, it has the advantage of simple deployment in any system, and it is a good, relatively safe case for seeing the strengths of scripting.
Solution In Detail
Scripting in SAP essentially allows you to record the user's action in dialog mode into a VB script. Once you've done that, you can insert a loop into that script and tell it to get the input for each iteration from a line in an excel file. You then run the script and watch the magic happen. In the above issue, that means creating an excel file with the relevant document numbers and telling the script to read from it. Once you've run it you'll have the setup table filled with the necessary documents.
Do note that different systems and GUI versions sometimes treat scripts differently, which means you'll have to do the recording yourself - the recorded part in the following examples may not work for you. That also means you might need to re-record the script for a specific architecture.
As a precaution, I do recommend that you first try this in a test environment before rushing to use this in production. On the other hand, if you made a mistake, the absolute worst that could happen is that you've put some stuff in the setup table that wasn't supposed to be in there, which is not necessarily that bad (of course - you should make sure it isn't, in your case). You should also definitely read this document, specifically parts 7.2 and 8, to get some idea regarding the safety of the procedure.
As preparation, we need to create an excel document containing the document numbers. This doesn't need to be anything more complex than a one sheet document with the document numbers in column A. Do note the file location and the sheet's name, as well as the total number of rows. we will use those later. I usually just change the sheet's name to Sheet1 if it isn't already the default name.
If you want to be able to test the program with a clear setup table, you should first empty the table using LBWG - make sure you know the correct Application number. You should then enter the transaction for the relevant application - whether you know the exact name of the transaction, or using transaction SBIW.
Once you're ready, open the script recording and playback window through the "customize local layout" button. If this is greyed out, ask your BASIS consultant to make sure scripting is turned on for your environment.
You will need to choose the name and location of the script you want to save. Once you've done that, hit record and use the transaction as you normally would if you wanted to load a single document.
After you've filled the details and clicked execute, stop the recording.
Go to the folder in which you've saved the file, and then right click the file and choose edit.
We will now edit it so that the recorded actions are performed in a loop.
To do so, we will insert the following code just before the session.findById commands:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set xcl = Createobject ("Excel.Application")
Set wbk = xcl.Workbooks.Open("c:\Scripts\doclist.xlsx")
Set sht = wbk.Sheets("Sheet1")
For i = 2 to 6345 ' rows required
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Fill in the correct file name and sheet name, and the number of rows after the "to". The best practice here is actually to test this for a small range at first, so in this example I would probably use "2 to 3" at first (I start at 2 here because 1 was the document I used to create the script with).
These lines tell SAPGUI to open the excel document and perform the following actions for each row.
The part after the ' sign is a comment - commenting your scripts is good practice.
At the end of the script, add the following lines:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Next
set sht = Nothing
wbk.close
set wbk = Nothing
xcl.quit
set xcl = Nothing
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
These actions tell SAPGUI to "close the loop" and go to the next iteration. Once the loop is done, SAPGUI will free the resources it used to run the script.
We're not done yet, as we need to tell SAPGUI to use the cell from the file instead of the placeholder document number. This can be done by replacing the document number with the location of the document number in the excel file. In this case, the line that had the document number in it should look something like like this (the part before the equals sign could be different in your script):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
session.findById("wnd[0]/usr/ctxtBELNR_L").text = sht.cells(i,1).Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
It is now the time to run the script. Again, I recommend running first in a test environment for a small number of rows. Note that you first have to exit the excel file if it's open. Also note that running the script locks the excel file for editing, and if the script crashes (and it may crash until you get everything right) - the lock will remain in place, and you'll have to kill the excel session using the task manager.
To run the script, simply go back to the SAPGUI window with the setup table transaction and reopen the scripting window, only this time click the play button and choose the script you've created. If you've done everything correctly, you should be seeing SAPGUI quickly filling the fields on the screen for each row. You can then test to see that the loading went well using RSA3.
That's all for this time. In the next part I will discuss how to use scripting to maintain BW models, and then in the following parts I will go into loading and modeling automation using ABAP.
I would like to thank hadar.morchi who introduced SAPGUI scripting to me.
- SAP Managed Tags:
| User | Count |
|---|---|
| 7 | |
| 5 | |
| 5 | |
| 5 | |
| 5 | |
| 4 | |
| 4 | |
| 4 | |
| 3 | |
| 3 |
