After „SAP BW 7.4 Maintain characteristics“ is one of the most read posts. I visualize this post in a short video.
The most important thing when you are working with VBA in Analysis for 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 for Office 2.1".
The command is:
In BEx Analyzer you could jump into another query from a query / workbook. The GoTo-Function make sense if you have one query for overview and one for detail.
When you have macros in your Analysis for Office Workbook and you remove the Crosstab of a DataSource, Analysis for Office maybe crashes. Therefore, if you need the DataSource no longer, you should remove it completely and not only the Crosstab.
In Analysis for Office 2.0, SAP has changed the name of the addin. The command is now:
Now you can continue working with the SAP Analysis commands.
Update: Since Analysis for Office 2.3 you also can filter your data by uploading a file.
As in BEx Analyzer, there is important information that you would like to represent in a Workbook. If you have worked with BEx Analyzer, this screenshot should be familiar.
The previous post about Excel Dashboards has such an appeal found, so I want to show another example.
You want to use the data which is provided by the Business Warehouse in your monthly report or any other report. The SAP Reporting tools like BEx Analyzer or Analysis for Office provides this data in "data tables". These are not easy to read and maybe it isn't possible to see at first glance what you want to express.
You can use the ABAP program RRMX_WORKBOOKS_GARBAGE_COLLECT to find workbooks that are currently assigned to a user or group of users via the transaction SE38. Through the hook "Workbooks found erase" these are removed from the system.
With every new release of SAP HANA functions are moved from the OLAP engine into the database. The current state of the push down can be found in note 2063449. So check this note from time to time, if it is interesting to implement a service pack.
Note: You need a S-User to access this note.
A limiting factor in SAP BusinessObjects Analysis for Office is the restriction to 500,000 cells per DataSource. The limitation is calculated from rows * columns. This limitation can be increased by the following parameter value in the registry:
HKEY_CURRENT_USER\Software\SAP\AdvancedAnalysis\Settings\DataSource\ResultSetSizeLimit = -1
The Value “0” means that the default value is defined by 500,000 cells. Values greater than zero will return the entered value as number of cells. If you want to use the Business Warehouse settings you have to set the Value “-1”.
The corresponding parameter value for this can be found in the Business Warehouse table RSADMIN. The Object is “BICS_DA_RESULT_SET_LIMIT_MAXHKEY”.
Unfortunately, SAP shifted the maintenance of master data in SAP BW 7.4 into the Web. Not everyone wants to maintain the master data on the Web. Here is a small workaround.
From time to time there is a mistake in the resolution of structured items and the transfer of material hierarchies to BW. It helps the Note 1410263.
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