Hunt for the Missing Account

This blog post is inspired by a true story.

Actually, chances are you've lived it already.  You've got a Jet Report that's supposed to balance, but for some reason, you're just a little bit off.  It's the end of the month, and we know the report 'worked fine' last month, but someone added a new G/L Account, Cost Center, or Item Category Code, and now you're spending hours tweaking things to make it balance. 

Like searching for the last submarine in a drawn out game of Battleship, the process can be tedious if you don't have a methodical search method, so we thought we'd share ours.

To search for missing accounts, we'll use a combination of Jet Reports' NP("Join"), NL("Filter") and NP("Difference").  Check out our 12 minute demo for balancing a trial balance.

The inspiration for this post came from a user seeking to balance distributions from a food bank (see image below).  

At first glance, the problem may seem completely different than a trial balance that won't balance, but actually the resolution process is exactly the same.

At the top, arranged in columns are the filter combinations for each category of distribution. The sum of the 6 column combinations, the [Added] column, 2,286,863, should sum up to the [Direct] column, 2,284,446, but the [Diff] column indicates there is a gap of 2417.

The user needs to tweak the 6 column filter combinations to cover the 2417 worth of excluded distributions.

The Onyx Reporting Solution:

1. Use NL("Filter") to identify all the [Entry No.] that ARE INCLUDED in each column (position them in the Yellow slots).  
2. Furthest to the right, we'll find all the entries that SHOULD be in included -- the [Direct] column.   
3. Use NP(Difference) to identify the missing entries
4. Finally, either use NP("Join") to list out missing Entry No., or pass the results into an NL("Table") so we can see the filter combinations that are missing from our report.

That's All Folks

At Onyx Reporting, we're data people, systematic thinkers, skilled trainers, and most importantly, dedicated to our client's success.  Contact us for support on your next Jet Reports project.

Alternatively, email us your Jet Professional or Jet Enterprise problem, and we may feature the solution in next weeks' blog post!