Filtering Data

Objectives

After completing this lesson, you will be able to:

  • Filter dimensions
  • Filter measures
  • View filter information

Filter by Member

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.
Three options for filtering dimensions: choose Filter in the Analysis ribbon, right-click on a dimension and use the context menu, drag to the Background Filter area of the Design Panel

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.

Filter by Key or Text

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.

Filter by Member dialog

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.

Filter by Range

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.

Selecting a range of dimension members in the Filter by Member dialog

Search for Filter Values

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.

Search for members in the Filter by Member dialog using the asterisk wild card character

Filter Dimensions

As an analyst, you want to restrict dimension data using the filter options in Analysis. You need to analyze an InfoProvider from your RWB BW system, instead of from the CIA BW/4HANA system.

Task 1:

Filter by Measure

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.

The Filter by Measure rule dialog

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.

Filter option in the Analysis ribbon allows suppressing zeros in rows or columns

All Dimensions Independently

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.

Filter by Measure options: All Dimensions Independently and Most Detailed Dimension in Rows

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.

Crosstab with All Dimensions Independently Filter by Measure rule showing only the top three Divisions and Sold to party

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.

Most Detailed Dimension in the Rows/Columns

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.

Crosstab with Most Detailed Dimension in Rows Filter by Measure rule showing only the top three Sold to party but more than three Divisions

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.

Totals

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.

Crosstab with the Overall Result totaling all measure values, not only the values displayed in the crosstab

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 TotalsCalculate Totals AsSum.

Crosstab with the correct Overall Result

SAP BW Query Conditions

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.

BEx Conditions in the Filter options of the Analysis ribbon

Filter Measures

As an analyst, you want to use the filter by measure options in Analysis. You need to analyze an InfoProvider from your RWB BW system.

Task 1:

Design Panel 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 (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.

The Information tab of the Design Panel

Log in to track your progress & complete quizzes