 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:
For more information on NL(rows) and the Record Key, check out this blog.
If you like what you saw, shoot me an email or visit our Services page to see a sampling of the services Onyx Reporting offers.
0 Comments
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. I've written at length about staying Up to Date with the current release of Jet Essentials (now called Jet Professional), but after my last blog reviewing major Jet Reports release 2015, I walked away with mixed feelings. The features and new tools, though beneficial were cumbersome to install or subject to 'gotchas' that if you weren't paying attention, you'd easily miss. I felt myself wishing that I'd written down all the gotchas so I could just email it to all my clients. Well this time I did it for 2017.
In a nutshell:
If you're uncertain whether your organization has Jet Enterprise, send an email to the same team that handles activation codes and licensing: orders@jetreports.com for non-US and orders.us@jetreports.com for US-based clients.  Calling all Jet Reports report writers.
I saw this post on Dynamics User Group from a few years ago. "I need help creating a report in Jet Express [report writing software]. I have the "Age" field in the vendor table and I want to show purchases grouped by age bucket: ex. 10 - 20, 20 - 30 , 30 - 40 and the count of vendors .. need help" Solution Permutations:
Jae Wilson is a Jet Reports Certified Trainer from Data Analytics consultancy Onyx Reporting.
Between now and my previous post --'why Power BI won't replace a Data Warehouse'--which was built on ideas shared in 'why people should stop confusing BI with Visualization', I've done even more thinking on the topic of why clients have unsuccessful or incomplete Business Intelligence implementations. The good news is, it's seldom a case of bad software fit, lack of resources or expertise, so much as uncertainty around what the desired outcome should be. It doesn't matter if your solution is Targit, Crystal, SAP, ZapBI or Jet Enterprise, if the client doesn't start with a clear vision of what they want, no matter what you deliver, it seems, they don't want that!
Ultimately, the root challenge is not a question of functionality or capability so much as whether the user can articulate what they want or need. I just had a conversation with a potential client who lamented, "No one on my team except me really knows what they want." Honestly, that degree of transparency is better than the dreaded "we just want a simple dashboard," but there's still work to be done! It's time to sit down with your users and map out some mock ups of what you want your final reports to look like. Get away from emails and lengthy paragraph long descriptions; instead, use simplified spreadsheets with sample headers, fake data, mock-up graphs, or a list of the KPI's you want to see in your solution. Even an seemingly obvious question like, "How do you want to consume this data, (spreadsheet, pivot table, web page, or mobile device) can have far reaching implications on what Front End software you use to develop your reports! I've delivered more than a handful of dashboard workshops where my opening question"what do you want your dashboard to look like?" was met with dead silence. #FacePalm. If this still seems too daunting, maybe it's time to do a little window shopping--did you know that Jet Reports has over 100+ generic reports available for you to use? I know, "these generic reports don't quite meet your company's unique needs." But that's not our expectation! If you can express what does and doesn't work about a generic report, then we can uncover not only what you want and need, but now you have ideas about what it could look like on paper! A good Jet consultant can customize any of the Report Player reports, or even build a more robust one from scratch with all the features you want! Unfortunately consultants are sometimes slow to promote the Report Player. "Our end users don't like it because XYZ." Trust me, I've heard every possible reason, from both clients and consultants, but I'll shorten my counter-argument to the following: the Report Player inspires users with glimpses of what's possible. At the end of the day, consultants can only deliver what users ask for, and clients can only ask for what they know about or have seen before. I like to think of the Report Player as a mini IKEA catalogue: the pictures are there to whet your appetite. Don't expect completeness; instead, let the report layouts be a source of inspiration. I love it when a client shows me a report and says: "I love it, but can it do ..." Now we can focus the conversation to delivering exactly what you want because you know what you're looking for. Happy Reporting!
For display optimized on mobile and tablet devices, please read the article originally developed at Sway.com: https://sway.com/8EZVD_Pxkwrs7QZW
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. Reporting Detail Information with Data DumpsWelcome 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 jae@onyxreporting.com. 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. 
Use Excel's Data Connection Wizard to pull in data from a SQL datasource.
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! |
RSS Feed
|
Portland, OR
info@onyxreporting.com +1 503.796.2700 |
London, UK
jae@OnyxReporting.com +44 747.426.1224 |
|