If you spend a lot of time looking at reports, you may have noticed that a lot of our reports are Joined Reports. Anytime we want to ask a question like, "How many times have case managers met with each of their clients in the past month?" or "How many of our clients have assessments last quarter?" we use a Joined Report. Any question of the form "How many X were there per client in period Y?" should be answered with a Joined Report. Why? Joined reports allow you to have multiple report types and it's impossible to see this data accurately with a single report type.
Any single report type will not work for the aforementioned issue:
- "Case Records with X": This report is only going to pull Case Records that have some X. So if a case record has no X (like no service entries or no assessments), they won't show up even though these are the people we particularly want to see
- "Case Records with or without X": Seems perfect, right? Nope. If the Case Record has some X from 2 months ago but not last month, they will come up as rows with X in the report. Then when we filter by date, they will disappear, and in the final report they will not appear at all even though these are also people we particularly want to see
- "Case Records": This could work if we have information about X on the Case Record via a Roll-Up Field, but usually that information will not be detailed enough and we need X itself
You have to use two report types
To see everyone and get an accurate understanding what did or didn't happen in period Y, we need to use a Joined Report with at least two report blocks:
- "Case Records": Use filters to only see the cases that matter (e.g. "Open" or "Enrolled")
- "Case Records with X": Filter based on date to see only the period you're curious about. You may also want to a) copy the filters from the other block and b) filter on "Created Date" of the case record so that Case Records created this month don't show up in your report about last month.