The article “Report generation: more information”, reports need to have filtering criterion to extract information either for a specific case, activities within a given month, etc.

This article provides details on the current implementation.

Database tables

lcm_rep_filter

One table is necessary to store the filters associated to each field of each report:

 id_filter: key for each filter;
 id_report: points to the associated report;
 id_field: points to the field of a given table present in the report (see lcm_fields);
 type: type of filter which is to be applied ("date_lt = date is less than", "date_in = date is in date interval", etc.);
 value: value of the field, if empty it will be requested to the user when the report is run.

lcm_fields

A modification was made to lcm_fields in order to specify what types of filters are available for each field (column “filter”). Values are: none, date, number, text. By default, it is set to “none” meaning that the user cannot use the field to filter the information in the report (to be honest, I don’t know when ’none’ will apply).

One example of how this can be practical: There are some lcm_fields for count(*). A report could use this to find cases which have more than, for example, 50 follow-ups.

lcm_rep_combine

Note: this is not yet implemented.

When there are many filters for a report, the user must specify how the filters should be applied (and / or). Worse, the user might want to do (A or B) and C.

 id_combine: key for each combination rule;
 id_report: points to associated report;
 id_filter1: first filter rule;
 id_filter2: second filter rule;
 id_combine2: instead of combining with a second filter rule, combine with another combine rule;
 type: AND / OR.

In short, the user can either combine two filters, or one filter and a another combination rule.

Example

The above example, (A or B) and C, could therefore be represented in the following (vulgarised) way:

Filters:

Combination rules:

Would allow us to generate a report on cases of Alice or Bob that were create in a month which will be entered as a parameter by the user, when the report is run.

Alternative implementation

The combination stuff will be a user-interface mess. An alternative would be to "assume" that filters are combined with "AND" rules, except for filters that apply to the same field, which would be combined with "OR" rules. But would this lack of flexibility answer most people’s needs?

Combination rules could "override" the default method... hmm..