Ranet OLAP Analysis Tool provides wide opportunities for filtering the data used for analysis. Using pivot table filters allows end users to create various olap data slices and view them from different perspectives, thus providing a more profound and in-depth analysis of their business processes.
Ranet allows to set up two types of filters in the pivot table:
- Member choice filter. This is a very simple type of filter that allows users to select manually the members of a particular dimension they need to see in the table.
- Filter by condition. A more advanced type of filter that forms the data slice for analysis based on various conditions, functions and logical operations applied to certain characteristics of dimension members.
When applied to the data in the pivot table, both types of filters form an olap cube slice (or subcube) that is then used for MDX query generation and pivot table initialization.
Let’s examine these types of filters in more details.
Member Choice Filter
Member Choice pivot table filter allows the user to slice olap data for multidimensional analysis by manually selecting dimension members that need to be processed.
Users can set up Member Choice filters when designing a new report in the pivot table designer or when modifying settings for a previously saved report. When modifying an old report, certain dimension members used for filtering can be not present in the olap cube’s data anymore. In this case, the system automatically compares the old and new data slices and informs the user if any members are not found in the cube.
Thus, the user knows that certain members will be missing from the analysis results and can update the filter settings accordingly.
Filter by Condition
With the help of filters by condition, the user can create custom data slices based on a certain condition (or a number of conditions). Ranet OLAP provides three types of filters by condition:
- Text filter;
- Value filter;
- Top N filter.
Pivot table text (caption) filters search for text data strings that match certain conditions and include/exclude the data in/from the report on the basis of these conditions. Each dimension member has properties that are specified as strings and, thus, are available for text search: UniqueName, Name, Caption.
Another type of filter by condition is a value filter. Value filters are used to search for data that matches certain conditions in the Measures dimension of the olap cube. It is worth mentioning that value filters cannot be applied to custom calculated members.
The video shows how to use text (caption) and value filters in Ranet OLAP pivot table:
Top N filters are used to search for data containing top and bottom values. The searching conditions are defined by various functions whose input parameters are specified by the user. Filters can search for data based on such conditions as top/bottom count, top/bottom percent from totals, top/bottom sums.
See how to apply Top N filters to a pivot table in Ranet OLAP in the video below:
Some Aspects of Using Pivot Table Filters
There are several aspects that users should remember when using filters in the pivot table.
One of them is that you cannot apply filters by condition to hierarchies, only to hierarchy levels. When used for the whole hierarchy, filter conditions are in fact applied only to the highest hierarchy level members, so the resulting pivot table does not include the data of child members even if it matches the search criteria and should be included in the report.
As already mentioned above, filters by condition also cannot be applied to custom calculated members, which are only subject to filtering via custom calculations editor.
In case the pivot table designer is initialized with an applied data filter as a subcube, it is automatically applied to viewing the metadata tree in the Cube browser, setting the Member Choice Control filter, and to all reports loaded from the report storage. If case the pivot table designer is initialized with an applied data filter as a tuple, the tuple filter settings replace the initial settings in the reports loaded from the report storage.
In case you have any question about using Ranet OLAP pivot table filters - contact us.
