Skip to main content

Adding filters

On the previous page, we created a content editor that allows users to select which measure is represented on the map.

There was a problem though…

  • Our code filters the data for the year 2019. Selecting a measure which has no data for 2019 results in an empty map.

From our end-users' perspectives, this looks broken. It would be nice if they could choose which year to filter the map on.

Let's see if we can filter right out of the box. At first we will try it with continents because it is more visual.

  1. Add a pivot table to our dashboard.
  2. In the content editor, give the pivot table the Real GDP per capita (USD).MEAN (same as our map) measure and the Country hierarchy.
  3. Open up the filters editor. It is the second drawer in the left bar.
  4. Add the stock FiltersEditor to pluginWidgetMap.ts:
- import { MdxSelect, parse, WidgetPlugin, CellSetSelection } from "@activeviam/activeui-sdk";+ import { MdxSelect, parse, WidgetPlugin, CellSetSelection, FiltersEditor } from "@activeviam/activeui-sdk";
  Icon: IconWorld,+ filtersEditor: FiltersEditor,  translations: {
  1. Add a Filter on continents to the whole dashboard by dragging the Country hierarchy to the Dashboard section of the wizard.

…And there already seems to be a problem…

Unlike Pivot Table, our map does not respond to the Continents filter.

This is expected. Components access filters via props and interpret them as we tell them to. So far our map hasn't touched the filters at all.

Wouldn't it be nice if there was something that could tell our components how to interpret filters so we didn't have to tell each one how to do it…

Fortunately, there is! Meet withQueryResult!


withQueryResult is a higher order component (HOC). It is a bit like the useQueryResult hook we already met, but with a few extra goodies:

  • It makes the query respond to filters.
  • It makes the query respond to deferred updates.
  • It pauses real time queries of inactive dashboard pages.
  • It catches query errors and displays a message informing the user about the situation.

Let's try it in Map.tsx:

  import React, {-   FC,-   useMemo,    useRef    } from "react";  import {    CellSet,-   stringify,-   useQueryResult,-   WidgetPluginProps,+   withQueryResult } from "@activeviam/activeui-sdk";
- export const Map: FC<WidgetPluginProps<MapWidgetState>> = (props) => {+ export const Map = withQueryResult<MapWidgetState>((props) => {  ...// The withQueryResult HOC knows to expect props.widgetState.query.mdx// It then passes down all the interpreted information in props.queryResult-   const mdx = props.widgetState.query.mdx;-   const stringifiedMdx = useMemo(() => stringify(mdx), [mdx]);-   const { data, error, isLoading } = useQueryResult({-     serverKey: "my-server",-     queryId: props.queryId,-     query: {-       mdx: stringifiedMdx,-     },-   });+   const { data, error, isLoading } = props.queryResult;

withQueryResult needs to know which server the query should be run against. It will look for serverKey on our widgetState.

We need to add serverKey onto the MapWidgetState interface in map.types.ts:

export interface MapWidgetState extends AWidgetState {  query: Query<MdxSelect>;+ serverKey: string;}

In the future, if we want to let users control the target server of their maps, we could develop something in the content editor that lets them update the widgetState's serverKey property.

Every time we update the shape of a widgetState, we also have to update its initial value in the corresponding plugin's initialState property:


import {+ CellSetSelection,  parse,  WidgetPlugin,} from "@activeviam/activeui-sdk";
- export const pluginWidgetMap: WidgetPlugin<MapWidgetState> = {+ export const pluginWidgetMap: WidgetPlugin<MapWidgetState, CellSetSelection> = {    initialState: {      widgetKey,      query: {        mdx: parse<MdxSelect>(`SELECT          Crossjoin(            [Green-growth].[Year].[Year].Members,            [Measures].[Real GDP per capita (USD).MEAN]          ) ON COLUMNS,          [Countries].[Country].[Country_Name].Members ON ROWS          FROM [Green-growth]`),      },+     serverKey: "my-server",    },  };

You probably noticed a mysterious new type: CellSetSelection. You can disregard it for now since we will cover it on the next page.

It's working!

Avoid client-side filters#

What if we try filtering on years now? After all, that was our initial goal.

Let's also add a filter for the Year 2018 to the Page section of the Wizard.

Ughhhh… An empty map! Broken again! 😿

But we definitely have data for Real GDP per Capita in 2018!

So what's going on here?

Let's take a look at how we are retrieving and processing the data for our map.

When we started, we made the (questionable) decision to fetch data for every country AND every year and then filter all that data by 2019 on the client side.

In the gif above, we are asking the server only for 2018 values. Then the Map Component attempts to filter that 2018 data by 2019, which of course yields no result.

We can do better!

First, let's change our initial query in pluginWidgetMap.ts to fetch the results for every country, but not every year:

initialState: {  widgetKey,  query: {    mdx: parse(`SELECT-     Crossjoin(-       [Green-growth].[Year].[Year].Members,-       [Measures].[Real GDP per capita (USD).MEAN]-     ) ON COLUMNS,+     [Measures].[Real GDP per capita (USD).MEAN] ON COLUMNS,      [Countries].[Country].[Country_Name].Members ON ROWS      FROM [Green-growth]`),  },  serverKey: "my-server",},

If we left it like this, the query would initially be implicitly filtered on 1990.

This is because Year is a slicing hierarchy and 1990 is its default member.

However, we can choose a different default by adding a widget filter on the map's initial state, in pluginWidgetMap.ts:

initialState: {  widgetKey,  query: {    mdx: parse<MdxSelect>(`SELECT      [Measures].[Real GDP per capita (USD).MEAN] ON COLUMNS,      [Countries].[Country].[Country_Name].Members ON ROWS      FROM [Green-growth]`),  },+ filters: [parse("[Green-growth].[Year].[2019]")],  serverKey: "my-server",},

Notice that filters are technically bits of MDX. So just like queries, they must be parsed when put into state.

Finally, we can remove the hardcoded client-side filtering from Map.tsx:

const [countries, values]: [string[], number[]] = useMemo(() => {  if (!data) {    return [[], []];  }- const [columnsAxis, rowsAxis] = data.axes;- const numberOfYears = columnsAxis.positions.length;+ const rowsAxis = data.axes[1];  const numberOfCountries = rowsAxis.positions.length;  const valuesForSelectedYear = new Array(numberOfCountries).fill(null);  data.cells.forEach((cell) => {-   const rowIndex = Math.floor(cell.ordinal / numberOfYears);-   const columnIndex = cell.ordinal % numberOfYears;-   const year = columnsAxis.positions[columnIndex][0].captionPath[0];-   // Only display the 2019 values for now.-   if (year === "2019") {+   const rowIndex = cell.ordinal;    valuesForSelectedYear[rowIndex] = cell.value;
  });  return [ => position[0].captionPath[2]),    valuesForSelectedYear,  ];}, [data]);

Congratulations on your perseverance! It paid off and everything is working!

Implement the filtering (optional)#


You can safely skip this section if you want to. The rest of the tutorial will work with or without it.

However, if you continue, you should make a git commit of your work here, so you can easily undo the changes you are about to make.

This is important because the rest of the tutorial's code snippets assume you've stopped here.

As we saw above, the withQueryResult HOC allows us to easily plug a widget into its filters.

But what if we want to control how the filtering is handled. Here we will implement the filtering ourselves instead of using withQueryResult.

This is a good exercise to wrap your head around how these filters can be accessed and used in case you had different usages in mind. (e.g. What if we develop another widget plugin in order to display the flags of the currently selected countries for instance?).

Before we start, let's revert Map.tsx to how it was before we introduced withQueryResult:

  import React, {+   FC,+   useMemo,     useRef    } from "react";  import {    CellSet,+   stringify,+   useQueryResult,+   WidgetPluginProps,-   withQueryResult } from "@activeviam/activeui-sdk";...
- export const Map = withQueryResult<MapWidgetState>((props) => {+ export const Map: FC<WidgetPluginProps<MapWidgetState>> = (props) => {
+   const mdx = props.widgetState.query.mdx;+   const stringifiedMdx = useMemo(() => stringify(mdx), [mdx]);
-   const { data, error, isLoading } = props.queryResult;+   const { data, error, isLoading } = useQueryResult({+     serverKey: "my-server",+     queryId: props.queryId,+     query: {+       mdx: stringifiedMdx,+     },+   });

The DashboardState interface shows that filters exist on three different pieces of state:

  • dashboardState
  • pageState
  • widgetState.

This corresponds to what we see in the filters editor's wizard.

Different filter sections target different sets of widgets:

We can access the widget level of filters directly from our map's widgetState. Let's also see how we can get the filters for the dashboard and page our map is on:

import {+ getPage,+ isWidgetInDashboard,+ setFilters,+ useDataModel,  stringify,  useQueryResult,  WidgetPluginProps,} from "@activeviam/activeui-sdk";
export const Map: FC<WidgetPluginProps<MapWidgetState>> = (props) => {+ const dataModel = useDataModel("my-server");+ const cube = dataModel ? dataModel.catalogs[0].cubes[0] : null;+ const {+   dashboardState = undefined,+   pageKey = undefined,+ } = isWidgetInDashboard(props) ? props : {};
+ const pageState = getPage(dashboardState, pageKey);
  const mdx = props.widgetState.query.mdx;
- const stringifiedMdx = useMemo(() => stringify(mdx), [mdx]);+ const stringifiedMdx = useMemo(() => {+   if (!cube) {+     return "";+  }++   const filteredMdx = setFilters(mdx, {+     filters: [+       ...(dashboardState?.filters ?? []),+       ...(pageState?.filters ?? []),+       ...(props.widgetState.filters ?? []),+     ],+     cube,+   });+   return stringify(filteredMdx);+ }, [+   mdx,+   dashboardState?.filters,+   pageState?.filters,+   props.widgetState.filters,+   cube,+ ]);
  const container = useRef<HTMLDivElement>(null);  const { height, width } = useComponentSize(container);
+ if (!cube) {+   // This should normally not happen.+   // But if it does, then abort mission.+   return null;+ }
  return (    <div      style={{

Great! The filters should still be working now and we did it all by hand!


Notice that props.dashboardState and props.pageKey are not necessarily defined.

If the map widget is used in a dashboard, then the dashboard filters will be given.

But in different contexts (like in an atoti notebook), there will be no dashboard and thus no dashboard filters. Thanks to isWidgetInDashboard, our code is safe either way.

This is it! Now we know how exactly how to access the filters set by the user and how to use them in an MDX query!

On the next page, we will look at ways to make our dashboard more interactive by allowing users to select countries directly on the map and filter other widgets on them.


If you did the optional section, don't forget to revert your code to its pre-optional-section state before continuing.