02.02 Layout - Filters
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 - don't forget the exercises at the bottom!
Filtering the Report Result
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.
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.
The Filter Control
Each filter control looks like the below. The various buttons (invert, member selection and clear) only appear when your mouse moves over the control.
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…
A single wild character can be used in place of a *. For example 4?01 would select 4001, 4101, 4201 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 the 'not equal to' symbol and turn red.
Adding and Removing Filters
Sometimes the filters provided aren’t quite those that are needed. Perhaps:
There are some filters that you no longer want
Whilst there are others that you do but which aren’t currently provided
Adding Filters Directly on the Main Screen
Filters can be Added directly on the main screen, using the “Field Explorer” by right clicking and selecting “Add to Filters” or by dragging them in to the filter area and positioning them more precisely.
The right click menu on the grid, also has an “Add Dimension to Filters” item to quickly allow the designer to add an item that is already on the grid to the list of filters.
They can be Reordered by simply dragging and dropping them to reposition in the filters area.
And unwanted filters can be Removed by right-clicking on the label and selecting “Remove Selection”
Adding / Removing Filters using the Filter Dialog
Alternatively, Filters can be added / ammended using the dedicated "Add / Remove Filters" dialog on the latout tab
Now try the downloadable exercises below:
