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

Report Doctor:  NL(First) and Multi Company Reporting

25/9/2017

0 Comments

 
Picture
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:
  • Proper setup of Filters
  • Understanding NL(First) versus NF() functions
  • Multi Company filtering​
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

Jet Professional Basics - Understanding Excel Cell References

18/2/2017

0 Comments

 
One of the biggest frustrations for new Jet Report writers is the dreaded #VALUE error with the accompanying "Empty Filter not allowed"
Picture
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!"
Picture
​Follow our best practices outlined in the video below to banish "Empty filter not allowed" errors to the nethers!
  • use $C4 for Row Filters
  • use C$4 for Column Filters
  • use $C$4 for Top Left Filters
  • use the F4 key to toggle cell reference modes (our 2nd favorite keyboard shortcut) 

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.
0 Comments

Jet Reports 2017 - Installed & Reviewed

13/9/2016

0 Comments

 
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:
  • The new release has new features that are good and well worth investigating (I am ecstatic about the Web Portal).
  • Installation: still challenging for those of us who like to just click through the installer and riddled with opportunities to mess up (which is why you need this article--learn from my mistakes!)
  • Documentation: better than previous years, but as the product becomes more robust (and therefore complex) users also need to know 'best practices' as well as 'where does this feature apply to me'.
This article will review Jet Professional 2017 (and none of the reporting tools for Jet Enterprise).  If you have Jet Enterprise, this article still applies to you, because Jet Enterprise augments Jet Professional.

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.

Read More
0 Comments

Brain Teaser: Aggregate by Dimension Attribute

10/8/2016

0 Comments

 
Picture
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: 
  • Lvl 1 Solution:  Assume using Jet Express & all data originates from Vendor and Vendor Ledger Entry.  May have to 'mangle' the data post execution.
  • Lvl 2 Solution:  Assume using Jet Professional & all data originates from Vendor and Vendor Ledger Entry.  Can use Pivot tables or other post aggregation methods, but no mangling data post execution.
  • Lvl 3 Solution:  Build solution without using Table Builder and no pivot tables (or similar) for aggregating the data post running the report.

Don't miss the solution!  Sign up for our bi-monthly mailing list or visit www.OnyxReporting.com/blog to access our archived posts.

Jae Wilson is a Jet Reports Certified Trainer from Data Analytics consultancy Onyx Reporting.
0 Comments

My Favorite Things

18/4/2016

0 Comments

 
My Favorite Things--Jae WilsonDocs.com
0 Comments

100 free Jet Reports via the Report Player

8/1/2016

0 Comments

 
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!
The Report Player--Jae WilsonDocs.com
0 Comments

Deep Dive - Filtering on data from multiple tables in Jet Essentials.

25/5/2015

0 Comments

 
For display optimized on mobile and tablet devices, please read the article originally developed at Sway.com:  https://sway.com/8EZVD_Pxkwrs7QZW


0 Comments

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

Jet Essentials Basic Training Series Part 2 of 4

11/3/2015

0 Comments

 

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 jae@onyxreporting.com.

Don't miss the follow up Basic Training series, sign up for our bi-monthly newsletter.
 

Detail Level Reporting

In 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.
Picture
Use Excel's Data Connection Wizard to pull in data from a SQL datasource.
Picture

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.
  • The Jet Function Wizard 13:13 - it's a lot to chew on but this video introduces Jet Essentials in a nutshell.
  • Filtering, Lists, and Totals 3:38 - this will show you how to build a filtered Data Dump from scratch
I've used this technique time and again to build hundreds of reports.  If you need some help getting started, just shoot me an email at Jae@onyxReporting.com.  I've worked with literally hundreds of customers on hundreds of reports including:
  • Trial Balance
  • G/L Detail Reports
  • Sales Shipment documents
  • Customer Information sheets
  • Employee benefits distribution reports
  • Vendor audits
  • Check run reports.
The sky is the limit!  There's some fancy Excel formatting and graphing in the Report pictured below, but this is basically a Data Dump, which you can build using Drag and Drop as described above.
 

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.


Your Next Steps

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!
 

I look forward to hearing from you.

Don't miss the follow up Basic Training series, sign up for our bi-monthly newsletter.

0 Comments

Jet Essentials Basic Training Series Part 1 of 4

11/3/2015

0 Comments

 

Analyzing Data with Excel Tables

Welcome to your Basic Training Series.  I put this series 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.
 

Do you use Tables?

For many of my clients, data analysis starts the same way, regardless of whether they're in Accounting, Sales, AP, AR, or Inventory. First you find the data in your ERP (NAV, GP etc.) then you copy the data into Excel.  From there, analysts will usually use custom formulas to create new measures across columns, or maybe add VLOOKUPs to add custom fields to the table.

Did you know Excel 2007 and later has a feature that allows you to build formatted "Tables" (Excel > Insert > Table) out of the data you copy into Excel?  Older versions of Excel called it a "List".  Advantages of using Excel Tables include the ability to add column filters, provide grand totals and add color formatting.  If you're getting really fancy you can look at adding Slicers and Pivot Charts to create robust Dashboards.
 

Seeing Detail versus Summarizing Data

If you think of copying data straight out of a table in your ERP as a "Data Dump" or detail report, then using Excel's Pivot Table to aggregate by a specific field (ex. total sales grouped by Country) would be a "Summary Report."

Pivot tables may sound intimidating, but they are easy to use. Select your Excel Table, then insert a Pivot Table (Excel > Insert > Tables > Pivot Table).  Now, Just drag and drop field names into a row or column.  Then drop a Numeric field into the Values area to aggregate.  It takes a little practice, but this opens up a wealth of new and dynamic analysis options.

Note: usually the default is to SUM value fields, but you can also COUNT, MIN, MAX and AVG.
Examples of Pivot Tables:
  • See sales by city, by customer, and by item, over specific periods of time
  • See purchases by vendor and item over time
  • See donations by organization, by city by donor over time.
 

Use Jet Essentials' NL("Table") to Automate Reporting in Excel


Analysis against one ERP table
Example: Sales Transactions. You may want to quickly analyze the sales from your Customer table to learn more about sales during a period. You may want to know who the sales reps are, who their customers are, what country the customer is in, and what the sales were for the period. All data is extracted from just one table in your ERP.
  • Check out this video:  Analyzing Data with Table Queries (6:31)


Analysis against multiple ERP tables
Often times, you'll want to extract data from a main table and then add supplementary columns from other tables.  In Excel it's common to use VLOOKUPs to add the supplementary data, but the Table Builder in Jet Essentials can automate that step too!

Example: Analyzing Purchase Transactions. The transaction table usually provides the vendor number but not the vendor name or any additional information about that vendor. In this scenario you would need to access the vendor master table for that information. You may also want to add in columns to make calculations based on data like vendor state or Payment Terms. 

The Jet Essentials Table Builder Wizard makes it easy to build a table by pulling data from other tables.  You can learn everything you need to know about Table Builder in 20 minutes by watching these tutorials!

  • Introduction to Table Builder (6 min)
  • Multi-Table Reports with Table Builder Part I , Part II
  • Understanding "Include Duplicates" Filter (2 min)

 

Your Next Steps

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 improving performance, working with FlowFields and FlowField filters, or just need a refresher on how to make the most of Pivot Tables, 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!
 

I look forward to hearing from you!

Don't miss the follow up Basic Training series, sign up for our bi-monthly newsletter.

0 Comments
<<Previous
    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