The purpose of this page is to explain more about the cell reference formula at the Business Objects Analysis for Office.
This WIKI page has as objective help users that are trying to add cell reference in some Business Objects Analysis for Office workbook.
Cell reference formula
This function returns the measure value for a specific dimension member combination.
The formula can only return values for member combinations that are part of the current navigation state of the data source. To be part of the navigation state, the member combinations must be used in rows, columns or as a background filter. If you filter a dimension, you can only return values for member combinations that the filter contains. For example, if the navigation state of the data source displays the dimension Region in rows and the measures Sales Volume in columns, you can create a formula to return a value for a particular region, but you cannot return a value for a special customer, even if customer information is available in the data source. To be able to return values for a special customer, you have to add the dimension to the navigation state, for example as a background filter.
The formula consists of at least 3 parameters and is made up of the following arguments:
• Data Source
Enter the formula alias for the data source. You can set the alias when configuring the data source on the Components tab in the design panel.
Enter the name of measure, for example "Incoming Orders".
• Member combination
There are two methods for entering the member combination:
• Enter one parameter as member combination, for example "Region=France;Product=Services"
. This method is used for converting to formula.
• Enter several parameters as the member combination, for example "Region";"France";"Product";"Services". This method can only be entered manually. It is recommended for member
Adding cell at the formula
The data for the value in this cell comes from data source DS_1. The name of the measure is Incoming Orders. The member combination is the region entered in cell B10 and Services. For example, if you enter Spain in cell B10. the formula in cell H20 uses the data from DS_1 to calculate the incoming orders for Region Spain and Product Services. If you change Spain to France in cell B10, the incoming orders for France and Services are displayed in cell H20.
Cell H20: =SAPGetData("DS_1";"Incoming Orders";"Region";B10;"Product";"Services")
The cell reference need to be without quotes (“) to the Excel understand as cell reference and not text.