Usage of BExAnalyzer 3.5 Macros and VBA Function:
In 3.5 BExAnalyzer you can use SAPBExSetVariable to set the variable to a query that you would like to see the result once the query is refresh in 3.5 BExAnalyzer
Following macro are typically used to refresh a single Data Provider with in a workbook.
- Run("SAPBEX.xla!SAPBExSetVariables", lRange)
lRange contains the Range address of the Variable Values (Structure RRX_VAR: VNAM, VARTYP, VPARSEL, SIGN, OPT, LOW, LOW_EXT, HIGH, etc...)
- Run("SAPBEX.xla!SAPBEXSetVariable", False, lRange)
The second parameter define if all Data Provider are to refereshed or if only the Data Provider located within the provider Range lRange.
This is the flow of BExAnalyzer 3.5 VBA Function SPABExSetVariable. However, the support of 3.5 BExAnalyzer is over and you may would like to use the function in 7X BExAnalyzer.
Usage of VBA Function SAPBExSetVariable in BExanalyzer 7X
When the workbook is upgraded to BExAnalyzer 7X the macro are automatically adapted to the new VBA Code of 7X BExanalyzer and it will look like
- Run("BEXAnalyzer.xla!SAPBExSetVariable", lRange)
- Run("BEXAnalyzer.xla!SAPBExSetVariable", False, lRange)
And you have also implemented the SAP Note "1849135 : Single Data Provider Refresh Functionality NEW"
Issues in current flow:
However, you may find that this SAPBExSetVariable is not working as required in BExAnalyzer 7X especially in case of
- Executing this macro more than once with different values for variable.
- Current flow also modifies the variable value for query for which you did not request for SAPBExSetVariable.
There are some more issue with the flow of SAPBExSetVariable and refresh.
Improvement / Enhancement in BExAnalyzer 7X for SAPBExSetVariable
SAPBExSetVariable is typically 3.5 BExAnalyzer supported function and this feature can work only if you would have already applied a note “1849135 - BExAnalyzer: Single Data Provider Refresh Functionality NEW”.
This is because 3.5 BExAnalyzer refresh single query at a time, however the 7.x architecture is very different then 3.5 Refresh. Therefore this feature SAPBExSetvariable can only work if the single data provider new and the note 1849135 is implement correctly.
In this note 184915, there is an information mentioned that the “The Flag "Allow Refresh Function for individual Queries" in the Workbook Settings Dialog in the Tab "General" does not need to be set for the macros to work.”
There were some inconsistency in flow of processing the single data provider refresh new and SAPBExSetVariable and so in new supported SAPBExSetVariable this Flag is required and mandatory to be set to make SAPBExSetVariable to work along with the new correction mentioned in the SAP Note 2265155 - VBA Function SAPBExSetVariable not working as required in BExAnalyzer 7X.
Usage of SAPBExSetVariable in BExAnalyzer 7X:
To use SAPBExSetvariable in 7.x you have make following changes in your excel range, as of now the you were only mentioning about the variable and its information as per the range address of the Variable Values (Structure RRX_VAR: VNAM, VARTYP, VPARSEL, SIGN, OPT, LOW, LOW_EXT, HIGH etc.).
The new changes here is you also have to maintain Data provider name as shown below in your excel range.
| | | | | | | |
|---|
| DATA_PROVIDER | | | | | <DATA PROVIDER NAME> | <DATA PROVIDER NAME> |
| VARIABLE NAME1 | 1 | I | I | BT | <VALUE> | <VALUE> |
| VARIABLE NAME2 | 1 | I | I | BT | <VALUE> | <VALUE> |
| VARIABLE NAME3 | 1 | P | I | EQ | <VALUE> | <VALUE> |
So as earlier you were only mentioning about the variable information in your excel sheet cell but now with new changes you also have mentioned the data provider name. As recomendation it will be more earier if you can mentione the dataprovide name first.
Now you also need to do some modification in your VBA Code
BExAnalyzer 7X VBA Macro code example:
strVBA = Run("BExAnalyzer.xla!SapBEXsetVariables", Sheets("<Sheet Name>").Range("<Range Address>"))
'<Sheet Name> where the data provider exit to be refreshed.
Set shtRefresh = ThisWorkbook.Worksheets("<Sheet Name>")
strVBA = Run("BExAnalyzer.xla!SAPBEXrefresh", False, Sheets(shtRefresh.Name).Range(<Range Address>))
Prerequisite to enable SAPBExSetVariable flow in BExAnalyzer 7x
- Please implement the notes 1832908, 1849135
- Note 2265155 - VBA Function SAPBExSetVariable not working as required in BExAnalyzer 7X
- Transaction RS_FRONTEND_INIT, Set parameter ANA_SINGLEDPREFR_NEW = 'X'
- Either set “The Flag "Allow Refresh Function for individual Queries" in the Workbook Settings Dialog or Global Setting in tab “General”.
