UCR Examples

This page lists some common examples/design patterns for user configurable reports and CommCare HQ data models.

Data source filters

The following are example filter expressions that are common in data sources.

Filters on forms

The following filters apply to data sources built on top of forms.

Filter by a specific form type using the XMLNS

{
    "type": "boolean_expression",
    "expression": {
        "type": "property_name",
        "property_name": "xmlns"
    },
    "operator": "eq",
    "property_value": "http://openrosa.org/formdesigner/my-registration-form"
}

Filter by a set of form types using the XMLNS

{
    "type": "boolean_expression",
    "expression": {
        "type": "property_name",
        "property_name": "xmlns"
    },
    "operator": "in",
    "property_value": [
        "http://openrosa.org/formdesigner/my-registration-form",
        "http://openrosa.org/formdesigner/my-follow-up-form",
        "http://openrosa.org/formdesigner/my-close-form"
    ]
}

Filters on cases

The following filters apply to data sources built on top of cases.

Filter by a specific case type

{
    "type": "boolean_expression",
    "expression": {
        "type": "property_name",
        "property_name": "type"
    },
    "operator": "eq",
    "property_value": "child"
}

Filter by multiple case types

{
    "type": "boolean_expression",
    "expression": {
        "type": "property_name",
        "property_name": "type"
    },
    "operator": "in",
    "property_value": ["child", "mother"]
}

Filter by only open cases

NOTE: this should be changed to use boolean datatypes once those exist.

{
    "type": "boolean_expression",
    "expression": {
        "type": "property_name",
        "property_name": "closed",
        "datatype": null

    },
    "operator": "eq",
    "property_value": false
}

Data source indicators

Count every contributing row (form or case)

{
    "type": "expression",
    "expression": {
        "type": "constant",
        "constant": 1
    },
    "column_id": "count",
    "datatype": "integer",
    "display_name": "count of forms"
}

Save a form property directly to a table

The following indicator stubs show how to save various properties to a data source. These can be copied directly into data sources or modified to suit specific apps/forms.

Submission date (received on)

This saves the submission date as a date object. If you want to include the time change the datatypes to "datetime".

{
    "type": "expression",
    "expression": {
        "type": "property_name",
        "property_name": "received_on",
        "datatype": "date"
    },
    "display_name": "Submission date",
    "datatype": "date",
    "column_id": "received_on"
}

User ID

{
    "display_name": "User ID",
    "datatype": "string",
    "expression": {
        "type": "property_path",
        "property_path": [
            "form",
            "meta",
            "userID"
        ]
    },
    "is_primary_key": false,
    "transform": {},
    "is_nullable": true,
    "type": "expression",
    "column_id": "user_id"
}

A text or choice property

This is the same type of indicator that should be used for typical Impact 123 indicators. In the example below, the indicator is inside a form group question called “impact123”.

{
    "type": "expression",
    "expression": {
        "type": "property_path",
        "property_path": ["form", "impact123", "cc_impact_1"]
    },
    "column_id": "impact1",
    "display_name": "Impact 1",
    "datatype": "string"
}

Getting the parent case ID from a case

{
    "type": "nested",
    "argument_expression": {
        "type": "array_index",
        "array_expression": {
            "type": "property_name",
            "property_name": "indices"
        },
        "index_expression": {
            "type": "constant",
            "constant": 0
        }
    },
    "value_expression": {
        "type": "property_name",
        "property_name": "referenced_id"
    }
}

Getting the location type from a location doc id

location_id_expression can be any expression that evaluates to a valid location id.

{
    "datatype":"string",
    "type":"expression",
    "expression": {
        "type": "location_type_name",
        "location_id_expression": {
            "type": "property_name",
            "property_name": "_id"
        }
    },
    "column_id": "district"
}

Getting a location’s parent ID

location_id_expression can be any expression that evaluates to a valid location id.

{
    "type":"expression",
    "expression": {
        "type": "location_parent_id",
        "location_id_expression": {
            "type": "property_name",
            "property_name": "location_id"
        }
    },
    "column_id": "parent_location"
}

Base Item Expressions

Emit multiple rows (one per non-empty case property)

In this example we take 3 case properties and save one row per property if it exists.

{
    "type": "iterator",
    "expressions": [
        {
            "type": "property_name",
            "property_name": "p1"
        },
        {
            "type": "property_name",
            "property_name": "p2"
        },
        {
            "type": "property_name",
            "property_name": "p3"
        },
    ],
    "test": {
        "type": "not",
        "filter": {
            "type": "boolean_expression",
            "expression": {
                "type": "identity",
            },
            "operator": "in",
            "property_value": ["", null]
        }
    }
}

Emit multiple rows of complex data

In this example we take 3 case properties and emit the property name along with the value (only if non-empty). Note that the test must also change in this scenario.

{
    "type": "iterator",
    "expressions": [
        {
            "type": "dict",
            "properties": {
                "name": "p1",
                "value": {
                    "type": "property_name",
                    "property_name": "p1"
                }
            }
        },
        {
            "type": "dict",
            "properties": {
                "name": "p2",
                "value": {
                    "type": "property_name",
                    "property_name": "p2"
                }
            }
        },
        {
            "type": "dict",
            "properties": {
                "name": "p3",
                "value": {
                    "type": "property_name",
                    "property_name": "p3"
                }
            }
        }
    ],
    "test": {
        "type": "not",
        "filter": {
            "type": "boolean_expression",
            "expression": {
                "type": "property_name",
                "property_name": "value"
            },
            "operator": "in",
            "property_value": ["", null],
        }
    }
}

