Filtering

Filters allow you to limit the scope of data in the view by setting conditions:

Finding Filters

There are several widgets in the ActiveUI SDK that can be used to set filters, follow the dedicated pages to learn more about these widgets:

Widget	Feature
Filters tab of the Content Editor	Provides the most comprehensive tool to control all layers of filters
Page Filters	Allows editing multiple filters applicable to a page
Quick Filters	Provides a convenient way to change a single filter applicable to a page

Continue reading to learn more about the filtering functionality.

Levels of Filters

To control filters on your different views, several levels of filters are available. They apply in the following order, which is from least specific to most specific:

User

Filters set at this level will apply to all dashboards that you open within your application.

User filters are configured via a setting, so they can be configured by user but also by roles. See the settings documentation for technical details.

Note that when opening a dashboard from a bookmark, your user filters will be prepended to the filters defined in the bookmark, without actually modifying the bookmark itself. This allows different users to see the same bookmark through a different prism, without having to duplicate it to change its filters.

Dashboard

Filters set at this level will affect the current dashboard, i.e. all widgets of all pages within this dashboard, after applying user-level filters.

Page

Filters set at this level will affect all widgets within the current page, after applying higher-level filters.

Widget

Filters set at this level will affect the selected widget only. Widget-specific filters will be applied after all higher-level filters have already been applied, unless the widget disinherits higher-level filters.

In the ActiveUI example application, all these filters are controllable in a side panel, as shown in the image below. Note that this might be displayed differently in your custom application. If so, please refer to your development team for more details.

In the below example, the filters are set as follows:

• Parameter Set "BCBS" for all dashboards seen by user "admin",
• Desk "Equities" for all pages of the "Daily Risk" dashboard,
• Risk Class "Equity" for all widgets of the "New Trades" page,
• Top-5 Underlying names by gross data for the selected widget, "Number of trades across desks".

Note: if a dashboard is displaying data from multiple cubes, there will be one set of filters for each cube.

Inherit/Disinherit Filters

From ActiveUI 4.3.0 onwards, it is possible for a widget to "disinherit" the higher-level filters. Then, the widget will display the scope of data limited by its own filters only (if any).

It might be useful when you want to change filters for multiple widgets in a view simultaneously, for instance by using page-level filters or higher, but you want one or more widgets to be insensitive to filter changes.

In the below example, the top right featured value is focused on. You can notice the "disinherit" icon in the widget filters area indicating that the widget is not following the user, dashboard and page filters. For instance, if the Desk filter is changed from Equities to Bonds, all the widgets will be updated, but this one will remain unaffected.

Please note that:

• Clicking on "disinherit" icon for a widget will remove all higher level filters from it and prevent the widget from being affected by any further changes to these filters. After disconnecting, only the filters local to the widget will remain.
• Clicking on "inherit" icon for a widget will apply higher level filters before the widget's own filters. As long as the widget remains connected, any change in the higher level filters are propagated to the widget.

Note: Modifiying a widget's filters so that they no longer obey the higher-level filters defined in the previous sections will result in the widget automatically disinheriting higher-level filters.

Context Values

Advanced users might use the Content Editor and toggle the Query Editor mode to manually edit the query's MDX and context values for the currently selected widget. You will notice that the Context Values tab has exactly the same sections as the Filters: user, dashboard, page, and widget context values. The idea is the same, with only one difference being that if a same context value is defined multiple times, the lower-level one will override the higher-level one.

Features

Default Filters

You may notice that some filters appear 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 the result would not be meaningful), a member must always be defined a for those hierarchies when querying the cube. When none is defined, default members are selected 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

From ActiveUI version 4.2.0 onwards, the Filter Editor displays a reduced list of members if some are irrelevant because of filters already in the view.

You can disable Smart Filtering either globally, or (from ActiveUI 4.2.6 onwards) for specific hierarchies. Please contact your development team if you need to do this.

In the example below, 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, you can see the difference between first selecting the Top-3 underlyings across all desks and then filtering the aggregation result to display the contribution of a specific desk only, or first focusing on a desk and then selecting the Top-3 underlyings for this desk.

Save Filters

Like other widgets, filters can be saved 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 the Submit or Save as button in the bottom right of Filter Editor. This provides the following options:

• Submit: This applies the filter to the View
• Save as: This launches the save bookmark popup
• Save and Submit: Provides a combination of the two mentioned above.

If either Save as or Save and Submit are chosen, the Save Bookmark popup will open. This enables you to provide a name for the filter, select a folder for it, and define its permissions (read more about bookmarks).

