Similar to SAP Analytics Cloud Add-In for Microsoft Office, SAP Analysis for Office also provides a Microsoft Excel-based interface for analyzing not only SAP Analytics Cloud data models but also SAP BW and SAP HANA data.
Sort options
You can sort data by values (smallest to largest, and largest to smallest) and members (A to Z, and Z to A). If you sort the values of one measure (for example, sales volume) from smallest to largest, the other cells in your crosstab are also affected by the sorting. The metadata therefore remains correct in the crosstab.
You can also sort a measure based on a dimension. For example, perhaps you want to sort sales volume based on the sales volume values for the most recent calendar year. It helps you to understand your data better as you can visualize and organize different views on the data in your crosstab. By sorting data, you are able to quickly identify the data you need for your decisions.
Filter options
The figure Filtering shows how you can filter in Analysis for Office.
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). Member filters affect the totals and subtotals in your analysis. Only the values for the displayed members are included in the totals.
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.
Filter operations
The following filter operations are available:
Parameters for Filter by Measure
When 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 box, 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.
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 three 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 five 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 five criteria and replace some of the previously displayed members.
