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.

Solutions:  

• Set a default budget for Budget Amount
• Invert the sign of Income Statement Accounts.

Why this blog series exists:
When you buy an out of the box data warehouse & cube solution like Jet Enterprise from Jet Reports, www.jetreports.com or ZapBI, it usually meets the 80/20 rule (the stock solution satisfies 80% of your reporting requirements, and you'll need further customizations to meet your remaining analytics requirements).  
​Client requests from customers like you inspire this blog series.

Problem:  Some measures require filtering by a default value to show reasonable results.  

Consider the example of the company that creates and revises a new budget each quarter.  While it's perfectly reasonable to show the [Budget Amount] for March 2017 from the 'Initial 2017 Budget' or 'Q1 2017 Amended', it would be misleading to show the sum of both budgets for March 2017.  Similarly, if your organization uses a forecasting tool to calculate expected item consumption or sales, you may have multiple forecasts numbers for the same item and month. 

Solution:  To minimize the risk of 'double counting', we'll modify [Budget Amount] to always filter by a default [Budget Name].

In this solution, we hard-coded a budget into the Default Member as part of the dimension definition.  For improved maintenance, we could add an [IsDefaultBudget] attribute to the [Budget] dimension in our cube and data warehouse.  Then reference the new column when defining the Default Member.  Ideally our source ERP system can identify the current budget, or we can implement business processes and SQL code to auto-identify the correct budget.

Note:  Because we can have multiple concurrent budgets, but shouldn't sum [Budget Amount] across different budgets, the measure is a semi-additive fact -- in Business Intelligence nomenclature semi-additive facts cannot be summed across all dimensions.  Other common examples include balance, profit percent, average unit price, company consolidation, as well as most cost-allocation schemes.  When customizing our data warehouses and cubes, we must be careful where we implement the calculation to avoid reporting errors.

Non-additive facts cannot be summed (ex. [First Invoice Date]).  Fully-additive facts are 'normal' facts which can be summed across all dimensions.

Problem:  Sometimes the dimension used affects how we want to calculate a measure.

In many ERP systems the [Amount] or [Quantity] column have counter-intuitive signs.  Consider G/L Entries which have negative signs for revenue and positive signs for expenses or inventory transactions which show a negative sign for item sales and shipments and positive signs for item purchases.  While the data makes sense to accountants and analysts, business users frequently appreciate seeing signs that intuitively make sense.

In cube-based reports, we'd like to invert the sign on [Amount] for all Income Statement accounts but not Balance Sheet accounts.  Note, it's arguably 'wrong' to invert the sign in the data warehouse because that would make our [Amount] column semi-additive, and potentially cause problems for our auditors!

Solution:  In the cube, use a SCOPE statements to assign a different calculation to [Amount] based on the [GL Account] dimension.

 Note:  in SSAS cube nomenclature we typically deal with two out of three measure types:  Standard and Calculated (avoid Derived measures when possible).

A Standard measure is typically a SUM or COUNT of a column that exists on the fact table in the data warehouse:  ex. sum(Amount) or sum(Quantity).

A Calculated measure is an expression that typically uses standard measures in conjunction with MDX expressions and functions.  Typical examples include division (profit margin percent or avg. daily sales) as well as all business functions (Year To Date, Balance, or  Previous Period). 

A Derived measure is a calculation based on fields from the same fact table -- ex. Profit Margin ([Sales Amount] - [Cost Amount])   When possible, avoid derived measures and use standard measures instead-- just add the calculation as a new column in the data warehouse.  Using standard instead of derived measures has two major advantages:  

1. Users reporting directly against the data warehouse can use the predefined column.
2. The cubes will process faster during their overnight load.

Remember, out of the box solutions by definition are designed to work for ALL customers.  Further customization is expected, even required!  Onyx Reporting is available for development services, training and mentoring as you implement minor (if not major) customizations to your Jet Enterprise implementation.

For clients not using Jet Enterprise, please contact us to find out how data warehouse automation can cut your business intelligence implementation time from weeks and months into days.

One of the biggest frustrations for new Jet Report writers is the dreaded #VALUE error with the accompanying "Empty Filter not allowed"

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.

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".

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

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!

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.

By Jae Myong Wilson -- get more articles like this sent to your email.


Last week as part of a Jet Reports Brainteaser, we explored a common report requirement--grouping transactions by an attribute off the vendor card.  The frequently-used solution would yield an incorrect result.
​

"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"

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.

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!

​At the risk of being the unpopular consultant, I'm going to make a recommendation that no one wants to hear.  Please.  Stop spending money on visualization software.

If you Google "Top 10 BI Software" or go to a trade show, you'll see all sorts of visualization tools that promise to sing, dance, and make your dreams come true.  It'll make dashboards, KPI's, and pretty charts--maybe even warn you if you drop below Safety Stock.  Wait, put the credit card down, and please stop buying visualization software.

While it is fun believing that a shiny new software can solve your data problems, you probably already own a visualization tool that will meet 90% of your reporting and analytics needs!  Don't believe me?  I'll bet if you asked an analyst to give you numbers about <insert your business metric here>, after a few hours, they'd come back to you with an Excel Spreadsheet or Pivot Chart or two.  Turn around and ask the Finance Department for an Income Statement report broken out by Department and Region with measures for Current Year, Previous Year and %Growth.  Again, I bet you get another spreadsheet.  And why not?  No matter how unsexy it seems, Excel is the de facto tool for business analysis, and it will probably meet 90% of your reporting needs.
​
The real question though, is how much sweating did the analyst have to go through to get you those spreadsheets?  If you look at the formulas and see a slew of SUMIF()s and VLOOKUPS() how nervous are you about presenting to the bank, auditors or in meeting rooms?  Only way it could be worse is if you saw a bunch of static numbers!  Truthfully, this is where BI comes to play.  Although our eyes may start glazing over when you start talking about SSIS, ETL, star schemas, and multidimensional models, the fact of the matter is, any visualization tool will perform better when pointed at a well-designed Data Warehouse or Cube.  In fact, if you ask the vendor giving you your Visualization Software demo, I’ll be they’ll tell you that data is coming out of a sanitized data warehouse, cube or data model that emulates a star schema.   If they’re using none of these tools they’re assuming that your ERP contains clean data.  <Insert skeptical snerk here>.

So what exactly is Data Warehousing all about? 
​Well, the Data Warehouse is actually the end of a tumultuous story of data access and extraction from your various source databases, Point of Sales systems, Google Analytics, Bloomberg CSV's, customs spreadsheets, or web-based Census Bureau data).  Once extracted, data undergoes rigorous application of business logic and queries to conform, validate, and clean all the disparate data, then finally a transformation process forges the cleaned data into a Data Model that consists navigable dimensions and fact measurements.  Voila.  Your Data Warehouse.

Sounds dry and possibly unsexy?  Consider the alternative.  Earlier this year I worked with a client who wanted a ranked dashboard that would show the amount and volume of deals that salespersons were bringing through the door.  The first draft of the report took less than an hour to build, but it was immediately sent back for revision.  "That lead conversion number is almost triple our average," I was told.  Further investigation revealed that Status could waffle between Lead and Conversion multiple times based on a combination of factors including: data entry problems, whether the data was collected via web forms or hand written forms, or if specific intermediary statuses were made or missed.  My report needed Olympic-quality gymnastics to extract a reasonable measurement at the cost of report performance.

Suffice to say, what should have been an easy report ended up taking more time than expected because there wasn't one source of clean data, and I loathe to think what would happen if the client wanted the report rolled up by region, or presented in a different layout.  Every client this story of having paid a consultant an ungodly amount of money for a report that we’re only marginally comfortable with and cannot be modified to present the data a slightly different way.  So what’s the solution?  A new visualization tools?  No!  Invest in a clean data warehouse that handles all the data gymnastics for you!  Don't get lured in by a vendor's siren's song of sexy graphics, KPI's and dashboards if you don't have a solid data warehouse already.  Instead, I would recommend looking around for a Data Warehouse automation tool. 

Many of my client have enjoyed success working with the Jet Enterprise data warehouse automation tool, http://jetreports.com/products/jet-enterprise.php.  And I’d be thrilled to work with you to develop data warehouses either from scratch or customize one from the library of pre-built projects.  Once that’s done, THEN we can talk about sexy visualization tools.

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