ONYX REPORTING LTD.
  • Welcome
  • Services
  • Blog
  • YouTube Channel
  • Contact
  • Welcome
  • Services
  • Blog
  • YouTube Channel
  • Contact
Search

Fatal Jet Essentials Filtering error

14/4/2015

0 Comments

 
If you ever work with me in trainings, I'm all about arming users with a system of rules and guidelines for building Jet Reports.  Filtering can be one of those tricky areas where oftentimes you don't know you're doing it wrong until it's too late.

Here's a rule:  Never mix OR and AND in the same filter criteria.  
And a second rule to go with it:  Never filter on the same filed twice.

Rule #2 - Never filter on the same field twice

Consider the function:
NL("Rows", "Customer", "Name"
     ,"Name", "A*"
     ,"Name", "*B")

What will Jet Return? All Customers starting with "A" OR ending with "B"?  Or All customers starting with "A" AND ending with "B"?  

Answer:  It doesn't matter.  Don't filter on the same field twice.  If you want A*|*B or A*B then do it in the same filter!

That rule is usually an easy one to follow.  But what about mixing AND and OR?

Rule #1 - Never mix OR and AND in the same filter criteria

Consider the function:
NL("Rows", "Customer", "Name"
     "Name", "A*|B*&<>*Corp*")

Will Jet return all Customers starting with "A" OR "B" that don't contain "Corp"?     
   (A*|B*) & <>*Corp*  ?  

Or all customers starting with "A" OR all customers starting with "B" that don't contain "Corp"? 
   A* | (B*&<>*Corp*)

Answer:  It doesn't matter.  Don't do it!  Theoretically, Jet should honor the order of operations as described by SQL, but even armed with that knowledge we still stand on shaky ground.  I certainly wouldn't stake my career on hoping I got the order right.

Solution:  Use NL("Filter") and Array calculations to get the results you want.  Please review this blog post for review on the technique, but the general gist of it is you'll make two sets of values and then compare the two sets.

If I want:  All Customers starting with "A" OR "B" that don't contain "Corp"? Then i create:


NP("Difference", 
,NL("Filter", "customer", "no.", "name", "A*|B*")
,NL("Filter", "customer, "no.", "name", "<>*Corp*"))


Remember, NP("Difference") says take the values in Array 1 and subtract values in Array 2.


If I want all customers starting with "A" OR all customers starting with "B" that don't contain "Corp"? 


NP("Union", 
,NL("Filter", "customer", "no.", "name", "A*")
,NL("Filter", "customer, "no.", "name", "B*&<>*Corp*"))

Although it may take a few extra seconds to execute, it's impossible to get the wrong result.  At the end of the day I'll have a list of Customer Numbers where all of the Names start with A Or B and none of the names contain Corp.
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
    Domo
    Excel Tricks
    Executive Training & Leadership
    Extract
    Jet Enterprise
    Jet Essentials
    New Release
    NP Function
    Onyx Reporting
    Planning
    Power Pivot
    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