## 3.13.2  Working in formula-optimized mode
You can open data sources in a formula-optimized mode. This allows the creation of highly flexible and formatted reports.
You can use all data sources connected to Analysis (SAP BW, SAP HANA and SAP Analytics Cloud). The data sources must have at least one key figure structure and they can contain restrictions, restricted key figures, calculated key figures and hierarchies.
The formula-optimized mode was introduced to foster the flexible creation of formula-based workbooks. It is intended to be used when you want to design workbooks that don't adhere to the classical resultset/crosstab based layout:
- individual data cells
- data combinations that can't be expressed in a single cartesian crosstab
##  Note
Note that the formula-optimized mode doesn't make processing and rendering formulas faster. And when SAPGetData formulas represent a complete or almost complete cartesian resultset, the formula-optimized mode will even be slower than the regular mode. Existing workbooks that use the regular mode will most likely not yield better performance in the formula-optimized mode.
To open a data source in formula-optimized mode:
1. Select a cell in the sheet and choose Data Source Select Data Source for Defining Formulas... .
2. Log on to a system and open a data source.
When you open a data source in formula-optimized mode, no crosstab is displayed. Therefore, the Analysis tab in the design panel does not show the Columns and Rows sections. You can use the sheet to define formulas.
On the Components tab in the design panel, you can use the property Use Data Source Formula-Optimized to switch between the formula-optimized and analysis modes.
In the recently used list, different icons are used for data sources opened for analysis and data sources opened in formula-optimized mode. You can open the data source directly from the list in the desired mode.
## The SAPGetData Syntax
SAPGetData formulas use the same syntax in the formula-optimized mode as in the regular mode:
SAPGetData(dataSourceAlias; measure: dimension\_1; dimensionMember\_1...; dimension\_n; dimensionMember\_n )
In this example, the dimension and the dimension member are provided as separate parameters. Alternatively, the dimension and dimension member parameters can be concatenated with a = sign and passed as a single parameter:
## SAPGetData(dataSourceAlias; measure; dimension\_dimensionMember\_1...; dimension\_dimensionMember\_n)
While a mixture between separated and combined parameters is supported, we do not recommend using it as it reduces the readability.
Since there are some important conceptional differences between the formula-optimized mode and the regular mode, the parameters are covered in more detail below.
- Data Source Alias Parameter
The data source alias parameter holds the formula alias for the data source(e.g. "DS\_1"). You can find the alias for a data source on the Components tab in the design panel.
- Measure Parameter
The measure parameter holds the name (key) or text of the measure member. If the measure member can't be resolved, the SAPGetData formula will return #VALUE!.
For data sources where the measures are restricted in a fix filter, the measure parameter must be empty: either SAPGetData(dataSourceAlias; ""; dimension\_1;...) or SAPGetData(dataSourceAlias; ; dimension\_1;...) .
- Dimension and Dimension Member Parameter
The dimension parameter holds the name (key) or text of the dimension. Key has precedence, so if there is a dimension with key "ABC123" and another dimension with key "XYZ789" and text "ABC123", then dimension with key "ABC123" is used when "ABC123" is the given identifier. If the dimension can't be resolved, the SAPGetData formula will return #VALUE!.
The dimension member parameter holds the key (external key), internal key or text of the dimension member. All three displays (key, internal key and text) will be used to look up the member (in case of a compound dimension, the look-up will also be done for partially compound keys). If the member can't be uniquely resolved (e.g. there are multiple members with matching keys or texts), the SAPGetData formula will return #VALUE!.
Alternatively, the dimension and dimension member parameter can also be provided in a combined way: dimension=dimensionMember .
## Presentation hints
To better indicate whether the given dimension member is a key, text or internal key so-called presentation hints can be used by suffixing the dimension parameter with {pres=key} for key, {pres=text} for text and {pres=internal\_key} for internal key.
This can be used when the parameters are combined (e.g. dimension{pres=key}=dimensionMember or dimension{pres=text}=dimensionMember ) as welll as when using separate parameters (e.g. dimension{pres=key}; dimensionMember ).
## Status and error codes
## #RFR
The status code #RFR indicates that the underlying data source needs to be refreshed to retrieve the value for the formula. This can be done via choosing Refresh All in the Data Source section of the Analysis ribbon or via Refresh from the context menu on the respective data source on the Components tab in the design panel.
## #VALUE!
The error code #VALUE! is shown if any of the given parameters are not valid.
## #N/A!
The #N/A! error code signals that while the given parameters were all valid, no value could be retrieved for the given parameter combination. Most likely because no corresponding booked value exists.
## Working with background filters
Dimensions in the background filters can be used. If a dimension that is in the background filter is also used in the SapGetData formula, the background filter for that dimension is ignored and the value that is provided in the formula is used instead.
Background filters can be assigned on the Analysis tab in the design panel.
## Working with hierarchies
We recommend the use of keys. Hierarchy nodes are best referenced via their input string +NodeKey(NodeTypeName). Ambiguities that result from non-unique keys and texts across the hierarchy are not resolved and #VALUE! will be shown.
## Tips
If you encounter any issue, you can check the following to solve it:
- Switch off parallel formula execution in Microsoft Excel. Choose File Options Advanced Formulas and deselect Enable mutli-threaded calculation .
- If the member value is a number, check if formatting it as text can avoid the issue. You can format it as text by adding an apostrophe before the number, for example.
## Examples
## Related Information
SAPGetData [page 79] SAPSelectMember [page 97]