Skip to Content
0
Apr 30, 2020 at 10:10 PM

Report On Records Without a Record in another Query

35 Views

I have a query [Student Details] whose ID field is [Enrolled Student ID].

I have a second query [FAFSA Info] that has the fields [FAFSA Student ID] and [ISIR FAFSA ID]. For each record in [Student Details] their can be zero or more records in [FAFSA Info].

Now, I need a report that lists records in [Student Details] that have no records in [FAFSA Info].

First, I created a merged field [Student ID] containing [Enrolled Student ID] and [FAFSA Student ID]. With [Enrolled Student ID] as the source dimension. Then dropped [Student ID] in as the first column in my report.

Next, I created a variable [FAFSA Count]. It's qualification is 'Measure'. Type is 'Number'. The formula is '=Count([ISIR FAFSA ID] In([FAFSA Student Id]))'

My plan was to next add a filter on [FAFSA Count] = 0. However, [FAFSA Count] is not working. If there is one record in [FAFSA Info] it is correctly returning a count of 1. However, if there are more then one record (for example two records) it is returning 0.

What do I need to do to get this to work?

Thanks

Skye