Usually the filter matrix and various widgets work together quite seamlessly. If we activate the “Apply filter matrix query“ checkbox, filters from the filter matrix are automatically applied to the search query and hence the data from the data query is filtered accordingly.

But sometimes it’s beneficial (e.g. for performance reasons or to pass it to a stored procedure) to use the selected filter matrix values in a data query directly.

First we have a look how the automatic filter is working under the hood.

We get employee data from a data query called employees. Furthermore we have a filter matrix to filter the table on department. We use the automatic filter matrix search functionality using the “Apply filter matrix query“ checkbox.

The data query itself is a very simple select on the mitarbeiter table.

SELECT * from mitarbeiter

As soon as we start to select values from the filter table, the dashboard requests the data from the webservice using a select request. It transforms the selected values from the filter matrix into the department URL parameter department=IN(Development,Management). This causes the webservice to only return rows with department “Development” or “Management”.

The webservice automatically applies all params it has a column for as a filter. This also applies to params directly added to the “Search query“ field.

Since we know now how the automatic filter mechanism works, we can start using the filter matrix values as a data query param. To do this, we have to extend the data query a wee bit.

SELECT 
  * 
 FROM 
   mitarbeiter
 WHERE
   ${departmentFilter} = '' OR
   ${departmentFilter}::text IS NULL OR
   department = ANY (string_to_array(${departmentFilter}, ','))

We define a data query param called departmentFilter. Later on, we are going to pass the selection from the filter matrix which then is transformed into an array. Using the ANY operator we filter for values contained in the array. In the case of no selected values we get an empty string or NULL and do not apply departmentFilter at all (using the comparison against an empty string / NULL and the OR).

We also have to add the departmentFilter=${filterMatrix.department} param to the table's search query. And we might also remove the “Apply fiter matrix query“ checkbox i f we don’t want to apply other filters.

Please notice that we use departmentFilter as the param name instead of simply department. Using the latter would cause a name clash with the department column. As we learned before, the webservice automatically applies filters for params it finds a column for. Hence it would e.g. filter the data for department = 'Development,Management' which is never true and never returns any rows.

Access