Filter

“Filter” can be invoked both from the context menu of the header:

and from the context menu of the tab “Filter”:

There are several filtering options here:

In the sections 1 – 4 there are quick filters:

• Top 10 – 10 biggest values;
• Top 50 – 50 biggest values;
• Top 100 – 100 biggest values;
• Top Х – Х biggest values;
• Top 80% – the biggest values whose sum is not less than 80% of the total sum;
• Top Х% – the biggest values whose sum is not less than X% of the total sum;
• Bottom Х – Х bottom values;
• Bottom 20% – the lowest elements whose sum is 20% of the values;
• Bottom Х% – the lowest elements whose sum is X% from the total sum;
• Non-empty values – show all elements except those which have empty values;
• Empty values or 0 – show all elements which are either empty or 0;
• =Х – values equal to X;
• < > Х – values not equal to X;
• > X – values more than X;
• >= X – values more or equal to X;
• < X – values less than X;
• <= X – values less or equal to X;
• X <= value <=Y – values in the range from X to Y.

If you select “Custom Condition…” you will get a window which allows to select the filtering condition and the arguments:

The context menu in section 6 allows to switch on/off all filtering at once.

It is worth to look at item “Add Filter of Rows (Expert Mode)…”:

There will be a form for setting up a filter:

This dialog allows not only to select the conditions for filtering and the argument, but also the set of columns to which it is applied:

• All visible columns
• Selected columns

Besides, the condition can be met for:

• For the sum of columns;
• For at least one of the columns which are considered (see below);
• For each column, of those which are considered (see below).

Let us look at several examples.

Let us assume we want to show only 5 subcategories of goods which were best sold in 2017. Let us compile the report as on the picture below:

Let us invoke the context menu for the header CY 2017 and select the item “Filter / Add Filter of Rows (Expert Mode)…”:

There will be a dialog for filter settings on the screen:

The selected function “X topmost members (TopCount)” is exactly what we need. In the Columns section it is said “For the sum of columns”, but we have just one column, so the filtering will be done for the year of 2017.  Press «OK» and look at the report:

There is an icon on the tab Filter that shows us that there is a new filter there. If we look at this tab, there is a filter with a name “Filter 1”. The table contains exactly 5 categories that are sold best in the year 2017.

Let us make the task more complicated. Assume we want to see those 5 products which are best sold in the sum of years of 2017 and 2018. Click on this icon:

and do the following actions there:

In this window we have to select “For the sum of columns”, then click on the member selector for date dimension and select CY 2017 and CY 2018 by first pressing “Refresh Children”:

After pressing “OK” the filed for Date dimension will look like this:

This means that several members are selected. If you press “OK” the report will look like this:

Only those product categories which are sold best by the sum of two columns will be displayed.

Filters can be switched on and off:

The selected check box means that the filter is switched on. If you switch it off, it will remain in the report configuration but the filter won’t be active, so that the rows will not be filtered.

Using the previous example let us set up the following parameters:

The hierarchy “Date/Date.Calendar:(Multiple Selection)” contains 2017 and 2018 as before. We will get:

Here we see the OR-principle: the row will remain in table if one of the columns meets the condition.

If we filter using the parameters below:

We will get the following result:

It is obvious that the last element was removed since it did not meet the condition by two years simultaneously (the AND principle).

Attention!

Filters are the part of page structure. If you create filters on the administrator’s page and close the report, all those filters will disappear. If you wish to keep your filters, make a copy of the page and save it.