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


[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 subdivided in Categories. Consider the following directory structure:

Directory Description
config/filters config/filters/__init__.py Repository for all filter definitions. Defines the order in which categories are considered.
config/filters/category config/filters/category/__init__.py Directory where all filters of a similar category reside. Defines the order in which the local filters are considered.
config/filters/category/item.py A single filter definition.

5.1.1. Category order

Category order is defined in config/filters/__init__.py. An example:

# define categories in presentation order
__all__ = ['date', 'population', 'process']

This defines the categories date, population and process. The BI Explorer expects similar named directories to be present under config/filters/.

5.1.2. Filter order

Filter order is defined in config/filters/category/__init__.py. An example:

from pyramid.i18n import TranslationString as _
# define active filters in presentation order
__all__ = ['age', 'yearofbirth', 'postcode', 'sex']

# define filter catagory name
name = _(u"Population")

This defines the categories age, yearofbirth, postcode and sex. The BI explorer will present the filters in this category as name on the screen in the presentation order defined by __all__. Note that name is defined using an i18n construct to aid in internationalisation.

5.2. Filter definition

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

  • id – filter identifier, should be the same as the filename. Unique across all filters, used in query composition.
  • name – presentation name as displayed to the end-user. Typically constructed using _, i.e. an i18n construct.
  • listorder – a list containing canned values presented to the user. This list only contains i18n strings, not the actual value used in the query.
  • selectexpr – the expression that should be used on the source table to obtain the value to be filtered. If multiple selectexpr are required in your select you can use a list of expressions here.
  • select – a dictionary that maps the listorder values into actual where clauses. %t can be used in the where clause to denote the column to be filtered. If selectexpr defines multiple expressions denote then here using %t followed by their index in the selectexpr list.
  • 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 listorder and select and should be used instead of those. Typical usecase: get all known doctors.
  • distinctoperatorOptional an operator that is used for the construction of the listorder and select 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.

Some filter examples:

Age filter, with:

  • a presentation name as an English default
  • a system wide unique filter id
  • a listorder that defines a presentation of the possible values
  • a selectexpr that shows how the value to be filtered is calculated from a table column
  • a select that shows how listorder fields are mapped to SQL. Note that the All case maps to None, i.e. if we want all values to be possible, we define no SQL filter.
  • a userexpr that shows one of the parse functions that can be used to convert user input to SQL where clauses.
from pyramid.i18n import TranslationString as _
from explorer.parse import parserangenumeric

name = _(u"Age")
id = 'age'

listorder = [ _(u"All"), _(u"<30"), _(u"<40"), _(u"<50"),
              _(u"<60"), _(u"<70"), _(u"<80"),
              _(u">80"), _(u">70"), _(u">60"),
              _(u">50"), _(u">40"), _(u">30"),
              _(u"70-80") ]

selectexpr = '''extract(year from age(reference_date, geboortejaar))'''

select = { _("All") : None,
           _(u"<30"): '%t <30',
           _(u"<40"): '%t <40',
           _(u"<50"): '%t <50',
           _(u"<60"): '%t <60',
           _(u"<70"): '%t <70',
           _(u"<80"): '%t <80',
           _(u">80"): '%t >80',
           _(u">70"): '%t >70',
           _(u">60"): '%t >60',
           _(u">50"): '%t >50',
           _(u">40"): '%t >40',
           _(u">30"): '%t >30',
           _(u"70-80"): '(%t >=70 AND %t <= 80)'

userexpr = parserangenumeric
End date filter that allows an end-user to select all rows leading up to a
particular end date. With:
  • a presentation name as an English default
  • a system wide unique filter id
  • a distinctlist that retrieves all possible dates from the database, and defines listorder and select implicitly
  • a distinctoperator an operator to be used in listorder and select construction.
from pyramid.i18n import TranslationString as _

name = _(u"End Date")
id = 'enddate'
selectexpr = '''reference_date'''
distinctlist = selectexpr
distinctoperator = '<='
Date last yearly checkup 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 selectexpr as a list, because multiple source columns are needed in the select clauses.
from pyramid.i18n import TranslationString as _

name = _(u"Date Last Yearly Check")


listorder = [ _(u"All"),
              _(u">1 year"),
              _(u"<1 year"),
              _(u">18 months"),
              _(u"<18 months"),
              _(u">2 years"),
              _(u"<2 years") ]

selectexpr = ['reference_date', 'datum_laatste_jaarcontrole']

select = { _("All") : None,
           _(u"Determined") : "%t1 IS NOT NULL",
           _(u"Never"): "%t1 IS NULL",
           _(u">1 year"): "((age(%t0, %t1) > '1 year') OR %t1 IS NULL)",
           _(u"<1 year"): "age(%t0, %t1) < '1 year'",
           _(u">18 months"): "((age(%t0, %t1) > '18 month') OR %t1 IS NULL)",
           _(u"<18 months"): "age(%t0, %t1) < '18 month'",
           _(u">2 years"): "((age(%t0, %t1) > '2 year') OR %t1 IS NULL)",
           _(u"<2 years"): "age(%t0, %t1) < '2 year'"

5.3. Filter debugging

When the BI Explorer runs in development mode a number of extra URIs become available:

  • test/filter/names – shows the current defined set of filter id s regardless of category. The filters are refreshed each time this page is accessed. If syntax errors preclude a successful load of a filter this will be displayed also. Each filter is shown as a hyperlink and will trigger a unit test described below.
  • test/filter/run?id – shows for the filter id the result of a run through all defined listorder values.
  • test/table – shows the table definition/structure all queries for this installation work on. This table can be used to determine the columnnames to use for select, selectexpr and distinctlist.