Evaluator Examples

Age in years to age in months

In the above example, age_in_years can be replaces with another expression to get the property from the doc

{
    "type": "evaluator",
    "statement": "30.4 * age_in_years",
    "context_variables": {
        "age_in_years": {
            "type": "property_name",
            "property_name": "age"
        }
    }
}

This will lookup the property age and substituite its value in the statement

weight_gain example

{
    "type": "evaluator",
    "statement": "weight_2 - weight_1",
    "context_variables": {
        "weight_1": {
            "type": "property_name",
            "property_name": "weight_at_birth"
        },
        "weight_2": {
            "type": "property_name",
            "property_name": "weight_at_1_year"
        }
    }
}

This will return value of weight_at_1_year - weight_at_birth

diff_seconds example

"expression": {
    "type": "evaluator",
    "statement": "timedelta_to_seconds(time_end - time_start)",
    "context_variables": {
        "time_start": {
            "datatype": "datetime",
            "type": "property_path",
            "property_path": [
                "form",
                "meta",
                "timeStart"
            ]
        },
        "time_end": {
            "datatype": "datetime",
            "type": "property_path",
            "property_path": [
                "form",
                "meta",
                "timeEnd"
            ]
        }
    }
}

This will return the difference in seconds between two times (i.e. start and end of form)

Date format

These examples using Python f-strings to format the dates.

Convert a datetime to a formatted string

2022-01-01T15:32:54.109971Z → 15:32 on 01 Jan 2022

{
    "type": "evaluator",
    "statement": "f'{date:%H:%M on %d %b %Y}'",
    "context_variables": {
        "date": {
            "type": "property_name",
            "property_name": "my_datetime",
            "datatype": "datetime",
        }
    }
}

Convert a datetime to show only 3 digits in the microseconds field

2022-01-01T15:32:54.109971Z → 2022-01-01T15:32:54.110Z

{
    "type": "evaluator",
    "statement": "f'{date:%Y-%m-%dT%H:%M:%S}.' + '%03d' % round(int(f'{date:%f}')/1000) + 'Z'",
    "context_variables": {
        "date": {
            "type": "property_name",
            "property_name": "my_datetime",
            "datatype": "datetime",
        }
    }
}

Getting forms submitted for a case

{
    "type": "get_case_forms",
    "case_id_expression": {
        "type": "property_name",
        "property_name": "case_id"
    }
}

Getting forms submitted from specific forms for a case

{
    "type": "get_case_forms",
    "case_id_expression": {
        "type": "property_name",
        "property_name": "case_id"
    },
    "xmlns": [
        "http://openrosa.org/formdesigner/D8EED5E3-88CD-430E-984F-45F14E76A551",
        "http://openrosa.org/formdesigner/F1B73934-8B70-4CEE-B462-3E4C81F80E4A"
    ]
}

Filter, Map, Reduce, Flatten and Sort expressions

Getting number of forms of a particular type

{
    "type": "reduce_items",
    "items_expression": {
        "type": "filter_items",
        "items_expression": {
         "type": "get_case_forms",
         "case_id_expression": {"type": "property_name", "property_name": "case_id"}
        },
        "filter_expression": {
                   "type": "boolean_expression",
                   "operator": "eq",
                   "expression": {"type": "property_name", "property_name": "xmls"},
                   "property_value": "gmp_xmlns"
        }
    },
    "aggregation_fn": "count"
}

It can be read as reduce(filter(get_case_forms))

Getting latest form property

{
    "type": "nested",
    "argument_expression": {
        "type": "reduce_items",
        "items_expression": {
            "type": "sort_items",
            "items_expression": {
                "type": "filter_items",
                "items_expression": {
                    "type": "get_case_forms",
                    "case_id_expression": {"type": "property_name", "property_name": "case_id"}
                },
                "filter_expression": {
                    "type": "boolean_expression",
                    "operator": "eq",
                    "expression": {"type": "property_name", "property_name": "xmls"},
                    "property_value": "gmp_xmlns"
                }
            },
            "sort_expression": {"type": "property_name", "property_name": "received_on"}
        },
        "aggregation_fn": "last_item"
    },
    "value_expression": {
        "type": "property_name",
        "property_name": "weight"
    }
}

This will return weight form-property on latest gmp form (xmlns is gmp_xmlns).

Report examples

Report filters

Date filter for submission date

This assumes that you have saved a "received_on" column from the form into the data source.

{
  "type": "date",
  "slug": "received_on",
  "field": "received_on",
  "display": "Submission date",
  "required": false
}

Report columns

Creating a date column for months

The following makes a column for a "received_on" data source column that will aggregate by the month received.

{
    "type": "aggregate_date",
    "column_id": "received_on",
    "field": "received_on",
    "display": "Month"
}

Expanded columns

The following snippet creates an expanded column based on a column that contains a fixed number of choices. This is the default column setup used in Impact 123 reports.

{
    "type": "expanded",
    "field": "impact1",
    "column_id": "impact1",
    "display": "impact1"
}

Charts

Impact 123 grouped by date

This assumes a month-based date column and an expanded impact indicator column, as described above.

{
    "y_axis_columns": [
        "impact1-positive",
        "impact1-negative",
        "impact1-unknown"
    ],
    "x_axis_column": "received_on",
    "title": "Impact1 by Submission Date",
    "display_params": {},
    "aggregation_column": null,
    "type": "multibar"
}