The Filter Control

Practically every report you ever run, will have filtering applied to it. A report with no filters applied would display the entire contents of the cube, laid out according to the across and down axes. Given that it is very rare that one wants to look at the entire contents of a cube in a single report, some degree of filtering is likely necessary in the report.

Here is a video, or read below.

In FastClose filter controls can be found in every report.

It is possible to have filters for every dimension and attribute in a report however it is usual to start with filters displayed solely for those dimensions visible in the report. Report designers add additional filters or remove existing ones as required.

Unlike consolidation and splitting which have no effect on the totals in a report, filtering will change the totals.

Many filters will initially appear blank, indicating that there is no filtering going on for that dimension and any member may be displayed. Others, may show a list of members, indicating that only members from that list are to be displayed. It is very common to filter to a specific Year or Month for example.

Filters can also be set as ranges or wildcards.

Range Filters

Ranges are specified using a : symbol. For example, in the period dimension 1,2,3,4,5,6,7,8,9,10,11,12 is equivalent to 1:12

Ranges become particularly useful in dimensions where the list of members may change over time. For example it is not uncommon for the accounts dimension to change slightly as years go by, where new account codes are added to the chart of accounts. Using ranges in this scenario future proofs your report such that as new account codes are introduced, your report automatically picks them up.

Wildcard Filters

A similar idea to ranges but instead uses pattern matching. For example in the account dimension, 4* would include all members starting with a 4, ie: 4, 40, 400, 4000, 4001, 4123 etc…

Inverted Filters

Sometimes it is quicker to specify everything you don’t want to see from a dimension, so it is also possible to invert the filter, so that everything not mentioned in the filter is selected. This is accomplished by clicking on the = button to the left hand side of the filter which will change to read != and turn red.

Filters with Single Selections

Where a filter specifies a single member from a dimension, there is no need to include that dimension in the across or down axis. For example if we are only interested in the month of June, there is little point giving up a column in the down axis to display the word “June”, or place it on every column label in the across axis. We can safely remove it from both the across and down axes and still know that all the data we can see refers to June, since we can see that in the filter.

Filters with Multiple Selections

Where a filter specifies more than one member from a dimension, it is usual for that dimension to be displayed in either the down or across axis of the report so that it is possible to see how numbers are split across the members selected.

However, you don’t have to. If for example we had selected June, July and August in the period dimension but then decide not to show that dimension in either the across or down dimensions – then the numbers we see in each cell in the grid, will be the sum of all data for June, July and August.