Forum Discussion

ChristineK's avatar
ChristineK
Qrew Captain
5 months ago

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

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"

reports

5 Replies

  • Melissa_Doran's avatar
    Melissa_Doran
    Qrew Cadet
    5 months ago

    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.

    • ChristineK's avatar
      ChristineK
      Qrew Captain
      5 months ago

      Oh that is a great idea, Melissa!  I have to think on the summary variables now - I'm not sure I'll know how to do that just yet. 

  • Jeff_Richey's avatar
    Jeff_Richey
    Qrew Cadet
    5 months ago

    Hi Christine,

    Have you attempted to use filters in the summary report to exclude records with null values? Your screenshot does not include how your filters are configured.

    • ChristineK's avatar
      ChristineK
      Qrew Captain
      5 months ago

      Thanks, Jeff!

      I had been thinking it'd be lovely if that's possible on the summary table level somehow, on the summarized row itself because each will be different, but I suspect it's not.

      I ultimately will need to bring in the following, and "null" across any of these (all different areas) need to just be filtered out so that null isn't a distinct count:

      • These 4 fields right from the Interactions table
      • 11 fields from rom grandparent Client table:
        • Client age group
        • Race
        • Gender
        • If "other" on gender, the brief detail needs to come down where i can get that detail
        • Special classifications (Several fields: Deaf/HOH, Homeless, Immigrants, LGBTQ, Veterans, Cognitive Disabilities, Limited English Proficiency)
      • 26 Victimization Type fields from the parent "Cases" table, including two fields with a detail
  • Melissa_Doran's avatar
    Melissa_Doran
    Qrew Cadet
    5 months ago

    See the attached example of creating the non-null calculation. 

    You would want to name that something that would make sense at the header row of your report for each calculation. 

    Then you would select those at the summarize data section (see second attachment)