Filtering Data

You can filter members of a dimension with the filter dialog or directly on the crosstab. You can also filter members of a hierarchical dimension (hierarchy). Filtering members is a static action. Members that you have filtered remain filtered until you change the filtering criteria. Member filters affect the totals and subtotals in your analysis. Only the values for the displayed members are included in the totals.

You can filter dimension members one of three ways:

• Select the column header containing the dimension/attribute name and choose the desired option from the Filter menu option in the Analysis ribbon.
• Right-click a dimension in the crosstab and use the Filter By Member . . . option in the context menu.
• Drag a dimension to the Background Filters area of the Analysis pane then use the context menu.

Filter By Member opens the Member Selector dialog. It is the primary option for filtering in Analysis for Excel and the only method in Analysis for PowerPoint.

If a dimension is filtered, Select all Members adds all members of the dimension, thus removing the filter for that dimension.

In the Member Selector you can filter members based on key or text and display the members for selection members the same way. You can also filter by attributes.

Explicit Selection allows you to see members you are authorized to see. With it turned off, you see only Not authorized.

The Show Selected button displays only the members you have selected.

Finally, you can paste members from your clipboard or from a text file. When pasting from a text file, each member must either appear on a single line by itself or be separated with a tab, colon, semi-colon, or pipe symbol. You can also use the following symbols:

• ! - Exclude

• \ - Escape character

• * - Wildcard character

For example, a text file containing !DE would exclude Germany from the display of data.

When working with large master data tables, filtering by a range of values avoids loading all master data values for the dialog. In the Filter By Member dialog, simply choose the Range Selection radio button.

You can use operators to define your range, and you can define multiple ranges of member selections.

You can search for specific members using the Member Selector's Search field. You can search for members based on key or text, depending on what you choose in the Display drop-down. You can type specific values or use the * as a wild card character.

When filtering measures, you define rules to filter the data of your analysis to your scope of interest. First, select the measure your filter is based on and then choose the appropriate operator. Be sure to Add the rule. You can apply one or multiple rules to a measure and use and or or for precise filtering.

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.

If your crosstab contains an entire row or column of Zero values, you can hide it.

Depending on where you open the Filter by Measure dialog box, you choose to apply the filter definition to all dimensions independently or to the most detailed dimension in your analysis.

The option All Dimensions Independently provides the most flexibility when analyzing the data in your crosstab because the filter by measure rule applies no matter what dimension or combination of dimensions is in the crosstab.

To illustrate the differences, consider this example. You have a crosstab with the dimensions Division and Sold to party in the rows. Sold to party is to the right of Division. You also have two measures in the columns and have added a filter by measure rule to display only the Top 3 dimensions based on Sales Volume EUR and you apply the filter to All Dimensions Independently.

As a result, the crosstab displays only the top 3 Divisions with their top 3 Sold to Party. If you add another dimension, such as Material to the rows of the crosstab to the right of Sold to party, you will see only 3 materials for each Sold to party.

Choosing Most Detailed Dimension in Rows or Most Detailed Dimension in Columns applies the filter by measure rule only to the dimension closest to the measures in the crosstab. In our previous example, Sold to party is the most detailed dimension in the rows because the measure values are aggregated to that level of detail.

Notice that now there are more than three Divisions in the crosstab but only three Sold to party members for each division. This result is because the measures are aggregated to the Sold to party detail, the Most Detailed Dimension in Rows.

By default the applied filter by measure rules do NOT affect totals or subtotals in your analysis. In other words, even though some measure values do not appear in the crosstab because they have been filtered out, the Overall Result totals and any Result subtotals still include those non-visible values.

Notice the in the following figure that the Sales Volume EUR values for Division 01 total only to 33,692,740.78; however the crosstab displays 75,053,681.94. While accurate because it includes the values for Sold to parties not visible in the crosstab, some analysts may find the 75,053,681.94Result value misleading and confusing.

To display the totals and subtotals so that they reflect only what is visible in the crosstab, change the totals calculation to Sum. Select the column header containing the measure name and choose Totals → Calculate Totals As → Sum.

In Analysis for Office, SAP query conditions correspond to a filter by measure. SAP query conditions can be defined for a query, for a query view, or for a workbook. Conditions for a query are created in SAP Query Designer. If you insert a query with conditions in Analysis for Office, you can activate and deactivate the conditions as SAP query conditions in the Filter menu.

On the Information tab, you can see detailed information about a data source or the complete workbook. You can also find information on filters (including background filters) and variables on this tab. The general information is displayed as text elements. In the Information for drop-down, you can select the complete workbook or one of the inserted data sources. The information fields are displayed for the selected object. You can drag and drop these fields into a blank cell and display the information directly on the worksheet.