ONYX REPORTING LTD.
  • Welcome
  • Services
  • Blog
  • YouTube Channel
  • Contact
  • Domo IDEA Exchange
    • Schedule
    • Call for Presenters
  • Welcome
  • Services
  • Blog
  • YouTube Channel
  • Contact
  • Domo IDEA Exchange
    • Schedule
    • Call for Presenters
Search

Hunt for the Missing Account

14/2/2017

0 Comments

 
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.
Picture
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).  
Picture
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!
0 Comments

Your comment will be posted after it is approved.


Leave a Reply.

    Profile Picture Jae Wilson
    View my profile on LinkedIn

    Stay Informed.

    * indicates required

    RSS Feed

    Categories

    All
    Automation
    Basic Training Series
    Business Intelligence
    Connect
    Dashboard
    Data Pipeline
    Data Science
    Domo
    Excel Tricks
    Executive Training & Leadership
    Extract
    Jet Enterprise
    Jet Essentials
    New Release
    NP Function
    Onyx Reporting
    Planning
    Power Pivot
    Python
    Report Writing
    Statistics And Analytics
    TimeXtender
    Visualization

London, UK
jae@OnyxReporting.com
+44 747.426.1224
Jet Reports Certified Trainer Logo
  • Welcome
  • Services
  • Blog
  • YouTube Channel
  • Contact
  • Domo IDEA Exchange
    • Schedule
    • Call for Presenters