Hi everyone. I am new to CommCare and I would like some guidance on how to design a single report using data that currently lives in two different UCRs.
I have two UCRs that work correctly:
-
A household UCR that aggregates household level data such as province, district, locality, community and the number of people in each household (num_patients). This UCR shows exactly one row per household.
-
A patient UCR that contains one row per individual with gender, age, and a reference to the parent household via household_id. This UCR is also working well and produces accurate results when analysed separately.
The challenge is that the client requires a single report that includes indicators derived from both levels. For example:
• Total number of households
• Total population
• Total males and total females
• Distribution of population by sex and age group
• Other demographic indicators that depend on both household aggregation and patient level information
I understand that CommCare Reports can only use one data source at a time. I tried to merge both case types into a single UCR using filters such as type in ["household", "patient"], but the results become inconsistent because each case type has its own logic, different properties and different aggregation rules. As expected, the simple merge approach does not work for mixed case types.
My question is the following:
What is the recommended approach in CommCare for creating a single report that needs indicators from two different case types? Should I:
• Create a parent UCR and a child UCR and then use report filters or joins, if supported?
• Create a single UCR and restructure all columns using conditional expressions so that each row behaves differently depending on the case type?
• Keep both UCRs separated and use a different reporting method?
• Or is this something that should be handled through exported data outside CommCare?
If there is any known pattern or best practice for demographic style indicators that mix household level and individual level metrics, I would appreciate your advice.
Thank you in advance.