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

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

6 Design Tips for Better Dashboards

6/12/2016

0 Comments

 
Picture
In his article "Choosing the Right Chart for your Data," Brian Petersen (VP of Professional Services at Jet Reports) writes: "Data is the foundation of effective business.  ... Being able to quickly read and analyze your data enables you ... to understand how a particular set or group of facts contributes to your overall success and steer your decisions proactively."
He then goes on to describe several common charts including:
  • Area Charts
  • Bar Charts
  • Scatter Charts and
  • Stacked Column Charts
Read the full article here:

For more seasoned analysts, the challenge is less about finding the right chart so much as laying out a dashboard that effectively communicates a broad scale of both summarized as well as detailed information. As we delve into optimizing dashboards, we move away from technical or domain expertise and transition toward questions of User Experience and User Interface.

For these projects, I'll leverage knowledge gleaned from Stephen Few's guide to dashboarding -- "Information Dashboard Design" which was heavily influenced by Edward Tufte's seminal work "The Visual Display of Quantitative Information".

Charts Jump off the Page with these 6 Tips

1) "Brevity is the soul of wit" -- Do not exceed 1 page.
Any CxO will describe the perfect dashboard as an interactive report where they can see all the important information on one page. Translation: Edit. Edit. Then edit again. Examine how much excessive detail or decoration you can pare away without supplying inadequate context.

​Your final product shouldn't require scrolling, changing tabs, or (ideally) a legend.

2) How good is good? -- Provide enrichment and context through comparison.
  • Same measure at a point in the past (Last Year or Last Month)
  • Target (Budget or Forecast)
  • Relationship to future target or prior prediction (% of annual budget or forecasted budget)
  • Benchmark for norm (company average or industry standard)
  • Separate but related measure
Picture
3) Consider providing non-quantitative data
If you frame dashboards around improving a process or keeping 'two fingers on the pulse of the company'; in addition to measuring activity, it may make sense to provide non-numeric data.
  • Top & bottom performers
  • Prioritized issues to investigate
  • Upcoming due dates.

4) Emphasize the important things.
By understand how the eye travels across the page, designers can highlight, prioritize and de-epmphasize.  This is particularly important when planning the placement of auxiliary elements including filters, slicers, legends and labels.
Picture
5) Maximize the "Data-Ink Ratio"
  • If 'data ink' is any line or pixel that communicates quantitative data (a line, point or bar in a chart) and 'total ink' includes all the lines used to create said chart (axes, tick marks, borders), then the data-ink ratio measures the distribution of ink used for communication versus formatting and decoration.
  • Strive to enhance data ink while reducing and deemphasizing non- data ink.
    1. Can you remove unnecessary tick marks or grid lines?
    2. Do variations in color provide additional meaning, or is the message equally clear with just one or two muted colors?
    3. Do you really need that 3-D effect or color gradients?
Picture
6) Organize information to support interpretation and application
  • Organize groups according to departments, entities or use
  • Co-locate and subtly delineate related data
  • Support meaningful comparison while minimizing meaningless comparisons

My favorite feature of Few's book was his analysis of sample dashboards, wherein he described, not only the flaws in various dashboards but also modeled various alternative ways of presenting the data.  For dashboard developers this analysis would prove invaluable for sharpening our critical eye, but also provide inspiration for what dashboards can (or shouldn't) look like!
Picture

Bridge the Gap between Concept and Execution

For those of you using Pivot Tables or PowerBI to access data from a Jet Enterprise cube, it can be difficult pursue the optimum dashboard layout or chart because you're constrained by the limits of the pivot table or data model.
In a previous post: Better Dashboarding with Cube Functions, Onyx Reporting presents a tutorial for converting Pivot Tables into infinitely more manipulable Cube functions.

One Book to Rule them All

Picture
Content and images from this blog post were taken from Stephen Few's Information Dashboard Design (buy it on Amazon).

Check out our blog at www.onyxreporting.com/blog or sign up for our weekly newsletter: "The Data Dump".

If you need support knocking out a batch of reports or want to customize your Jet Reports cubes to include some new comparative measures, our services team is amoung the best in the business.
0 Comments

Jet Essentials - NL(Filter), NP(Integers) and Dynamic Replication

4/12/2014

0 Comments

 
I was corresponding with a former colleague at Jet Reports US, Steve Little, and he shared this fantastic report.  The general gist of the report is to Print Mailing labels on Avery 5160 paper for Christmas cards!

If you ask any Jet Reports consultant, they'll tell you, writing the report is never the problem.  Formatting it for printing especially multipage printing is a pain!  

Steve uses a classic technique described in summary below:  

Count the number of qualifying records do display and identify how many records to fit per page.
Identify how many Pages to print, then replicate the report with NL("sheets")
On each page, use 'Excel Number Magic' to calcula
te which record to start on.
Then use a series of NL("nth") functions to show the 1st, 2nd, 5th, nth Record.  

It's really quite clever! 

If you're interested in honing your skills for the Jet Certification exams take a look at the use of:
 
NL("Filter") to create an Array of customers

     note the use of  the Filters= keyword to quickly select an array of Filter criteria


NP("Integers") to replicate numbers in the NL("Sheets") function.
    note the use of named cell references 


essentials_-_nav_-_customer_labels_for_avery_5160_labels_-_original.xlsx
File Size: 23 kb
File Type: xlsx
Download File


Steve was so excited about the report, he encouraged me to share it with my customers and fellow bloggers, so feel free to distribute it!

But, be advised, the report would be suitable for printing on one specific type of paper and label size.  

I personally am too lazy to make a version for A4 and A5, so I thought, why not find a dynamic way where the user can identify how many rows they want in their report (as opposed to a fixed 30 records per page).

At the end of it all, it's really just the same report with some fancy Excel math and expanded replication regions which we learned from grouping reports -- NL("rows=6").

essentials_-_nav_-_customer_labels_for_avery_5160_labels_-_finished.xlsx
File Size: 15 kb
File Type: xlsx
Download File


DISCLAIMER:  I am not the originator of this report.  Steve Little and his team at Jet Reports US originally developed this report.  I'm blogging about it to provide a learning opportunity for up and coming consultants and report writers.

If you have any questions about the techniques used, please don't be a stranger.  Email me at Jae@OnyxReporting.com
0 Comments

Jet Essentials - using Excel Named Ranges with NP Array functions

13/11/2014

0 Comments

 
As per request of Joe Little, president of Jet Reports, I've created this new webinar to review using Jet Essentials NP Array functions with Excel Named Ranges.

If you have further questions, don't be a stranger! Shoot me an email.
0 Comments
    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