Working in Formula Mode

Use

In formula mode, you can edit the analysis table using Microsoft Excel formatting and formula functions and make further calculations using the existing data.

To switch to formula mode, select Convert to Formula from the context menu in the analysis table. This deletes the Analysis Table design item and defines every row in the table as a Microsoft Excel formula. The result values called from the server with the formula are still displayed in the table. The formula of the selected cell is displayed in the formula bar.

In formula mode, you can use all Microsoft Excel formatting functions. With the deletion of the design item, the individual formatting of the data will not be overwritten by the standard formatting in the analysis table the next time you update this data. For example, if you select a color to highlight interim results in the table and then navigate in this table, only the data for the values from the server is called and not the standard formatting from the analysis table. Your individual formatting is retained.

You can use the Microsoft Excel formula functions to make further calculations on the basis of existing data, for example to create an offer or perform planning activities. You can also copy the formula for a cell to another cell outside the table and thus work independently of the original table. If the workbook contains two analysis tables based on different data providers, you can combine the data from both data providers for your calculations.

Prerequisites

The following prerequisites apply:

If the queries used have a column structure or a row structure, then texts that occur several times in a column or row might produce invalid formulas (#NU) when they are converted to formulas. If you set the indicator Reference Structure, the formula can also be uniquely specified for these cells.
More information:Analysis Grid
When configuring the data provider, set the Provide Results Offline flag so that you can display the data without having to connect to the server.
More information:Configuring Data Providers

Features

Switch to Formula Mode

Insert an analysis table into a workbook.
To work in formula mode offline, configure the data provider in the workbook settings and choose Provide Results Offline.
In the context menu for the analysis table, choose Convert to Formula. This performs the following steps:
- Transfer of formatting is deactivated.
- Suppression of repetition of values in the rows is deactivated, so that the entire key is known for all rows.
- Every cell in the analysis table is defined as a Microsoft Excel formula.
- Design item Analysis Table is deleted.
In the properties for design item Analysis Table, you can set the Use Formulas flag. This defines the cells in the analysis table as Microsoft Excel formulas. The other steps are not performed, however. To be able to use all features, you should always switch to formula mode by choosing Convert to Formula in the context menu.
Use the formatting function and formula functions to make changes as required. You can access all data in the underlying data provider, as a link still exists between the result cells and the data on the BW server, and the values for each cell can be called using the formula.

The Formulas

There are two formulas that can be converted into the cells in the analysis table.

BExGetData
Cells are converted into this formula if the underlying query is not input ready. You can use the data on the BW server for calculation, but you cannot write data back to the server. This formula is made up of the following elements:
- Name of the Data Provider
  You set the name when configuring the data provider.
- Name of the structure component of the column or key of the corresponding cell, for example $H15.
- Name of the structure component of the row or key of the corresponding cell, for example F$20.
Example

Cell H20: =BExGetData("Data_Provider_1", $G15, F§20)

The data for the value in this cell come from data provider Data_Provider_1. The name of the structure component of the column in cell G15 is Sales Revenue in 2007. The name of the structure component of the row in cell F20 is Region France. The formula in cell H20 therefore uses the data from in Data_Provider_1 to calculate the sales revenue for Region France in 2007.
BExSetData
Cells are converted into this formula if the underlying query is input ready. You can use the data on the BW server and write the data you have modified back to the server. This formula is made up of the following elements:
- Value in the selected cell.
  Enter the key of the cell that contains the value to be written back to the BW server. Copy the formula to an empty cell in the workbook before modifying it. You should not modify the formula in the original analysis table.
- Name of the Data Provider
- Name of the structure component of the column or key of the corresponding cell.
- Name of the structure component of the row or key of the corresponding cell.
Example

Cell I20: =BExSetData (H20, "Data_Provider_1", $I15, F§20)

The value that you have entered in cell H20 is written back to the server from cell I20. Cell I15 stands for Planned Sales Revenue in 2008. The formula in cell I20 therefore writes the planning data for sales 2008 in Region France back to Data_Provider_1. The corresponding value is read from cell H20.

More information:

Input-Ready Query

Examples of Working in Formula Mode

In formula mode, you can use various functions to modify the layout and perform additional calculations.

You can highlight cells by formatting the font and background color.
You can insert spaces to make the display easier to read.
You can copy parts of the table or individual cells to another position in the workbook in order to compare particular values.
You can re-use cells.
You can overwrite a characteristic value with another one, or add one in order to call data that you need from the BW server. If characteristic value "3.2007" is used to read the sales revenue for March 2007, for example, you can replace the 3 with a 4, thus using characteristic value "4.2007" to obtain the sales revenue for April 2007, provided that the data provider contains this data.
You can also calculate additional subtotals:
You can create offers based on data from various data providers:
You can use existing data to carry out planning and write the planning data back to the BW server.

Restrictions

Switching to formula mode has the following consequences:

Navigation using Drag & Drop is no longer possible.
The context menu is not available.
The number format in the cells is set to standard.
You cannot switch back to analysis mode. To do this, you have to re-open the workbook.