Sorting and Filtering Data in the SAP Analytics Cloud, Add-in for Microsoft Excel

You can open the Set Filter dialog to filter members of a dimension by either selecting the Filter icon in the builder panel or by choosing + Add Filter in the Filter area in the Designer panel.

Filtering opens the Set Filter dialog, where you can:

• Select all members or specific members.
• Exclude a member by choosing the More ... option.
• Sort the selected members using the Table Sort option, where you can apply one of these sort options to the selected members:
  • Default Order
  • Sort Ascending
  • Sort Descending
  • Custom Order: You can define your own order in the Selected Members area that will be applied in the table. You can move the members using the arrow icons or by drag and drop.
  

You can delete the dimension with a filter in the Rows and Columns areas. However, if you select the delete icon there, the dimension is removed from the table, but the filter state remains in the Filter area and is applied as background filter in the table.

If you want to remove the filter entirely, you must delete it from the Filter area.

Using the More Options... icon, you can choose the additional options Display Options and Unbooked Data.

In the Display Options, you can specify whether the members are displayed with their ID, Description, or ID and Description. The option you choose determines if you filter by the ID or Description.

Unbooked members are members for which there is no measure value.

With Unbooked Data, you can show the unbooked members of the dimension to add them to your table. If you have already chosen to show unbooked data for a dimension in the panel, the toggle is switched on when you open the filter dialog, as in the example below.

You can enter strings in the search box in the upper left area of the Set Filter dialog to search for members in the Available Members list. You can then select members in the result list of your search.

You can refine your search by selecting one of the search options: Match Case and Whole Word Only.

To see the complete list of the available members again, delete your search string.

Ranking allows you to isolate the top and bottom records based on different criteria. Select a data cell in the table that belongs to the measure you want to use for ranking and then choose Rank in the ribbon.

In the Create Top N dialog you can define if you want to see the top or bottom values, the number of values and if the direction is in columns or rows.

When using dimensional hierarchies, you can filter on hierarchy nodes as well as members.

In the Filter dialog, select the specific level or levels you want to filter:

• A square in the box indicates that not all children of the parent node are selected.
• A checked box indicates the node is selected.
• A blank box indicates the node is not selected.