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

Watch this short demonstration to learn how you can display, sort, and filter data in your table using SAP Analytics Cloud, add-in for Microsoft Excel functionality. Explore various options to customize your table and view the data you need.

The Designer panel is where you configure the table.

The Rows and Columns areas are where you can change the table by adding or removing dimensions and measures.

To do so, choose + Add Measures / Dimensions in the Designer panel for rows or columns and select the measures and dimensions you want to add or remove in your analysis.

You can also filter dimensions to specific members in the Filter area.

You can close the Designer panel, but be aware that if you do, the commands in the ribbon are no longer active and you cannot change the table.

You can reopen it by choosing Designer, followed by a refresh to enable the table.

Using the add-in, you can add dimension properties to a table to add more information about a specific dimension.

To add a property of a dimension to your table, choose the context menu and Properties... on the respective dimension. Then select the properties you want to add in the Set Properties dialog.

To add dimension attributes to a table, choose + Add Measures / Dimensions in the Builder panel, expand the dimension, and choose a dimension member.

Dimensional hierarchies are created either in the SAP source systems for the SAP Analytics Cloud model or in the model itself. A dimension can contain multiple hierarchies. In the hierarchy, dimension members are organized in a tree structure, similar to a corporate organizational chart or a family tree.

If a dimension has hierarchies associated with it, then you will see the Hierarchies option in the menu when you right-click the dimension in the builder panel.

From the Set Hierarchy dialog, choose the hierarchy you want to use. The Flat Presentation option removes any hierarchical display of the dimension members.

You can expand or collapse a single node of a hierarchy using the triangle symbols to the left of the node cell.

It is also possible to select a node cell in the table and choose Expand/Collapse.

Refreshing updates the data in the table with the latest data from the data source.

You manually refresh the data in the table at any time by choosing Refresh from the ribbon. Using the dropdown, you can refresh only a specific data source on your worksheet or all data sources.

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.

Variables are parameters of an SAP data source and serve as placeholders for members, currencies, hierarchies, formula elements, and texts. They often render as prompts in the SAP Analytics Cloud, add-in for Microsoft Excel when you insert a model based on such a data source. After providing the variable values, the table is displayed according to the selected values.

To change variable values, open the Prompt dialog from the ribbon.

You can insert multiple models in your workbook and even on the same worksheet. It is possible that each data source will have variables defined. Because of this possibility, the prompt icon has two options available in the drop-down: Prompts for Workbook and Prompts for Table. If you choose Prompts for Workbook, the prompt dialog will display all the prompts for all the data sources used in the workbook.

Watch this short demonstration to see an example of working with variables in the add-in.