Sorry it's been so quiet! Been super busy.
Here's a quick 'report doctor' tutorial based on a report sent in by an end user Jorge from my Youtube Channel. He was looking my tutorial on NL(First) and posted in the comments that he was unable to figure out why his report still wasn't working as expected.
After a little poking around, I saw a couple opportunities for improving his report as well as spotted why the report wasn't working as expected.
This video covers:
One of the biggest frustrations for new Jet Report writers is the dreaded #VALUE error with the accompanying "Empty Filter not allowed"
On most days of the week, this problem can be attributed to cell reference errors, in particularly not properly using Absolute, Mixed and Relative references. Well I say, "NEVER AGAIN!"
Follow our best practices outlined in the video below to banish "Empty filter not allowed" errors to the nethers!
That's All Folks!
Like what you saw?
Share with a friend, and subscribe to our bi-monthly newsletter -- don't miss the rest of our Basic Training series.
If you require one-on-one training, a mentor for some reports you're struggling with, or have a grip of reports you needed done yesterday, contact us, we can get started today.
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"
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"
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:
,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"?
,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.
Reporting Detail Information with Data Dumps
Welcome to your Basic Training Series. I put this series of emails together to provide my Jet Essentials clients access to the information you need to get started with reporting! Make sure to try out the practice exercises after you've watched all the videos! If you need help, I'm available at firstname.lastname@example.org.
Don't miss the follow up Basic Training series, sign up for our bi-monthly newsletter.
Detail Level ReportingIn our last email, we looked at using the NL("Table") function in the Jet Table Builder to construct a report that showed row level or detail level information.
Did you know that Excel has a native feature that allows you to pull in row level information from a SQL table? (Excel > Data > From Other Sources > From SQL Server). It's a great feature, but you have to know a little SQL in order to reliably combine data from multiple tables! One of the reasons our clients love Jet Essentials, is that you don't have to be a SQL guru in order to build beautifully formatted reports.
In comparison to Excel's native tool for importing data from your ERP, Jet Essentials makes showing detail level information a piece of cake with the Drag and Drop functionality built into the Jet Browser.
Once you've got the bones of your report in place, you'll want to make sure to use the Jet Function wizard to add filter criteria to your report.
Once you have your data dump and filters in place, you're still not quite done! If you want your users to be able to change the filter criteria for the report, you'll need an Options page. Check out the video here.
Lastly, if you want to get really fancy, you can also use NL("First") to add data from multiple tables to your report, but you can't use Drag and Drop for that, you'll have to get into the Jet Function Wizard.
These videos are designed to get you started and give you just a TASTE of what's possible. If you want to know more about multicompany reporting, scheduling and automatically distributing reports, filtering on data from multiple tables, filtering on calculated fields, or designing reports to audit data entry, I'm just an email away! In fact, if you go to our website, www.onyxreporting.com, you might be able to chat with me right now!