Reuse Existing Filter

It can be advantageous to apply saved filters to existing views.

• Saved filters can be dragged into any of the filter sections of the Content Editor, or into a Page Filters widget.
• Saved Quick filters can be added to a dashboard page.

In this example, an analyst is adding a saved Quick Filter into the view to filter for positions under IMA approach:

Filter Editor

When adding/editing a filter in the Page Filters or the Content Editor's Filters you will see the Filter Editor offering you to select from multiple filter types:

For each filter type available via the Filter Editor, this section provides descriptions, illustrations and, for advanced users, MDX samples to draw inspiration from.

You can create multiple filters on the same dimension and use combinations of the filters described below to achieve the desired results.

Members

The 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 deselect>

• Use 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 an a, a z and any characters in between.

• The Exclude selected Members checkbox causes selection inversion.

• You can copy and paste a list of values to select a group of them.

  

  Clicking the Cutter icon launches a popup where you can:

  1. Insert a list of values from the clipboard (for example, values copied from a spreadsheet).
  2. Change the separator, if necessary.
  3. Add a bulk list of members into the selection.

The following sample MDX is generated by the filter that does not Exclude selected members:

SELECT
  /* ... */
  FROM (
    SELECT
    {
      [Geography].[City].[ALL].[AllMember].[London],
      [Geography].[City].[ALL].[AllMember].[New York]
    } ON COLUMNS
    FROM [/* Insert cube name */]
  )

The alternative sample MDX generated by the filter having Exclude selected members enabled is as follows:

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 */]
  )

TopCount

The TopCount filter can be configured by choosing "Top/Bottom Count/Sum/Percent" in the filter editor.

Select {please type how many, e.g. 3} Members of [Selected Dimension] with Highest value of Measure {select measure}.

The following example shows a subquery returning the top five underlyings by Gross EQ Delta.

The sample MDX generated by the TopCount filter is:

SELECT
  /* ... */
  FROM (
    SELECT
    TopCount(
      Filter(
        [Underlying].[Underlying].Levels(
          1
        ).Members,
        NOT IsEmpty(
          [Measures].[Gross EQ Delta]
        )
      ),
      5,
      [Measures].[Gross EQ Delta]
    ) ON COLUMNS
    FROM [/* Insert cube name */]
  )

TopSum

The TopSum filter can be configured by choosing "Top/Bottom Count/Sum/Percent" in the filter editor.

Select all Members of {Selected Dimension} with highest Measure Name whose total reaches {please type amount}

TopPercent

The TopPercent filter can be configured by choosing "Top/Bottom Count/Sum/Percent" in the filter editor.

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... and Value between... are similar and permit selection of members by measure values: Select all [Selected Dimensions] for which {select measures} is {select mathematical expression} {set value}.

This Underlying filter generates the following sample MDX:

SELECT
  /* ... */
  FROM (
    SELECT
    Filter(
      [Underlying].[Underlying].[Underlying].Members,
      [Measures].[Gross EQ Delta] > 1000
    ) 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 Underlying groups whose names are starting with "APS":

This Underlying filter generates the following sample MDX:

SELECT
  /* ... */
  FROM (
    SELECT
    Filter(
      [Underlying].[Underlying].[Underlying].Members,
      Left(
        [Underlying].[Underlying].CurrentMember.MEMBER_CAPTION,
        3
      ) = "APS"
    ) ON COLUMNS
    FROM [/* Insert cube name */]
  )

Another example:

This Trades filter generates the following sample MDX:

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.

This Underlying filter generates the following sample MDX:

SELECT
  /* ... */
  FROM (
    SELECT
    Filter(
      [Underlying].[Underlying].[Underlying].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 the selection of dates using controls such as a Calendar Date Picker. When setting up a filter on any date/time hierarchy, you can choose from date-specific filter types:

Dates

Dates filter allows the picking up of one or more dates from Calendar:

If the hierarchy is slicing and absent from the ROWS and COLUMNS axis, it will only be possible to select one date at a time. To select multiple dates, it will be necessary to add the slicing date hierarchy to the ROWS or COLUMNS axis first.

This HistoricalDates filter generates the following sample MDX:

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 the Calendar Date Picker to be able to select any date in the calendar, not just the ones that are already in the cube:

Date Range

Select all dates in a range.

This HistoricalDates filter generates the following sample MDX:

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 before or after the current date.

This HistoricalDates (Starting/Ending date) filter generates the following sample MDX:

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.

This HistoricalDates filter generates the following sample MDX:

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:

This HistoricalDates filter generates the following sample MDX:

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 */]
  )