Absolutely maddening reporting requirements, I think I'm almost there though - help!

Question

Hi all! A daunting task I've put off forever is here. I need to build some dimensional reporting.

Here's what I need to do:

Extrapolate the following:

Total individual clients served within a quarter, by the specific funding stream (upline from Interactions, Clients may be having multiple cases across multiple funding streams, and the stream is set at the Interaction level)

Total individuals new that fiscal quarter for the fiscal year

Individual client demographics (feeding from Client table)

Individual client case victimization types (feeding from Case table)

Individual clients who received any services across 4 service buckets

And then, the easy part, just a quick-and-dirty count of services by sub-category (about 30 of them or so)

My table structure:

Clients -->

Cases -->

Interactions

> [Interactions Parent]: Funding Stream

> [Interactions Parent]: Service Line

What I have built so far:

Using the "Interactions" table ideally as the "Single Source of Truth" and not pivoting to the parent tables if I don't have to

Interaction table helper fields:

4 "Flag" fields when a record has a qualifying service which falls into each of the 4 service categories

4 conversion helper fields that converts the above flags to a "Related Case - Related Client ID"

Summary Report that counts distinct count across my four "Related Case - Related Client ID" by each of the bucket (pictured below)

Issue I'm running into:

"Distinct Count" wants to count "null" or "" entries as "1"

What's Left to Do:

I need to run that same exercise to pull down Client demographics and Case victimization types, but if I can't get the Interactions summary report to stop counting "null", then I don't know this is the right pathway.

I also have a helper field that rolls up the "Minimum date of service" last quarter to the Client table, and rolls back down as a lookup on the Interactions table, but that doesn't feel right - I need that per funding stream, and that is just going to be unsustainable and sprawling, so I need to think of a better way

I'm a little leery of pipeline building or moving out of my Interactions table to the parent tables, for fear of either 1) developing a sprawling maintenance trap with pipelines, or 2) moving away from the data and baking in a wrong formula.

Conversely, I would absolutely love to connect with anyone willing to give me a few free spare minutes!

Attachments:

Report settings

Current sample report with "null" counting out in the last column"

MelissaDoran37297 · Answer

Hi all — jumping in with a possible workaround for the “Distinct Count counts null as 1” issue.

From what I’ve seen, there aren’t a ton of clean ways to exclude nulls from a Distinct Count at the Interaction level without shifting the logic somewhere else (like filtering, rolling up from parent relationships, or using formula queries — which may get heavy if you’re doing this across a large dataset).

One idea that might be a simple/low-maintenance hack: Create one dummy Interaction record where the fields you’re doing distinct counts on are intentionally null/blank (across the relevant helper fields).

Then in reporting, use Distinct Count minus 1 as your final displayed total (via a summary variable).

Since null is only one distinct value, this effectively removes the “null = 1” inflation without needing to filter out records (and accidentally impacting other counts), or build out additional parent-table rollups.

If anyone’s done something cleaner, I’d love to hear it — but this feels like a pretty contained workaround that keeps the Interactions table as the single source of truth.

Sign up

Login to the community