You use the workbook settings function to configure general functions or apply themes to individual workbooks. To apply default settings valid for the entire application, including the default workbook, see Global Settings.
You configure general properties for the workbook on the General tab page, and configure themes on the Themes tab page. In this dialog box, you can also attach the macro required to use the BEx Analyzer API.
The following settings are available on the General tab page:
If this indicator is selected, when you open a workbook from the server, the workbook is automatically refreshed with values from the server. If this indicator is not selected, you have to manually update the workbook after you open it to retrieve the most current results.
If you do not set this indicator, the macro specified under Exits is not called when the workbook is opened.
More information: Refresh / Pause Automatic Refresh
This indicator is set by default. If you do not select this indicator, you cannot use drag and drop functions to navigate in the analysis grid and navigation pane of your query.
More information: Drag and Drop Functions
Here you can specify how non-existing values are to be displayed in the analysis grid.
Here you can specify how non-unique values are to be displayed in the analysis grid.
Set this indicator if your workbook includes a large amount of information and metadata and you want to reduce the space required by your workbook by compressing the data in the workbook when you save it.
You can use this option if, for example, you are working in formula mode and want to view the data in your result set without having to connect to the server (Provide Results Offline). To do this, you must first save the result set in the workbook. If you do not do this, the data is not available in the cells the next time you open the workbook; only the formulas are displayed.
In a planning workbook, when you change the value of an input-ready cell and then navigate, the changed value is stored in the buffer on the BI server. However, you can change this behavior by selecting the following options:
● Transfer Plan Values to Server Before Navigation – The changed value is transferred automatically when you navigate. You do not need to execute the Transfer Plan Values function explicitly from the context menu when you change data in an input-ready cell.
● Confirm Transfer of Plan Values to Server Before Navigation – A message popup prompts you to confirm whether the changed value needs to be transferred. You do not need to execute the Transfer Plan Values function explicitly from the context menu when you change data in an input-ready cell.
● Do Not Transfer Plan Values to Server Before Navigation – The changed value is not transferred automatically when you navigate. You have to execute the Transfer Plan Valuesfunction explicitly from the context menu when you change data in an input-ready cell.
If you select this indicator, the BEx Analyzer password-protects all the sheets in your workbook against any changes you make with BEx Analyzer functions. When you attempt to change your workbook by inserting a design item, for example, the BEx Analyzer requests the password you specified here. The BEx Analyzer only accepts the changes if you enter the correct password.
When the BEx Analyzer protects your workbook, you can still navigate in analysis mode, but you cannot enter data. Cells that are not locked with cell protection are not protected. Activate Enable Cell Protection in the Analysis Grid properties to protect the cells in the analysis grid as well.
The workbook protection in the BEx Analyzer is similar to Microsoft Excel’s workbook protection (Tools → Protection). However, you cannot navigate in your query if you protect your workbook using the Microsoft Excel protection.
If the Protect Workbook indicator is selected, the Password and Confirm Password fields are activated. Enter a password in the Password field, and re-enter the same password in the Confirm Password field.
In design mode, worksheets are protected by default to prevent you from unintentionally deleting design items. Temporarily disable sheet protection (Tools → Protection → Unprotect Sheet on Microsoft Excel’s menu) or switch to analysis mode before formatting your workbook.
All data providers that are configured for the design items of the selected workbook are displayed on the Data Providers tab page.
Under Properties, you can change an existing data provider or create a new data provider. You can also set the following indicator:
● Restore Initial Query View on Refresh
Select this indicator if you want to display the view that was saved in the Query Designer each time you open a workbook. If you do not select this indicator, when you save the workbook, the current navigational state is saved and is later displayed when the workbook is opened again.
If any query in your workbook contains variables, any current values set for the variables are part of the query view definition and are therefore stored in the workbook.
Set this indicator if the values saved in the workbook are to be used automatically.
If you also set the Refresh Workbook on Open indicator, the variable dialog is always displayed when a workbook is opened. The values previously saved are automatically selected in the variable dialog; however, you can change these values.
More information: Using Variables in the BEx Analyzer
If a workbook contains multiple queries that use the same variable, you are prompted to enter the variable values again for each query. To avoid this, select the Display Duplicate Variables Only Once indicator.
You can save a variant to a query or workbook. The variants of a workbook can contain the variable values for multiple data providers. You can only save a variant to a workbook if the workbook is already saved on the server and has received an ID. You can choose from the following settings:
If you choose this option, the variant is saved to the query if the workbook only contains one data provider. If the workbook contains more than one data provider, the variant is automatically saved to the workbook.
● Workbook Name
If you choose this option, the variant is always saved to the workbook.
● Data Providers
If you choose this option, the variant is always saved to the query.
BEx themes, based on NetWeaver Portal styles, are shared services across the Business Explorer suite. In the BEx Analyzer, they are delivered as a set of Microsoft Excel style definitions for your workbook. Themes provide formatting information such as background color, font, and font size. To display the set of style definitions for a workbook, choose Format → Style from the Microsoft Excel menu. The styles provided by the Business Explorer are prefixed with SAPBEX*.
Themes are content objects that are stored on the server and that you can choose to activate. Themes are stylesheets in which you can make important changes to a theme. All workbooks in which the theme is used are updated accordingly.
The following settings are available on the Themes tab page:
The list of available BEx themes is displayed. The BEx theme that is currently applied to your workbook, if any, is selected.
If the following special authorization is assigned to your user name, you can change and save themes on the server: AUTHORITY-CHECK OBJECT 'S_RS_TOOLS' ID 'COMMAND' FIELD 'THEMES'.
If you have this authorization, these buttons appear:
● Create a new theme by choosing New.
● Select a theme from the Current Theme list, and choose Delete to delete it. This deletes your theme locally.
● Select a theme from the Current Theme list, and choose Apply to apply currently configured styles to it. When you do this, the BEx Analyzer reads the Microsoft Excel style definitions of all the styles in the workbook that are prefixed with SAPBEX* and stores them in the selected theme. This allows you to edit the styles in Microsoft Excel and then create a theme with these customized styles.
● To save all the themes to the server, choose Save.
Since BEx themes are saved on the server, they can be changed on the back end. Select this indicator if you want to retrieve the most up-to-date definition of the style from the server and apply it again.
Select this indicator to update the current workbook with any style that has been added to the theme on the server since the theme was originally applied. If, for example, new styles have been added to a theme during a patch release, you can retrieve them from the server with this option.
Select this indicator to stop using the selected theme in your workbook.
All design items that are inserted in the selected workbook are displayed on the Design Items tab page. You can display or modify the properties of a design item, as required.
On the Exits tab page, you can choose whether a macro can be called during the refresh. You can choose any macro name. Note that the macro is only called when the workbook is opened if the Refresh Workbook on Open indicator is set on the General tab page.
The signature of the macro must be as follows: Sub <MACRO_NAME>(ParamArray varname())
<MACRO_NAME> is a place holder for the name of the macro, which can be any text of your choice, for example, RefreshMacro.
The name of the data provider is in varname(0).
The range is contained in varname(1).
Sub RefreshMacro(ParamArray varname())
Dim lData_Provider As String
lData_Provider = varname(0)
Set lRange = varname(1)
To use the BEx Analyzer API, you need a particular macro as well as references to the type libraries. Instead of manually maintaining the macro and references, you can do this using the following toggle button, which is available on both tab pages in this dialog box.
Choose Add Macros to attach the macro and references you need to use the BEx Analyzer API. To remove the references, choose Delete Macros.
If you attach macros to your workbook and then save it locally, when you open it again, a security popup is displayed. For this reason, macros are not automatically attached to workbooks. Simply choose Delete Macros to discontinue their use and the appearance of these popups.
Choose Workbook Settings in the design toolbar, or BEx Analyzer → Design Toolbar → Workbook Settings in the menu.