6. Projections

Projections are used to select particular attributes from a previously filtered set, and possibly modify the attributes value. Projections define:

  • the human readable name of a dataset column and its description

  • how a dataset column should be transformed before it can be displayed

  • what aggregation expressions are possible

6.1. General structure

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

Directory

Description

config/projections

Repository for all filter definitions.

config/projections/name.yaml

A single projection definition.

config/projections/aggregates.yaml

Definition of all known aggregates in projections.

6.2. Aggregates definition

The aggregates yaml file defines all possible aggregates that can be applied to projections. These are stated in ordered form, and for each a description is given according to the example below.

6.2.1. Aggregates example

aggregates:
  avg:
    description: average value
  count:
    description: count
  max:
    description: maximum value
  min:
    description: minimum value
  stddev:
    description: standard deviation
  stddev_pop:
    description: standard deviation of population
  stddev_samp:
    description: standard deviation of sample
  sum:
    description: sum
  var_pop:
    description: variance of population
  var_samp:
    description: variance of sample
  variance:
    description: variance

6.3. Projection definition

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

  • id – installation wide unique name for this projection. Convention is to also use this name as the filename.

  • name – presentation name as displayed to the end-user.

  • description – presentation description as displayed to the end-user.

  • aggs – array of aggregates that are available for group and pivot queries using this projection.

  • align – HTML alignment of a projected column. Can be left, right or center.

  • expr – SQL expression that performs the projection. For selecting single column names, this will revert to just the columnname in the database.

  • sortkeyOptional When sorting, what key to sort on instead of the column name.

  • groupexprOptional SQL expression that is performed on the projection before the value is presented to the user. This can be used to unnest if the projected value is an array.

6.4. Projection Examples

Some projection examples:

projection:
  id: preferencedate
  name: Referentiedatum
  description: |-
    De referentiedatum geeft de stand van zaken weer op dat moment.
    Op iedere referentiedatum worden de laatst bekende meetwaarden van de patient berekend.
  aggs:
  - count
  align: center
  expr: reference_date
projection:
  id: ppatientnumber
  name: Patient ID (HIS)
  description: |-
    Het HIS patientnummer van de huisartsenpraktijk zelf.
  aggs:
  - count
  - sum
  - max
  - min
  - var_pop
  - var_samp
  - variance
  - stddev_pop
  - stddev_samp
  - stddev
  align: right
  expr: patient_id_his
projection:
  id: pclusters_diabetes
  name: Organisatie Cluster Diabetes
  description: |-
    Het cluster waar de patient toe behoort voor de diabetes behandeling. De clusters worden bijgehouden in het indicatorenscherm
  aggs:
  - count
  align: left
  expr: array_to_string(clusters_diabetes,',')
  groupexpr: unnest(clusters_diabetes)