orkungedik/SAP-basis-dataset huggingface dataset:
## 3.14.19  Hiding / Showing Analysis UI components
You can use the commands Hide and Show of the API method SAPExecuteCommand to hide and show Analysis UI components.
The changed visibility of UI components is valid for the active workbook in which the VBA macro is executed. If the Analysis tab is hidden in a workbook and the user switches to another workbook, the Analysis tab is visible again. When switching back to the first workbook, the Analysis tab is hidden again.
The following types and IDs are supported:
- Ribbon
- All Use this ID to hide the Analysis tab in the ribbon. Example: Application.Run("SAPExecuteCommand", "Hide", "Ribbon", "All")
- Default
Use this ID to show the Analysis tab in the ribbon.
Example:
Application.Run("SAPExecuteCommand", "Show", "Ribbon", "Default")
- ContextMenu
- All
- Use this ID to hide the Analysis context menu. Application.Run("SAPExecuteCommand", "Hide", "ContextMenu", "All") Default Default Example: Example:
- Default Default
Use this ID to show the complete context menu.
Example: Application.Run("SAPExecuteCommand", "Show", "ContextMenu", "Default") Application.Run("SAPExecuteCommand", "Show",
## Related Information
SAPExecuteCommand [page 104] Enhancing the Analysis Ribbon [page 131]
## 3.14.20  Enhancing the Analysis Ribbon
The Microsoft Office Ribbons can be enhanced and adjusted. You can learn more about the general ribbon enhancement in the Microsoft documentation.
If you build an application related to Analysis, you can visualize the dependencies to Analysis by locating the new ribbon tab beside (before/after) the Analysis tab or by adding new ribbon groups to the Analysis tab. The ribbon is described via an XML definition where an identifier is assigned to each item. The Analysis tab has a qualified identifier (idQ) with the name space SapExcelAddIn and the id com.sap.ip.bi.analysis.menu.
## Example: Adding a tab before the Analysis tab
```
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" xmlns:x="SapExcelAddIn"> <ribbon>
```
```
<tabs> <tab id="myCustomTab" label="Custom Tab" insertBeforeQ="x:com.sap.ip.bi.analysis.menu" > <group id="myCustomGroup" label="Custom Group" > <button id="myButton" label="my Button" onAction="myAction" /> </group> </tab> </tabs> </ribbon> </customUI>
```
With this example, the new tab 'Custom Tab' is added before the Analysis tab containing the group 'Custom Group' with the button 'my Button'.
## Example: Adding a ribbon group to the Analysis tab
```
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" xmlns:x="SapExcelAddIn"> <ribbon> <tabs> <tab idQ="x:com.sap.ip.bi.analysis.menu"> <group id="myCustomGroup" label="Custom Group" > <button id="myButton" label="my Button" onAction="myAction" /> </group> </tab> </tabs> </ribbon> </customUI>
```
With this example, the group 'Custom Group' with the button 'my Button' is added to the Analysis tab.
## Related Information
Hiding / Showing Analysis UI components [page 130]
## 4 Analyzing Data
On the Analysis tab in the ribbon, you have different options to analyze your data.
Using the design panel, you can analyze the data and change the view on the displayed data. You can add and remove dimensions and measures to be displayed with drag and drop. To avoid single refreshes after each step, you can pause the refresh to build a crosstab. After ending the pause, all changes are applied at once.
You can refine your analysis using filters, prompting, calculations and display hierarchies. You can also calculate new measures based on existing ones and define the display of members, measures and totals in the crosstab.
##  Note
When your analysis requires a data update from the server (BW and HANA) and the crosstab is redrawn, you might get a dialog with the option to cancel your request. By default, the dialog appears after 5 seconds. You can use the setting CancelPopupDelay to define another time in seconds for the dialog.
If you cancel the server request, you can select Restart to go back to the workbook and refresh all the data sources in the workbook. Or you select Close to go back to the workbook, and the data sources in the workbook are disconnected. Note that the refresh of all data sources that is started automatically with the option Restart could take very long.
If you don't cancel the server request, the dialog will disappear automatically when the server request is completed.
With Microsoft Excel functionality, you can add a note to a cell by inserting a comment. You can edit the text in comments and delete comments that you no longer need.
You can also plan business data based on the current data in your data source. You can enter the planning data manually and you can enter planning data automatically using planning functions and planning sequences of SAP BW Integrated Planning.
## Related Information
Analysis tab [page 10] Analyzing data with the design panel [page 143] Prompting [page 160] Filtering data [page 168] Sorting data [page 183] Working with hierarchies [page 186] Calculating new measures [page 191] Defining the display of members, measures and totals [page 198] To jump to a target with Goto [page 205] Planning Data [page 210]
## 4.1 Analyzing data with the design panel
The Analysis design panel is an additional element on the user interface that you can use to create new views on your data, to find information on the used data sources and on the components of the workbook. The design panel consists of of several tabs, for example Analysis , Information and Components .
You can show and hide the design panel by choosing Display Display Design Panel in the design panel group. You can modify the size and position of the design panel in Microsoft Excel. Your modifications of the design panel will be preserved even if you close Analysis or hide the design panel and show it again.
You can also show and hide the technical names in the design panel by choosing Display Show Technical Names in the design panel group. The technical names are displayed in squared brackets.
On the Analysis tab, you can show and hide the property view on the design panel by choosing Display
Show Property View . There, you can also decide to display dimensions as grouped dimensions by choosing
Display Show Dimension Grouping .
Working with the design panel, the crosstab is updated after each navigation step. You can choose Pause Refresh to deactivate the permanent refresh.
## Related Information
The Analysis tab [page 143]
The Property View on the Analysis tab [page 145]
The Information tab [page 147]
The Components tab [page 148]
The Design Rules tab [page 154]
The Comments tab [page 155]
Pausing Refresh [page 156]
Managing components in the design panel [page 157]
Managing system connections in the design panel [page 159]
## 4.1.3  The Information tab
On the Information tab, you can see detailed information about a data source or the complete workbook. You can also find information on filters and variables on this tab. The general information is displayed as text elements.
In the Information for list, you can select the complete workbook or one of the inserted data sources. The information fields are displayed for the selected object. You can insert these fields in the analysis using drag and drop.
For a workbook, the following information is displayed:
- Workbook Name
- Created By
- Variables
- Logged On User
- Last Refreshed At Shows the time when the workbook was last refreshed in Analysis.
- Created At*
Shows the time when the document was created/saved for the first time on the server (e.g. BI platform).
- Last Changed At*
Shows the time when the document was last modified/changed on the server (e.g. BI platform).
*Please note that the server time could differ from the time in Analysis depending on your time settings on the server. In addition, the server time information is not available for all platforms you can use with Analysis, so you won't get the times for all platforms.
For a data source, the following information is displayed:
- Data Source Name
- Key Date
- Last Data Update
For MultiProviders, two dates are displayed: The date when all InfoProviders of the MultiProvider were updated successfully for the last time (LastDataUpdate) and the date when a single InfoProvider of the MultiProvider was updated for the last time (LastDataUpdateMaximum).
- Variables
- Filter
- BEx Conditions
- Query Technical Name
- InfoProvider Technical Name
- InfoProvider Name
- Created By
- Last Changed By
- Last Changed At
- System
- Logged On User
## Related Information
To insert an info field [page 46] To insert a filter [page 46]
## 4.1.5  The Design Rules tab
On the Design Rules tab, you can display and maintain the rules created with Table Design. The rules are displayed for each data source and rule type.
You can select the data source in the Rules For field. If a workbook contains only one data source, then this data source is preselected.
You can copy the rules from one data source to another: Select the copy icon next to the data source. Then select another data source and the paste icon. The rules are added to the list. After copying the rules, they are no longer connected. If you edit a rule, the copied one will not be changed automatically.
In the Rule Type field, you can select a rule type. The following rule types are available:
- Format
- Formula
- Texts
- New Lines
The rules for the selected data source and rule type are displayed in the list. You see a name and a description for each rule. The information in the list is filled in automatically. If a rule is deactivated, the corresponding icon is displayed in front of the name.
Rules of type Format can be edited from the design panel. If you select a rule and Edit , a dialog box appears where you can change the rule. For rules of type New Lines , you can edit the Row Height / Column Width.
Rules of type Formula can be edited in the design panel. Only formulas added to data cells of an input-ready planning data source can not be edited in the design panel.
You can select a rule and Copy Rule to copy the rule. The new copied rule is added to the list with the same description as the original rule but with a different name.
You can select a rule and Delete , to delete it completely.
You can select a rule and Deactivate to deactivate it, if you don't want the rule to be applied at the moment. If you deactivate a rule, the icon is displayed in front of the rule name. You can activate the rule again.
You can use the arrows above the list to move the rules up and down in the list to change their priority.
If you select the button Mark Rules Based on Crosstab Selection and you select a cell in the crosstab containing a rule, the rule is highlighted in the list.
## Related Information
Editing crosstabs with Table Design [page 50]
## 4.1.6  The Comments tab
On the Comments tab, you can see a section that shows the comment history for a selected cell and a section that shows the context information for the cell.
In the History section, you can see different comment versions that were saved for a data cell. The comment history is only available for comments of SAP BW/4HANA data sources:
1. Enter a comment for a selected data cell and select Update History on the Comments tab. The comment is displayed in the design panel. If you edit the comment, the text in the design panel is also changed.
2. Save the comment.
3. Edit the comment.
The new version of the comment is added in the design panel and the saved version is shown the earlier version.
Below the History section, the Context section shows the context information for a selected cell with the following information: data source name and context information (member and measure names). This information for all data sources.
## 4.1.7  The Versions tab
A version is a set of data from the database, viewed in a table. To control who can access a version, you make the versions public, private, or shared.
On the Versions tab in Analysis, you can see the versions for a SAP Analytics Cloud data source and properties and a history for each version. If you use multiple data sources in your analysis, you can select between the data sources and the corresponding versions will be displayed.
This tab is only visible in the design panel when a SAP Analytics Cloud model is inserted as data source.
The Versions tab contains the following sections:
- Versions for: field In this field, you see the name of the data source that is currently selected. If you use multiple data source in your analysis, you can use the drop down list to select another one.
- Category: field
In this field, you can select by category which versions should be displayed on the Versions. If you select All , the versions for all categories are displayed. If you select a special category, for example Planning , the versions for this category are displayed.
· Properties In this section, you can find general and history information for a selected version. On the General tab, you can see general information such as the name, the category and the type of the selected version. On the History tab, you can see a list of the steps executed for this version. Y ou can select a step and undo or redo it. Y ou can also revert changes executed for a version. After reverting the changes, the steps on the History tab are deleted.
You can find more information in the SAP Analytics Cloud documentation at About Version Management.
## Related Information
Planning Data [page 210]
## 4.1.8  Pausing Refresh
You can choose Pause Refresh to deactivate the refresh after each navigation step when you work on the Analysis tab in the design panel. With the deactivated refresh, you can design a new view on your data with multiple navigation steps. All steps will be executed at once when you choose Pause Refresh again to activate the refresh. Note that most functions in the menu are not available when Pause Refresh is activated. You should only work on the Analysis tab when the automatic refresh is deactivated.
You can also activate Pause Refresh before inserting a data source. You can insert a data source and define your analysis on the Analysis tab in the design panel (columns, rows and filter). The crosstab with the data source and the defined analysis will only be inserted after Pause Refresh is deactivated again.
The following functions are available in the context menu of the design panel if Pause Refresh is selected:
- Define the members display More information: To define the members display [page 198]
- Define the number format
More information: To define the number format [page 200]
- Show or hide totals in the crosstab More information: To show or hide totals in the crosstab [page 203]
- Define the calculation of totals More information: To define the calculation of totals [page 204]
- Calculate a new measure based on available measures
More information: To calculate a new measure based on available measures [page 193]
- Add a new measure based on one available measure (dynamic calculation) More information: To add a new measure based on one available measure [page 194]
## 4.1.9  Managing components in the design panel
On the Components tab, all components included in a workbook are displayed in a tree-like structure. You can select if the components should be listed by data source or by sheet. In both cases, the highest node of the list is the workbook. Below this, the data sources or the sheets are displayed with their components.
You can use the context menu to manage the components. The following sections describe the functions of the context menu.
## To insert a component
On workbook level, you can insert the following components:
- data sources ('Use Data Source')
- planning functions ('Use Planning Function')
- planning sequences ('Use Planning Sequence')
On data source level, you can insert a crosstab for this data source ('Insert Crosstab'). A dialog box appears to enter the sheet and cell information where the copied crosstab should be inserted.
On the planning objects level, you can insert planning functions ('Use Planning Function') and planning sequences ('Use Planning Sequence')
## To refresh a component
You can refresh the following components with the context menu:
- workbooks
- crosstabs
- charts
## To copy a component
You can copy the following components with the context menu:
- data sources When you copy a data source, the copied data source is added to the tree-like structure with a new data
source alias. No crosstab or other component of the original data source is added to the workbook.
- crosstabs
When you copy a crosstab, a dialog box appears to enter the sheet and cell information where the copied crosstab should be inserted. The new crosstab is inserted on the defined position and in the structure, a entry with data source, data source alias and crosstab is added.
- sheets When you copy a sheet, the copied sheet is added to the tree-like structure.
## To move a component
You can move crosstabs and charts ('Move To'). A dialog box appears to enter the sheet and cell information where the crosstab or chart should be moved to.
## To jump to a component
You can use the context menu to jump to a crosstab or a filter ('Jump To'). You can use this function to jump to a crosstab on another sheet in the workbook, for example. The sheet with the selected crosstab will be displayed.
## To execute planning objects
You can use the context menu to execute planning functions and planning sequences.
## To delete a component
You can delete the following components with the context menu:
- data sources
- crosstabs
- charts
- filters
- planning functions
- planning sequences
## 4.1.10  Managing system connections in the design panel
On the Components tab, you can manage the system connections on workbook level. You can log off from all systems the workbook is connected to. You can also reconnect to the systems or replace a system with another system.
You can use the context menu to manage the system connections. The following sections describe the functions of the context menu.
## To log off a workbook
To disconnect a workbook from the server (BW and HANA), select the workbook and choose Log Off in the context menu.
The connections to all systems in the workbook are closed and the design panel is closed.
You can also disconnect a workbook using the API method SAPLogOff.
## To reconnect a workbook
To reconnect to the systems, display the design panel, select the workbook and choose Refresh All in the context menu.
All systems in the workbook are reconnected.
You can also reconnect a workbook using the API method SAPLogOff.
## To replace a system in a workbook
To replace a system, select Replace System... in the context menu. The option is only enabled if the workbook is not connected to any system.
In the dialog, you can select the system that you want to replace (Current System) and the system that you want replace the current system with (Replace By System).
For BW and HANA systems, you can replace local connections and connections from the BI platform. If a workbook contains BW and HANA connections, you will find two tabs in the dialog: BW and HANA.
To list the BIP connections in the Replace By System section, select the Logon to BIP icon and log on to the BI platform.
You can also open the dialog for replacing systems in the Platform options.
## Related Information
SAPLogOff [page 113]
## 4.2 Prompting
In the prompting dialog, you can set values for prompts. Prompts are defined as variables in BEx Query Designer. The variables are parameters of a BW query and are filled with values when you insert a query as a data source in a worksheet. They serve as placeholders for members, hierarchies, hierarchy nodes, formula elements and texts. After defining the variable values, the crosstab is displayed according to the selected values.
To be editable in the prompting dialog, a variable must be defined as input-ready and set to Manual Input in Query Designer. A variable can be defined as mandatory or optional in Query Designer.
The prompting dialog appears automatically when you insert the query with variables in a worksheet. You can open the prompting dialog manually with the prompting icon in the menu to change variable values.
At workbook level, you can select properties to define how the prompting dialog and defined values behave in workbooks. For example, you can define that variable values are saved in a workbook. If you open this workbook again, the data is displayed according to the defined values. You can then open the prompting dialog and change the values.
Defined prompt values can be saved as variant for a query or for a document using the variant dialog in the prompting dialog.
If there are no input-ready variables in the query that you use as your data source, the prompting dialog is disabled.
##  Note
Objects that are defined as input-ready variables in BEx Query Designer are called prompts in Analysis for Microsoft Office. In the following documentation the Analysis term prompt is used for these variables. A query can also contain other variable types. All variables and values of these variables that a query contains, are displayed in the Variables area on the Information tab in the design panel.
To find more information about the definition of variables in the BEx Query Designer, see the documentation on the SAP Help Portal at Variables.
## Related Information
To define prompt values [page 161] Using Variants [page 163] To select workbook properties for prompting [page 167]
## 4.2.2.1 To create a user-specific variant
## Procedure
1. Open the prompting dialog in the required mode.
2. Define the prompt values for the variant.
3. Enter a name for the variant in the Use Variant field.
4. Press the save icon.
## Results
The new variant can now be selected in the drop down list.
## 4.2.2.2 To create a global variant
## Prerequisites
When using document variants on the SAP BusinessObjects BI platform, global variants are available only if the Variants Support is set to User and Global in the Central Management Console, properties of the Analysis runtime.
## Context
You create a global variant by changing the type of a user-specific variant.
## Procedure
1. Open the prompting dialog in the required mode.
2. Select the Show Variant Settings icon. The available variants are listed in the Variant Settings dialog with name, type and technical name.
3. Select the change icon next to the user-specific variant you want to change.
A user-specific variant has the type User .
4. Select type Global for the variant.
5. Enter a technical name.
6. Press the OK icon.
7. Press Close to close the variant settings dialog.
## Results
The variant is now saved as a global variant and can be used by all authorized users.
## 4.2.2.3 To apply a variant
## Procedure
1. Open the prompting dialog in the required mode.
2. Select the variant in the drop-down list.
The variants are listed in alphabetical order: Firstly, the user-specific variants are listed. The global variants are then listed, separated by a bar.
After selecting the variant, the first prompt in the Prompts Summary area is selected and the corresponding entry in the Specify Value for Prompts area.
3. Press the OK button.
## Results
The variant is applied to the query or workbook, and the crosstab is displayed accordingly.
## 4.2.2.4 To change a variant
## Procedure
1. Open the prompting dialog in the required mode.
2. Select the variant in the drop-down list. The variants are listed in alphabetical order. After selecting the variant, the first prompt in the Prompts Summary area is selected and the corresponding entry in the Specify Value for Prompts area.
3. Change the prompt values for the variant.
4. Press the save icon.
## Results
The variant is saved with the new defined prompt values. If you want to change the variant name, go to the variant settings dialog.
## 4.2.2.5 To delete a variant
## Procedure
1. Open the prompting dialog in the required mode.
2. Press the Show Variant Settings icon. The available variants are listed in the Variant Settings dialog.
3. Press the delete icon next to the variant you want to delete. The variant is deleted.
4. Press Close to close the variant settings dialog.
## Results
The variant is deleted permanently.
## 4.3 Filtering data
In Analysis, you can define criteria to restrict the data displayed in your analysis to a subset of data. This action is called filtering. By filtering, you define which subset of data appears in the analysis. This allows you to create new, more specific views of your data. Filters are additive, which means that each additional filter is based on the current filter and further reduces the subset of data.
There are two types of filters. With "Filtering members", you can specify which members to display and which not to display in your analysis. You can also define members to use as filters in the background of the analysis. With "Filtering measures", you can specify which measure values should be displayed in the analysis. For example, you can define that you want to see the Top 3 regions in sales volume for each product in your analysis. You can also filter out rows and columns that contain only zeros.
## Related Information
Filtering members [page 169]
Filtering measures [page 177] To show/hide zeros in rows and columns [page 182]
## 4.3.1  Filtering members
By selecting and removing members of your analysis, you can create new views of your data. You can filter members of a flat dimension with the filter dialog or directly on the crosstab. You can also filter members of a hierarchical dimension (hierarchy). The measures in an analysis are combined to one dimension, the measure dimension. You can also define criteria that are used in the background of the analysis for filtering.
For example, if you analyse the sales volume of your company in the last three years, and you only want to see the values for the last three month of each year, you can remove the other months from your analysis.
Filtering members is a static action. Members that you have removed from your analysis remain excluded unless you change the filter criteria and include them again.
Member filters affect the totals and subtotals in your analysis. Only the values for the displayed members are included in the totals.
## Related Information
To filter data by member [page 170]
To filter data by member within hierarchies [page 172]
To filter members directly on the crosstab [page 173]
To filter out members directly on the crosstab [page 174]
To create a background filter [page 174]
To remove a filter by member [page 176]
## 4.3.1.2 To filter data by member within hierarchies
## Prerequisites
The data source you are using contains hierarchies.
## Procedure
1. Select a cell in the crosstab that belongs to the hierarchy you want to use for filtering.
2. Choose Filter Filter by member . The Filter by Member dialog box for hierarchies appears.
##  Note
You can also open this dialog box using the context menu on the crosstab or the design panel.
3. If you want to search for members, clear the Select All checkbox in the members list and enter a search string in the entry field. In the dropdown list of the search button, you can select if you want to search for a key or a text.
Members that match the search string are displayed in the members list and can be selected.
4. With the Display list, you can select how the members are displayed. The following options are available: Key, Text, Text and key, Key and text and Show Attributes. You can also select the Access Mode.
##  Note
If no text is defined for a member, the key is displayed as text. Members without text are displayed first in the filter dialog. Even if the key is displayed as text, the search for text is not possible if no text is defined. The result list will be empty.
In the Show Attributes list, you can find the attributes that are available for the members in your hierarchy. For example, a product hierarchy could have 'size' and 'color' as attributes. You can select and add these attributes to the member list in the filter dialog. This additional information may help you to make the selections for filtering.
5. In the list box, choose which members should be displayed in the list for selection.
- Select Hierarchy in the list if you want the members to be listed with the hierarchy.
- Select Leaves if you want all members of all levels to be listed in a flat view.
6. Choose the Show Selected button if only the selected members should be displayed. The button name changes to Show All . Choose this if you want all members to be displayed.
7. Select the members that you want to use for filtering in the members list.
In the members list, all members are selected in the default setting. To deselect a member, clear the corresponding check box. You can also deselect all members by clearing the Select All checkbox and selecting individual members from the list.
Note: To select and deselect members in the list, you can click the checkbox or the text next to the checkbox.
If you deselect a node in a hierarchy, all leaf members of this node are deselected automatically. The parent members of the deselected node are still selected, but the background color of the checkbox changes to gray. That shows you that not all leaf members of this node are selected for filtering.
8. Press OK to apply the filter to your data.
## Results
The data in the worksheet is filtered according to your selection. The filter is saved with the workbook and is available to other users or in other sessions. You can also reopen and edit existing filters.
## Related Information
Access mode for filtering [page 176]
## 4.3.1.3 To filter members directly on the crosstab
## Context
You can filter for one or multiple members directly using the context menu or double-clicking a member cell.
## Procedure
1. In the crosstab, select one cell for each member that you want to filter for. To select multiple members, hold down the Ctrl or Shift key as you select members.
2. Choose Filter Members in the context menu.
##  Note
To filter for one member only, you can also double-click the corresponding member cell.
In the design panel, the filter icon is added to the corresponding dimension.
## Results
The data in the worksheet is filtered according to your selection. You can apply this filter to dimensions with and without hierarchies.
## 4.3.1.4 To filter out members directly on the crosstab
## Context
You can filter out one or multiple members directly using the context menu or drag and drop.
Please note that you can filter out members for dimensions without hierarchies and for flat and hierarchical structures.
## Procedure
1. In the crosstab, select one cell for each member that you want to filter out. To select multiple members, hold down the Ctrl or Shift key as you select members.
2. Choose Filter Other Members in the context menu.
To filter out the selected members using drag and drop, select the border of the marked field(s) and drag them out of the crosstab.
## Results
The data in the worksheet is filtered according to your selection.
## 4.3.1.5 To create a background filter
## Context
In Analysis, you can set filters for dimensions and hierarchies that you have chosen to be displayed in the crosstab. This allows you to narrow the scope of your analysis to the data you need. You can however
also select members for filtering that should not be displayed in the crosstab. These non-visible filters are background filters.
For example, you have selected the dimensions Product and Region to be displayed in the crosstab. The data source contains the sales volume for the years, 2008, 2009 and 2010. If you are only interested in the sales volume for 2010, you can add the Year dimension as a background filter to your analysis and filter for 2010. The analysis will show the data for year 2010, but the Year dimension is not visible in the crosstab.
##  Note
Background filters for a measure dimension can contain only one measure as a filter. The same is true for other dimensions that do not aggregate.
You have the following options to add a background filter to your analysis:
## Procedure
1. You can add a dimension to the Background Filter area and define a filter:
- a. Add the dimension that you want to add to the Background Filter area in the design panel.
3. You can add the dimension using drag and drop or you can choose Move to Background Filter in the context menu.
- b. Choose Filter By Member in the context menu. The Filter by Member dialog box appears.
- c. Make your selection and press OK . The background filter is created.
2. Or: You can add a dimension that already has a filter. The existing filter will be applied as a background filter in the crosstab.
3. Or: You can add a background filter using the context menu on the crosstab.
- a. Select the member(s) that you want to define as background filter in the crosstab.
- b. Choose Filter Members and Swap With in the context menu. The dimension with the selected filters is added automatically to the Background Filter area, and the
10. dimension chosen for swapping with is added to the Rows area in the design panel.
## Results
The data in the crosstab is displayed according to your filter selection.
## 4.3.1.6 To remove a filter by member
## Procedure
1. Select a cell in the crosstab that belongs to the dimension you want to remove the filter for.
2. Choose Filter Select All Members .
##  Note
You can also choose Select All Members in the context menu on the crosstab or the design panel.
## Results
All members of the selected dimension are displayed in the crosstab.
## 4.3.1.7 Access mode for filtering
In the Filter by Member dialog, you can define the access mode that is used to display the members in the dialog. The acces mode is available in the Display list. The available options vary for the different data sources: BW data sources, SAP HANA analytic views, SAP HANA calculated views and SAP Analytics Cloud models.
##  Note
The Access Mode option is available only if you enable it. You have the following options to do so:
- Select the check box Show Access Mode for Member Display in the Advanced options dialog.
- Set the file setting AllowChangingAccessMode to true. You can do this in the Technical Configuration in the Analysis backstage area or in the file system.
## Access mode for BW data sources
For BW data sources, you have the following options:
- Choose Access Mode Values in Master Data to display all members for the dimension (with and without posted data).
- Choose Access Mode Only Values in InfoProvider to display the members contained in the InfoProvider (data source).
- Choose Access Mode Only Values with Posted Data in Current Navigation State to display the members with posted data that are selected in the current navigation state.
- Choose Access Mode Characteristic Relationship to display the members that are valid according to a defined characteristic relationship.
## Access mode for HANA analytic views
For SAP HANA analytic views, you have the following options:
- Choose Access Mode Values in Master Data to display all members for the dimension (with and without posted data).
- Choose Access Mode Values in Fact Table to display the members with posted data.
- Choose Access Mode Only Values with Posted Data in Current Navigation State to display the members with posted data that are selected in the current navigation state.
- Choose Access Mode Only Values for Existing Attribute Combinations in Master Data to display all members for existing attribute combinations.
## Access Mode for SAP HANA calculated views
For SAP HANA calculated views, you have the following options:
- Choose Access Mode Values in Fact Table to display the members with posted data.
- Choose Access Mode Only Values with Posted Data in Current Navigation State to display the members with posted data that are selected in the current navigation state.
## Access Mode for SAP Analytics Cloud models
For SAP Analytics Cloud models (analytic and planning), you have the following options:
- Choose Access Mode Values in Master Data to display all members for the dimension (with and without posted data).
- Choose Access Mode Values in Fact Table to display the members with posted data.
- Choose Access Mode Only Values with Posted Data in Current Navigation State to display the members with posted data that are selected in the current navigation state.
- Choose Access Mode Only Values for Existing Attribute Combinations in Master Data to display all members for existing attribute combinations.
## 4.3.2  Filtering measures
With filtering measures, you can define rules to filter the data of your analysis to your current scope of interest. You can apply one or multiple rules to a measure. Depending on where you open the Filter by measure dialog, the filter definition is applied to a selected dimension, to all dimensions or to the most detailed dimension in your analysis. The applied measure filters do not affect totals or subtotals in your analysis.
You can define rules to get a ranked list of data and a list of data matching a threshold value.
In a ranked list filtering, all entries of the currently displayed data are considered and their relationship to each other determines whether an entry is displayed. For example, you can define that the Top 3 entries for a measure should be displayed.
In a threshold list filtering, an entry is filtered independently of the other entries if its value matches the criteria in the defined rule. For example, you can define that all entries are displayed that are greater than a specific threshold value.
Filtering measures is a dynamic action. Whenever you change the view of your data, the filter is applied again. For example, if you add a Top 5 filter, five members are shown in your analysis. If you then add members that you previously removed from the analysis, some of these newly added members could match the Top 5 criteria and replace some of the previously displayed members.
## Related Information
To filter data by measure [page 178]
To change a filter by measure [page 180] To remove a filter by measure [page 181]
## 4.3.2.1 To filter data by measure
## Procedure
1. You have the following options to open the filter dialog:
- Select a cell belonging to a dimension in the crosstab and choose Filter Filter by Measure Edit . If you select this option, the filter is applied to the members of the selected dimension.
- Select a cell belonging to a measure in the crosstab and choose Filter Filter by Measure All Dimensions Independantly Edit .
If you select this option, the filter is applied to all dimensions in the analysis, from the outermost to the most detailed one. For example, in an analysis with three dimensions in columns A, B and C, the filter is first applied to the dimension in column A, then to the dimension in column B and finally to the dimension in column C.
- Select a cell belonging to a measure in the crosstab and choose Filter Filter by Measure Most Detailed Dimension in Rows Edit .
If you select this option, the filter is applied to the most detailed dimension of the rows. For example, in an analysis with three dimensions in columns A, B and C, the filter is applied to the dimension in column C. This option is optimized for threshold value filtering.
- Select a cell belonging to a measure in the crosstab and choose Filter Filter by Measure Most Detailed Dimension in Columns Edit .
If you select this option, the filter is applied to the most detailed dimension of the columns. For example, in an analysis with three dimensions in rows 2, 3and 4, the filter is applied to the dimension in row 4. As with the option above, this option is also recommended for threshold value filtering.
The Filter by Measure dialog box appears.
##  Note
You can also open this dialog box using the context menu on the crosstab or the design panel.
2. In the Based on measure: list, select the measure you want to use.
3. In the Definition area, select the operator and value for a new rule.
The following operators are available: Between, Not Between, Greater Than, Greater Than or Equal to, Less Than, Less Than or Equal to, Equal to, Top N, Bottom N, Top%, Bottom %, Top Sum, Bottom Sum.
##  Note
For SAP HANA data sources the following operators are available: Top N and Bottom N.
4. Press Add .
The new rule is added to the Rules area.
5. If you define multiple rules, select the logic for the rules. Select AND , if all rules should be applied simultaneously, or OR , if only one of the rules should be applied.
6. Press OK .
## Results
The data in the worksheet is filtered according to your selection. The filter is saved with the workbook and is available to other users or in other sessions. You can also open and edit existing filters again.
## Related Information
Operators used for filtering measures [page 179]
To change a filter by measure [page 180]
To remove a filter by measure [page 181]
## 4.3.2.2 Operators used for filtering measures
When defining filters by measure, you can create rules to get a list of data for a defined threshold value and rules to get ranked list of data.
To define threshold values, you can use the following operators:
- Between / Not Between
For these operators, you define a range of values that should be included or excluded in the list.
- Greater Than / Less Than
For these operators, you define a specific value to display members that are greater or less than this value.
- Greater Than or Equal to / Less Than or Equal to For these operators, you define a specific value to display members that are greater or equal to or less or
equal to this value.
- Equal to
For this operator, you define a specific value to display members that are equal to this value.
To define a ranked list of values, you can use the following operators:
- Top N / Bottom N
For these operators, you must enter a whole, positive number as the value. The ranked list is arranged according to the defined number.
For example, if you want a list of the three customers with the strongest sales volume and the five customers with with the weakest sales volume, you create a Top 3 and a Bottom 5 rule in the filter definition.
- Top% / Bottom %
For these operators, you must enter a number between 0 and 100. The ranked list is arranged according to the defined percentage.
For example, if you want a list of the strongest products in revenue until 25% of the total revenue is attained, you create a Top 25% rule for this measure with regard to products.
- Top Sum / Bottom Sum
For these operators, you can also enter a floating point number (15.7 for example). The ranked list is arranged according to the defined total value.
For example, you want a list of products with the highest sales volume, whose combined sales volume totals EUR 20,000. All sales volumes are first sorted in descending order. The totals are calculated until the threshold value of 20,000 is exceeded. If the last value that need to be included to achieve EUR 20,000, causes the total to exceed 20,000, it is also included in the list. All products with a lower sales volume than this product are no longer displayed. Products that exceed the EUR 20,000 threshold remain in the list.
##  Note
Do not use these operators for ranked lists for filtering hiearchies.
## 4.3.2.3 To change a filter by measure
## Procedure
1. Select a cell in the crosstab.
There are four options for defining a filter by measure. To delete an existing filter, you have to select a cell that belongs to the option you used for defining it.
2. Choose Filter Filter by Measure [Selected Filter Option] Edit .
##  Note
You can also use the context menu on the crosstab.
3. In the Rules area, select the Change icon in the rule that you want to change.
4. Change the definition of the rule as required and press the Confirm icon to change the rule.
5. Press OK to apply the changed rule(s) to your data.
## Results
The data in the worksheet is displayed according to the changed filter rule(s).
## 4.3.2.4 To remove a filter by measure
## Procedure
1. Select a cell in the crosstab.
There are four options for defining a filter by measure. To delete an existing filter, you have to select a cell that belongs to the option you used for defining the filter.
2. Choose Filter Filter by measure [Selected Filter Option] Reset .
##  Note
You can also use the context menu on the crosstab.
## Results
The data in the crosstab is displayed without the removed filter.
## 4.3.3  To work with BEx conditions in Analysis
In Analysis, BEx conditions correspond to a filter by measure. In SAP Business Explorer, you can define a condition for a query, for a query view or for a workbook:
- Query conditions are created in BEx Query Designer. In the results area of the query, the data is filtered according to the conditions. You can define multiple conditions for a query. If you insert a query with condtions in Analysis, you can activate and deactivate the conditions as BEx conditions in the menu.
- Conditions that are attached to a workbook are created in the BEx Analyzer. In the BEx Analyzer, you can activate and deactivate conditions. In Analysis, you can activate and deactivate the conditions as BEx conditions in the menu after the workbook conversion.
- Conditions that are attached to a query view can be created with various BEx tools, for example BEx Web Analyzer.
If you insert a query view with condtions in Analysis, some of the conditions can not be recognized as BEx conditions. These conditions are displayed as filter by measure in Analysis. Conditions that are not activated in the query view, are not displayed in Analysis.
In Analysis, you can activate and deactivate the conditions as BEx conditions in the menu or with the Filter by Measure dialog.
## Using BEx conditions in Analysis
You can activate and deactivate BEx conditions:
- To activate a condition, choose Filter BEx Condition and select the condition you want to activate. The displayed data in the crosstab is changed according to the condition.
- To deactivate a condition, choose Filter BEx Condition and select the condition you want to deactivate.
- The displayed data in the crosstab is changed accordingly.
## Using BEx conditions as filter by measure in Analysis
The conditions are displayed as rules in the Filter by Measure dialog. You can activate and deactivate the conditions in Analysis:
- To activate a local condition, choose Filter Filter by Measure Edit . The conditions are displayed in the rules section. Press OK to activate the conditions.
- To deactivate a local condition, choose Filter Filter by Measure Edit and remove all conditions in the rules section that you want to deactivate.
- Press OK to deactivate and delete the conditions. Deleted conditions cannot be activated again.
## Related Information
Filtering measures [page 177]
To convert a BEx workbook [page 40]
The Information tab [page 147]
SAPListOfMeasureFilters [page 93]
## 4.3.4  To show/hide zeros in rows and columns
## Procedure
1. Select a cell in the crosstab.
2. Choose Filter and one of the following options:
- Suppress Zeros in Rows
All rows that only contain zeros are removed.
- Suppress Zeros in Columns
All columns that only contain zeros are removed.
## Results
The zeros are suppressed in rows and/or columns. When zero suppression is active, a check mark is displayed beside the menu item.
##  Note
In the BEx Query Designer, a query can have 2 settings relevant for zero suppression.
The first one is available at the overall query level and configures whether zero suppression is active or not in general. The second one is available at the structure level and specifies the behavior of the structure with regards to zero suppression. Given the 2 settings, there are several configurations that can be set for each axis respectively (especially if there are 2 structures).
In order to simplify the UI, you can only switch on or off zero suppression for each axis in Analysis. Hence, there is only 1 setting with 2 options for each axis available.
Therefore, Analysis is not able to visualize all possible BEx Query configurations in the menu. Depending on the configuration in the Query Designer a second activation of zero suppression could be necessary. After this second activation, zero suppression will be active in Analysis.
For more information, see SAP Note 2366950 .
To restore the hidden rows and columns, choose Filter Suppress Zeros in Rows or Filter Suppress Zeros in Columns again.
You can also define how zeros should be displayed in the crosstab if they are not hidden.
## Related Information
To define the display of zeros and negative values [page 202]