Filters
Filters allow you to define a subset of data you would like to explore in a view.
Quick start
Using filters
Most of the time, it is easier to start from an existing view or a dashboard.
- Select a view that fits your interest
- Hover over the local filter panel in the view. To show the filter panel, click the more icon
...
in the right corner of the widget and select "Show Filters".
- Remove a filter to widen the scope. Click
x
in Filters or right click on the column header and select "Remove filter on..."
To add a filter, you have multiple options:
- Right click on value to filter by value.
- Right click on a column header to create a filter on that particular hierarchy
- Click on the
+
button in the filter panel to create a filter on any other hierarchies.
To view/edit a filter, click on the pencil icon of the desired filter and explore its content in the Filter Editor. Filters create MDX expressions that you can be viewed in the Mdx Editor.
Dashboard versus local filters
Local Filters apply to a single widget and are controlled by the filter panel displayed inside the widget.
Dashboard Filters apply to all the widgets of a dashboard and are controlled by a separate and optional widget. Note: filters that are common to all widgets of a dashboard will automatically appear in the "Dashboard Filters" panel.
Here's an example. DeskA filter is common to all widgets, so it is possible to change it by using the "Dashboard Filters" widget. New York City filter exists only in one widget, in this case Pivot Table, so it does not show up in Dashboard Filters.
- If a filter is created using the "Dashboard Filters" panel, it will be applied to all widgets of the dashboard at once.
- If a local filter happens to be identical for all widgets on the screen, the filter will show up in the "Dashboard Filters" panel.
- If a filter, that appears in the "Dashboard Filters" panel, is changed in just one widget via the local filter panel, it is no longer common to all widgets, and will disappear from the "Dashboard Filters" panel.
Dashboard filters are automatically applied to new widgets.
Note: if a dashboard is displaying data from multiple cubes, Dashboard Filters apply individually to each of them. You will see separate sets of Dashboard Filters for each of the cubes.
Default Filters
You may notice some filters show up by default. These are default members of slicing hierarchies. As it is not possible to aggregate data across all members of a slicing hierarchy by design (because it does not make sense), one must always define a member for those hierarchies when querying the cube. When none are defined, default members are picked by the cube and displayed in the filter panel.
For example, cube designers often choose a business date as a slicing dimension. When there is no filter for date and dates are not in the view, measures will return values for the most recent date by default.
Smart filtering
Since ActiveUI version 4.2.0, the filter editor displays a reduced list of members if some members are irrelevant due to filters already existing in the view.
You can choose to disable Smart Filtering either globally, or beginning in ActiveUI 4.2.6, for specific hierarchies. Please contact your dev team if you have such requirements.
In the below illustration, the scope of the members list is limited by the table’s filter on the CounterParty member Mitsubishi Tokyo Financial Group. When creating an additional filter on Desk, the selector for a Members filter only allows you to select from the list of members with facts belonging to the scope of the view. In this case, DeskA is the only member relevant for Mitsubishi Tokyo Financial Group:
Filter order matters
A different filter order may bring different results. Filters are applied one-by-one: a filter is impacted by all the filters above. You can rearrange filters using drag-and-drop to achieve the desired result.
In the example below, the Top-2 names are selected and then filtered for a specific activity, then the data is filtered by activity first and then the Top-2 names are selected.
Save Filters
Filters can be saved like other widgets as bookmarks.
This is particularly useful when a filter contains complex logic and you would like to share that filter with your colleagues.
To save a filter, find "Submit" or "Save as" button in the bottom right of Filter Editor, it allows to switch between different actions:
- Submit. Applies the filter to the View
- Save as. Launches the save bookmark popup
- Save and Submit. The combination of the two mentioned above.
On click "Save as" and "Save and Submit" opens the save bookmark popup, where you can give the filter a name, select a folder, and define its permissions (read more about bookmarks).
Reuse existing filter
It can be very handy to apply saved filters to existing views.
In this example, a logistics analyst is exploring high-level KPIs for a logistics company. They can easily limit the scope by applying pre-saved filters, such as only Rail shipment or only Top-2 Carriers:
Main types of filters
For each filter available via the user interface, this section provides descriptions, illustrations as well as, for advanced users, MDX samples to draw inspiration from.
You may create multiple filters on the same dimension and use combinations of the filters described below to achieve the desired results.
Members
Members filter allows you to choose the exact values you would like to keep:
Click One or More Members to select
Click
x
next to selected Members to deselectUse the search bar to browse the list of members. This will perform an additional query to the backend to dynamically fetch the list of members matching your search. The search supports [Glob patterns](https://en.wikipedia.org/wiki/Glob_(programming) containing a
*
or a?
. Example:a*z
would match any members whose caption contains ana
, az
and any characters in between.The
Exclude selected Members
checkbox allows to invert selectionCopy and paste a list of values to select many of them at once:
Clicking the cutter icon launches a popup where you can:
- Insert a list of values from the clipboard (copied from a spreadsheet, for example)
- Change the separator, if necessary
- Add a bulk list of members into selection
Sample MDX generated by the filter:
SELECT
/* ... */
FROM (
SELECT
{
[Geography].[City].[ALL].[AllMember].[London],
[Geography].[City].[ALL].[AllMember].[New York]
} ON COLUMNS
FROM [/* Insert cube name */]
)
Sample MDX generated by the filter having "Exclude selected members" enabled
SELECT
/* ... */
FROM (
SELECT
Except(
[Geography].[City].[City].Members,
{
[Geography].[City].[ALL].[AllMember].[London],
[Geography].[City].[ALL].[AllMember].[New York]
}
) ON COLUMNS
FROM [/* Insert cube name */]
)
Top/Bottom Count/Sum/Percent
These filters rank Members by a selected measure and pull records accordingly.
TopCount
Select {please type how many, e.g. 3} Members of [Selected Dimension] with Highest value of Measure {select measure}.
Below is an example of a subquery returning top three cities by the count of records.
Sample MDX generated by the filter:
SELECT
/* ... */
FROM (
SELECT
TopCount(
Filter(
[Geography].[City].Levels(
1
).Members,
NOT IsEmpty(
[Measures].[contributors.COUNT]
)
),
3,
[Measures].[contributors.COUNT]
) ON COLUMNS
FROM [/* Insert cube name */]
)
TopSum
Select all Members of {Selected Dimension} with highest Measure Name whose total reaches {please type amount}
TopPercent
Select all members of {Selected Dimension} with the highest {Select Measure} whose total reaches {please type number, e.g. 20}%.
Other types of filters
Value greater, equal or lower...
andValue between...
are similar and allow to select members by measure values: Select all [Selected Dimensions] for which {select measures} is {select mathematical expression} {set value}.
Sample MDX generated by the filter:
SELECT
/* ... */
FROM (
SELECT
Filter(
[Geography].[City].[City].Members,
[Measures].[contributors.COUNT] > 378
) ON COLUMNS
FROM [/* Insert cube name */]
)
Label alphabetically after, before or equal
,Label between
,Label contains... starts with, ends with, contains
are all similar and perform search and comparison of strings.
For example, filter for counterparty groups whose names are starting with "Active":
Sample MDX generated by the filter:
SELECT
/* ... */
FROM (
SELECT
Filter(
[CounterParty].[CounterParty].[CounterPartyGroup].Members,
Left(
[CounterParty].[CounterParty].CurrentMember.MEMBER_CAPTION,
6
) = "Active"
) ON COLUMNS
FROM [/* Insert cube name */]
)
Another example:
Sample MDX generated by the filter:
SELECT
/* ... */
FROM (
SELECT
Filter(
[Trades].[Trades].[TradeId].Members,
[Trades].[Trades].CurrentMember.MEMBER_CAPTION > "138"
) ON COLUMNS
FROM [/* Insert cube name */]
)
IsEmpty or not
allows to select/exclude members for which a measure evaluated against a specific hierarchy returns no values.
Sample MDX generated by the filter:
SELECT
/* ... */
FROM (
SELECT
Filter(
[CounterParty].[CounterParty].[CounterParty].Members,
IsEmpty(
[Measures].[contributors.COUNT]
)
) ON COLUMNS
FROM [/* Insert cube name */]
)
Custom
provides access to the MDX editor. You can check what MDX expression is generated for all of the filter types above. See also Mdx Editor.
Date Filters
Date filters allow you to select dates using controls such as a calendar date picker. When setting up a filter on any date/time hierarchy, choose one of the following filter types:
Dates
Dates filter allows picking up one or more dates from Calendar:
If the hierarchy is slicing and absent from the ROWS and COLUMNS axis, you will only be able to select one date at a time. If you need to select multiple dates, you will need to add the slicing date hierarchy to the ROWS or COLUMNS axis first.
Sample MDX generated by the filter:
SELECT
/* ... */
FROM (
SELECT
Filter(
[Time].[HistoricalDates].[AsOfDate].Members,
IsDate(
[Time].[HistoricalDates].CurrentMember.MemberValue
) AND (
(
Year(
[Time].[HistoricalDates].CurrentMember.MemberValue
) = 2018 and Month(
[Time].[HistoricalDates].CurrentMember.MemberValue
) = 5 and Day(
[Time].[HistoricalDates].CurrentMember.MemberValue
) = 9
)
)
) ON COLUMNS
FROM [/* Insert cube name */]
)
Disable dates with no data
Deselect Disable dates with no data in Calendar date picker to be able to select any date in calendar, not only the ones that exist in the cube now:
Date range
Select all dates in a range.
Sample MDX generated by the filter:
SELECT
/* ... */
FROM (
SELECT
Filter(
[Time].[HistoricalDates].[AsOfDate].Members,
IsDate(
[Time].[HistoricalDates].CurrentMember.MemberValue
) AND (
CDate(
[Time].[HistoricalDates].CurrentMember.MemberValue
) >= CDate(
"2018-05-07"
) AND CDate(
[Time].[HistoricalDates].CurrentMember.MemberValue
) < CDate(
"2018-05-10"
)
)
) ON COLUMNS
FROM [/* Insert cube name */]
)
Starting/Ending date
Select all dates before or after a specific date (selected from a calendar) or now.
Sample MDX generated by the filter:
SELECT
/* ... */
FROM (
SELECT
Filter(
[Time].[HistoricalDates].[AsOfDate].Members,
IsDate(
[Time].[HistoricalDates].CurrentMember.MemberValue
) AND (
CDate(
[Time].[HistoricalDates].CurrentMember.MemberValue
) < CDate(
"2018-05-08"
)
)
) ON COLUMNS
FROM [/* Insert cube name */]
)
Relative dates
Select a date range for the previous months, the current month, the next months; and similarly for years, quarters, weeks, and days. It also enables users to create a year-to-date filter, its inverse; and equivalents for quarters, months, weeks, and days.
Sample MDX generated by the filter:
SELECT
/* ... */
FROM (
SELECT
Filter(
[Time].[HistoricalDates].[AsOfDate].Members,
IsDate(
[Time].[HistoricalDates].CurrentMember.MemberValue
) AND (
DateDiff(
"m",
CDate(
Now(
)
),
[Time].[HistoricalDates].CurrentMember.MemberValue
) = 0
)
) ON COLUMNS
FROM [/* Insert cube name */]
)
Quarter/Month/Day of week
Select all dates belonging to a specific month, quarter or by weekday
For example, all Fridays:
Sample MDX generated by the filter:
SELECT
/* ... */
FROM (
SELECT
Filter(
[Time].[HistoricalDates].[AsOfDate].Members,
IsDate(
[Time].[HistoricalDates].CurrentMember.MemberValue
) AND (
Weekday(
[Time].[HistoricalDates].CurrentMember.MemberValue
) = 5
)
) ON COLUMNS
FROM [/* Insert cube name */]
)
Recent date members
Select the most recent date members or a range of date members. This might prove useful on a date axis where data are generated sporadically.
Example: consider a Business Dates hierarchy where no data will be generated on weekends or holidays. On a regular week, if we are Tuesday and create a filter selecting the "last 2 most recent date members", it will select Friday and Monday rather than Sunday and Monday.
Sample MDX generated by the filter:
SELECT
/* ... */
FROM (
SELECT
Subset(
Order(
[Time].[HistoricalDates].[AsOfDate].Members,
[Time].[HistoricalDates].CurrentMember.MemberValue,
BASC
),
2,
5
) ON COLUMNS
FROM [/* Insert cube name */]
)