5. Filters

Filters are used to constrain the dataset. Filter definitions define

  • how a dataset column should be transformed before it can be filtered 1

  • what canned filter values are possible

  • a user expression parser, if allowed

Footnotes

1

For instance: calculate age when the source dataset only defines a birthdate.

Simply put; they define types of where clauses and how the filter information is to be presented to the end-user.

5.1. General structure

Filters are defined in the configuration directory under filters as yaml files. Consider the following directory structure:

Directory

Description

config/filters

Repository for all filter definitions.

config/filters/name.yaml

A single filter definition.

5.2. Filter definition

A filter is constructed from a filter definition. The following attributes may be used in the filter definition:

  • name – presentation name as displayed to the end-user, and when searching for filters.

  • description – presentation description, used as pop-up and when searching for filters.

  • columnname – filter identifier, should be unique across all filters, used in query composition. Convention is to also use this value as the filename.

  • select – the expression that should be used on the source table to obtain the value to be filtered. If multiple expressions are required, a list of expressions can be used here.

  • filtersOptional any number of key: value statements where key is presented to the user, and value represents the where clause to be used in the actual selection. %t can be used in the where clause to denote the column to be filtered. %t0, %t1 can be used in case of multiple select expressions.

  • userexprOptional function that defines a parser that returns a where clause given a user expression.

  • distinctlistOptional an expression that retrieves all known values. This provides filters should be used instead of those. Typical usecase: get all known doctors.

  • distinctoperatorOptional an operator that is used for the construction of ordering of the found filters after they have been retrieved using the distinctlist. Typical usecase: get a list of known dates, prefixed with a <=.

  • exclusiveOptional a boolean that is use to construct exclusive filters. This enables the creation of NOT(value=x OR value=y) filters instead of the normal (value=x OR value=y) filters. If not set it defaults to False.

  • isarrayOptional a boolean that is used to indicate that the distinctlist will return an array of values that needs to be unpacked. This can be used when a row returns a number of items that need to be put into the filters not as array but as singular values.

  • highlowOptional an two element array that specifies the name for the highest and lowest item to be added to the filters array. This allows users to quickly select the highest or lowest item as the filter condition.

5.2.1. User expressions

If a filter has a userexpr statement, users can add new values to be filtered on simply by typing them in. userexpr then defines the types of user expression possible, by pointing to one these parsers:

  • parsecompnumeric – expressions of the form comparator number. E.g. <32, >= 5.6.

  • parserangenumeric – expressions of ranges in the forms number-number, [number;number[, comparator number or number. Where brackets denote that a range bound is included or excluded. E.g. 1-2, [1;2], [1;3[, >9, 10.

  • parsedutchpostcode – expressions of the form 0123AB where a smaller string or inclusion of the _ or % denotes a like query. E.g. 0123AB, 0123_, 0123%.

5.3. Filter Examples

5.3.1. Filter with userexpr

filter:
  name: Leeftijd
  description: |-
    Leeftijd van de patient (ten tijde van de referentiedatum).
    U kunt hier ook een range intypen zoals 70-80.
  select: extract(year from age(reference_date, patient_birthdate))
  columnname: age
  filters:
    "All": null
    "<30": "%t <30"
    "<40": "%t <40"
    "<50": "%t <50"
    "<60": "%t <60"
    "<70": "%t <70"
    "<80": "%t <80"
    ">80": "%t >80"
    ">70": "%t >70"
    ">60": "%t >60"
    ">50": "%t >50"
    ">40": "%t >40"
    ">30": "%t >30"
    "70-80": "(%t >=70 AND %t <= 80)"
  userexpr: parserangenumeric

Interesting to note here are:

  • Arguments can be quoted when convenient. Filter keys and values are quoted by MGRID in all examples because the lower-than and greater-than sign are special characters in yaml.

  • Arguments may be multiline. One way to indicate that is by inserting |- and then following with an indented text block.

  • filters "All": null makes All in the ui map to no selection, i.e. we want all values possible.

  • a userexpr that shows one of the parse functions that can be used to convert user input to SQL where clauses.

5.3.2. Filter with Distinctlist

filter:
  name: Referentiedatum - Einde Periode
  description: |-
    Met dit filter kan een reeks aan referentiedata geselecteerd worden die in dit geval voor Einde Periode liggen.
    Kan gebruikt worden in een pivot, om meerdere kolommen referentiedata weer te geven en het verloop van een waarde te zien.
  select: reference_date
  columnname: enddate
  distinctlist: reference_date
  distinctoperator: <=
This describes an End date filter that allows an end-user to select all rows

leading up to a particular end date.

Items of interest here:

  • distinctlist retrieves all possible dates from the database, and defines select and filters entries implicitly

  • distinctoperator defines the operator to be used in the filters construction.

5.3.3. Filter with multiple select

filter:
  name: Datum Laatste Consult
  description: |
    Datum laatste consult. Dit betreft het algemene onderzoek genaamd "Consult".
  select:
  - reference_date
  - date_last_consult
  columnname: date_last_consult
  filters:
    "All": null
    "Determined": "%t1 IS NOT NULL"
    "Never": "%t1 IS NULL"
    ">6 months": "((age(%t0, %t1) > '6 month') OR %t1 IS NULL)"
    "<6 months": "age(%t0, %t1) <= '6 month'"
    ">1 year": "((age(%t0, %t1) > '1 year') OR %t1 IS NULL)"
    "<1 year": "age(%t0, %t1) <= '1 year'"
    ">18 months": "((age(%t0, %t1) > '18 month') OR %t1 IS NULL)"
    "<18 months": "age(%t0, %t1) <= '18 month'"
    ">2 years": "((age(%t0, %t1) > '2 year') OR %t1 IS NULL)"
    "<2 years": "age(%t0, %t1) <= '2 year'"
    "Year to date": "date_trunc('year', %t0) = date_trunc('year', %t1)"

This describes a Date last consult filter that allows an end-user to select all rows for patients that have not been seen by their treating physician for some time. Of interest here is the use of the select as a list, because multiple source columns are needed in the filters clauses.