A couple of weeks ago I got a question from a sales analyst asking if it’s possible to automatically send out workbooks. Every morning the sales analyst is waiting for output of multiple workbooks. This consumes a lot of time. I responded that for this purpose SAP has a standard functionality available in the form of the Bex Broadcaster, but that I needed some time to setup the infrastructure and testing it.
If you have already worked with the Bex Broadcaster you probably know that scheduling queries can be used out of the box, without any additional server. However if you want to schedule workbooks you will need a precalculation server (which is actually a Windows computer with Excel and the precalculation service installed). Since the sales analyst is using workbooks with more than 35 queries in it (we are not talking about efficiency here), this additional precalculation server is required. All steps to setup this server are mentioned in this post on SCN.
During the testing phase scheduling a simple workbook worked straight out of the box (that’s a good thing!). Second test was executing the workbook consisting out 35 (!) queries. Believe it or not, this also worked. The only downside of scheduling this workbook, is that the variables which are assigned to the underlying queries were fixed values. This is a disadvantage because the workbook is refreshed every day based on the current calendar day. One option I considered in order to solve this, is to redesign the complete workbook (including the queries). This will take a lot of time, thus I started searching on Google and SCN, resulting in viewing this post. There someone explains how workbook variables can be changed dynamically by using a program he created. My quest was finished! We will use this program to manipulate Bex Broadcaster variables.
Before we can go to the practical part, you have to create an entry in the Bex Broadcaster for the workbook which you want to send out every day. The Bex Broadcaster can be requested via the Bex Analyzer.
Once in the Bex Broadcaster create a new entry for the workbook. Be sure to setup all the options, especially the variable assignment. This option looks for all (non)required fields in the underlying queries. Setup the required variables. Later on we are going to change the variable key date of the query by using a function module.
When this entry is saved it will create an record in table RSRD_SETT_NODE_A. This table is used to store all settings and parameters of the Bex Broadcaster.
The next step is to determine which variable we need to change. As discussed earlier the description of the variable was key date of the query. In order to get the technical name of this variable we need to look at the value PR_VARIABLE_STRING. In the example below (ouput is an Excel output, since BW is limited to a certain amount of characters) we notice that the technical name of this variable is 0P_CSDAT.
Now the real fun begins, manipulating the data. For this we use two programs. The first one ZBW_CHANGE_BROADCAST_VARIABLE lets you change the value of a Broadcaster variable. Here only fixed values are possible, so no dynamic dates. The second program lets users change the value of a variable to a fixed value or a data value, by using an offset. Create these two programs by copy and pasting the code below to your BW system with the use of transaction SE38.
Program ZBW_SET_BROADCAST_VARIABLE requires a couple of parameters which can be used to manipulate the data in table RSRD_SETT_NODE_A.
| Parameter | ||
|---|---|---|
| Technical name | Required | Technical name of the Bex Broadcaster setting |
| Variable name | Required | Variable name which should be altered |
| Fixed value | Optional | Fixed value like USD, EUR or 01012014 |
| Value is date | Optional | Is the fixed value a date notation (X = true) |
| Calculate value based on date | Optional | Use date calculation as value, based on current calendar day |
| Offset | Optional | Offset in number of days |
| Negative offset | Optional | Substract (X) or add offset to current calendar day |
Now, if we execute program ZBW_SET_BROADCAST_VARIABLE by filling in the parameters we are able to change for e.g. a the date variable key date of the query. Refer to the picture below to see which parameters are required to in order to set the value of the variable to the current calender day. You can either use this program manual (by using SE38) or attach it to a daily chain (by using a variant), this can be quit useful if you need to change the variables every day.
