You can filter DATA so that only the data that you’re interested in is included in your crosstabs and charts. To filter out the data, you apply a condition to one or more rows or columns in the crosstab. If a cell value does not satisfy the filter condition, its entire row or column is removed from the crosstab.
A filter is applied to one or more row members, or one or more column members, but not to both row and column members simultaneously. On an axis that has nested dimensions, the selected members must be on the innermost dimension.
So far, we have CREATED A VERY SIMPLE WORKSPACE and learnt where we can find the elements from our BEx query. In this section, we shall review the different options to filter the data.
From the previous steps, we have a workbook which shows the COUNTRY AND REGION IN THE ROWS and the KEY FIGURES IN THE COLUMNS (see Figure 4.52).
Figure 4.52 Analysis Workspace
The first option to filter the data in your cross tab is to use the Filter by Member option, which allows you to filter a characteristic or a key figure.
Figure 4.53 Filter by member
4. Select the option by Member (see Figure 4.54).
Figure 4.54 Filter by Member
5. You can now select from the list of available members.
Figure 4.55 Display Options
The Display button (see Figure 4.55), in the Filter dialog allows you to configure how the list of available members is being shown. In addition, the Filter by Member dialog allows you to save the filter and enter a specific name for your filter combination. Saved Filter combinations are shown in the Data Panel for the corresponding characteristic, as shown here for characteristic Country (see Figure 4.56).
Figure 4.56 Save Filter
6. For our example, select the countries France, Germany, United Kingdom, and the United States.
7. Click OK.
8. Now select the characteristic Country in the Rows in the Layout panel.
9. Use a right click to open the context menu (see Figure 4.57).
Figure 4.57 Filter by Member
10. Select the menu Filter by Member.
Figure 4.59 Filter by Member
11. Here you can select which of the available key figures should be displayed in the cross tab and you have the option to save the filter (see Figure 4.59).
12. Select the key figures Net Value, Product Costs, and Transport Costs.
13. Click OK.
In addition to the option to filter the data in the cross tab by selecting specific members like we did in the previous steps, you can also use the Filter by measure functionality which allows you to define a condition (to speak in BEx query terms), which is now being called a Filter by Measure.
In the cross tab, we are showing right now characteristic Country and Region with the key figures Net Value, Product Costs, and Transport Costs.
14. Select characteristic Product from the Data Panel and drag and drop characteristic Product to the Rows so that it replaces characteristic Region.
15. Navigate to the tab Display.
16. Select the menu Totals & Parents and ensure the option Display Sub-totals is activated (see Figure 4.60).
17. Select characteristic Product in the Rows of the Layout panel.
Figure 4.60 Totals and Parents
18. Use a right-click and select the menu Filter by Measure (see Figure 4.61).
You will notice that we activated the subtotals before creating the Filter by Measure. The result set of a Filter by Measure rule with subtotals being shown is different to the result set when the subtotals are not shown.
Without the subtotals being part of the cross tab, a Filter by Measure rule is being applied to the complete combined result set and not just to the inner rows. In most scenarios, you want to ensure that the subtotals are shown.
Figure 4.61 Filter by Measure
19. Set the option Based on to the Net Value.
20. Set the option Include Members to Top N.
21. Enter the value 3.
22. Click Add (see Figure 4.62).
Figure 4.62 Filter by Measure
The functionality Filter by Measure in SAP BusinessObjects Analysis, edition for OLAP is the corresponding functionality to a Condition in a BEx Query.
23. Click OK.
Figure 4.63 Analysis Workspace
The option Filter by Measures can only be defined on specific selected characteristics and not like in SAP BUSINESS OBJECTS Analysis, edition for Microsoft Office on the rows or columns dynamically.
In addition to using the menu option Filter by Member you can also drag and drop a characteristic to the Background area in the Layout panel and you will be asked which members should be included in the cross tab. In this section, we have learned about the different options on how to filter the data that is shown in the workspace. In the next set of steps, we will show you how to add data visualizations in form of charts to your workspace.
Ravindra Savaram is a Content Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.