You can define the RuleID, if you do not define an ID, the system generates one automatically. As Data Source Alias, you have to enter the data source alias, e.g. DS_1. As Position you can choose between Before, After, BelowHeader or BesideHeader. For PositionBy you can enter one of the following elements:
A new feature of Analysis Office 2.3 is a macro called SAPOpenWorkbook. Some of you maybe know the old sapbex.xla!SAPBExReadWorkbook or the BExAnalyzer.xla!runWorkbook command.
The user guide explanation for this macro is:
You can use this API method to open an Analysis workbook. The workbook that should be opened can be stored on a SAP NetWeaver server or on a BI platform.
The workbook is opened in the same Microsoft Excel instance. After the command execution, the opened workbook is active.
As you know the sapbex.xla!SAPBExReadWorkbook respectively BExAnalyzer.xla!runWorkbook command needed either an established connection from the addin or you created your own connection with the following code:
The most important thing when you are working with VBA in Analysis Office, is the refresh of the DataSource(s). Otherwise nothing works. Once a connection to the Business Warehouse is established, each DataSource needs to be refreshed.
If you have only one DataSource, that is the source code:
If you have more than one DataSource, this is the source code:
So that you know if the refresh was successful, you should use the following source code:
Therefore you can check lResult to 1 or 0. If you refresh the DataSource(s) each time, a lot of time is wasted. To counteract this, there is the following source code:
This command checks if the DataSource is already active. Here an example source code:
After this you can excute your own code.
So far is nothing in the help file, but it is called in the SCN Forum under "Whats new in Analysis Office 2.1".
The command is:
In Excel you can quickly create very complex formulas. If you want to simplify your formula, you can also write your own function in VBA. Here is an example for calculating the percentage variance from the previous year.
As you can see, a very simple formula. For this purpose the sample as an Excel formula.
=IFERROR(IF(targetCell < 0,(sourceCell - targetCell) / - targetCell,(sourceCell - targetCell / targetCell), 0)
= (ACT) 215 - (PY) 204 / (PY) 204 = 0,055 = 5,5 %
As you can see the own formula is more readable and can be easily combined with another formula.
There is an easy way to delete the contents of an array. Is possible by the command Erase
If you want to import source code from a text file, you can use the following code.
If you have created your own SAP function module, you can use this with the following VBA code.
Dim MyFunc As Object
Dim E_INSERTED As Object
Dim E_MODIFIED As Object
Dim DATA As Object
Set MyFunc = R3.Add("Z_RSDRI_UPDATE_LCP") 'FunctionModule Name in SAP BW
Set E_INSERTED = MyFunc.imports("E_INSERTED") 'InsertFunction in SAP BW
Set E_MODIFIED = MyFunc.imports("E_MODIFIED") 'ModifyFunction in SAP BW
Set DATA = MyFunc.tables("I_T_DATA") 'Table to store data and write to BW.
DATA.Rows.Add 'add new data
rowDATA.Value(1, 1) = Sheet1.Cells(1, 10).Value 'First Cell of the data table is filled with the value from Sheet1.Cells(1,10)
'Call Insert or Modify
Result = MyFunc.CALL
'Message to the User
If Result = True Then
MsgBox "Insert Rows: " & E_INSERTED.Value & " Modify Rows: " & E_MODIFIED.Value, vbInformation
MsgBox MyFunc.EXCEPTION 'Exception
If you want to create automated reports with data from an SAP ERP or Business Warehouse system, you first need to connect to a SAP system. The connection can be used later to access system tables in the ERP or Business Warehouse or to trigger various other action.