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:
If you're using the [Inventory Turnover] report from the Jet Reports, Report Player you may be drawing the wrong conclusions because THE REPORT HAS AN ERROR IN IT.
If you use the [Location Code] slicer, you may assume that you're looking at the inventory from THAT location; however, due to the design of the report, you're actually looking at inventory across ALL locations.
Easy solution? Recreate the report in Jet Enterprise.
At 40+ minutes, the tutorial is a mini-consulting session! But if you're strapped for time, I've included hyperlinks that function as chapter markers.
Use Excel's GetPivotData function: https://youtu.be/h2Si6xHmWbw?t=262
Show Calculated Fields in Pivot Tables: https://youtu.be/h2Si6xHmWbw?t=604
The Inventory Turnover Report IS WRONG: https://youtu.be/h2Si6xHmWbw?t=781
Building KPIs in Jet Data Manager: https://youtu.be/h2Si6xHmWbw?t=1107
Visualizing your cubes in Excel #PivotTables: https://youtu.be/h2Si6xHmWbw?t=1712
When Rollups make your KPIs 'wrong': https://youtu.be/h2Si6xHmWbw?t=1798
Visualizing your data in PowerBI: https://youtu.be/h2Si6xHmWbw?t=2293
If this is all greek to you and you want someone to review your existing Jet Enterprise implementation (there's a new update to Jet Data Manager) , give me a holler at firstname.lastname@example.org or shoot us a message through the website. Our team is available for training, development services and support remotely as well as onsite!
The new update to Jet Data Manager is finally here and Harry L., the lead KB article writer over at Jet Reports, www.jetreports.com, has been super prolific in documenting the updates! Side bar: the Change Log hasn't been updated yet, but I'll post about it as soon as it does.
If you're on an older build of JDM (anything less than 17.5) today is the day to update because the features and performance improvements are HUGE.
There are a grip of new KB articles but I'll just share the ones I think most relevant to me and my Jet Enterprise clients:
If most of these articles are greek to you, that's fine, give me a holler at Jae@OnyxReporting.com, or open a chat window and I'd be more than happy to give a free assessment as to whether the upgrade to JDM 17.5 is appropriate for you!
One of the selling points of Jet Enterprise / TimeXtender is how easy it is to create cubes that allow you to quickly and flexibly analyze data that rolls up by hierarchies (think [Sales] rolled up by [Date YQMD] or by [Customer] > [State] > [Region].
Traditional Business Intelligence developers and SQL DBAs know and recognize this as 'User-Defined Hierarchies' traditionally implemented in Visual Studio or SQL Data Tools. Furthermore, your developer probably knows a handful of ways to optimize SSAS performance which can / should be duplicated in Jet Enterprise.
This tutorial shows how to analyze your cube for user-hierarchy optimization opportunities and then how to implement them in JDM via attribute relations.
For an in-depth introduction to attribute relations in SSAS multidimensional, check out, MS SQL Tips.
After my last video and blog post on 5 ways to sort Jet Reports, Mario from Jet Reports Spain asked for a tutorial on the "sort on sum" technique. So I made one!
Full disclosure. This is not an easy technique! It requires a little knowledge about using double quotes, ", in Excel strings, as well as adding calculated fields inside of Jet replicators using NF functions. It may seem a little confusing, but if you give me 40 minutes of your attention, hopefully it'll all make sense.
Fair Warning: Only use calculated fields in your report if you absolutely need it (because it reduces report performance).
Like what you saw?
Have reports that require construction?
Give us a holler.
If your Jet Report has ever suddenly stopped working ("it was fine and all of a sudden...") many times it can be attributed to behaviors that can cause spreadsheet corruption. The most common culprit? Changing the structure of the spread sheet ex.
In the follow 45 minute tutorial, I demonstrate 5 alternatives that will NOT corrupt your workbook.
Warning: Shameless Plug.
This presentation was inspired by a client who emailed me just before the weekend. Feel free to pass your questions along, who knows, you may get an extended tutorial out of it!
Alternatively, if you need onsite training and/or a bunch of reports developed, we do have a summer sale for onsite days with additional discounts for non profit organizations.
Interested? Give us a holler.
This tutorial takes a Jet Professional report and shows how to implement time-based performance metrics in the Jet Enterprise standard NAV project.
In case you missed the original post, I've also included the video of the mentoring session I did for the original Jet Professional Report.
In the tutorial I review using the Company= argument for multi-company reporting, and include information about report optimization using Data Dumps / Record Keys instead of a slew of NL("First") functions.
Hope you enjoy!
This tutorial highlights 3 ways of visualizing data using the Jet Reports product suite.
This tutorial will show you:
This year I, Jae Wilson, had the pleasure of representing Onyx Reporting at CollisionConf 2017, the largest growing tech conference in the United States. While this blog is usually reserved for business intelligence related topics, I did want to share some tweet-able things I heard at Collision.
"What is the half life of data?" How rapidly does the value of your data decay, and what systems are you putting in place to capture and analyze it?
IOT-ready platforms (internet of things) and real-time analytics may not be high priority concerns for your organization, but all companies regardless of industry or size should be examining how they acquire, analyze and act on data.
Jet Reports, PowerBI, and TimeXtender are platforms that can accelerate data acquisition, integration, and analysis. If you need support with implementation, Onyx Reporting has a summer sale for services, training and upgrades.
"Allow your audience to see themselves in your shoes."
"Keep asking Who The F$@K cares about your service."
These two nuggets of wisdom jumped out at me because I regularly ask my clients "why do your customers stay with you?" and I'm equally regularly surprised by the answers.
"We offer high-quality and personalized customer service which our loyal customers love," my client told me. Curiously, as far as I know, they haven't implemented any customer satisfaction metrics, nor do they analyze why customers churn.
Thought for the Day
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.
Set a Default Value for Budget Amount
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.
Invert the sign of Income Statement Accounts
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:
That's All Folks
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.