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

Optimizing User Defined Hierarchies in Jet Data Manager (Jet Enterprise)

13/8/2017

0 Comments

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

The Elusive "Sort on Sum"

28/6/2017

0 Comments

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

5 Ways to Sort Jet Reports

12/6/2017

1 Comment

 
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.
  • Inserting Columns or Rows 
  • Moving Columns or Rows around OR
  • CHANGING THE SORT ORDER OF YOUR REPORT!
Picture
In the follow 45 minute tutorial, I demonstrate 5 alternatives that will NOT corrupt your workbook.
  • Values Only Workbook (scheduler)
  • Table Builder and NL("table") functions
  • Applying a basic sort
  • Grouping
  • Implementing dynamic sorting

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.
Picture
1 Comment

June 05th, 2017

5/6/2017

0 Comments

 
This tutorial takes a Jet Professional report and shows how to implement time-based performance metrics in the Jet Enterprise standard NAV project.

Elements covered:
  • Creating a custom reference table (Holiday Schedule).  Alternatives to a Custom Table include importing Excel Spreadsheets, Access databases or other SQL sources (like DQS).
  • Customizing the Date Dimension with new attributres (the [Is Holiday] attribute) using a SQL CASE statement.
  • On Time Delivery metrics ([Num Days Act] and [Num Days Working]) on the Sales Shipment Header using SQL DateDiff and custom SQL Script Actions.
  • Implementing core business logic in the Data Warehouse (SLA (service level agreement) analytics)
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!
0 Comments

Video Tutorial:  3 ways to visualize data in Jet Professional, Pivot Tables and PowerBI

24/5/2017

0 Comments

 
This tutorial highlights 3 ways of visualizing data using the Jet Reports product suite.
  • Jet Professional (the Excel add-on) pointed directly at NAV
  • Pivot Table pointed at a cube built using Jet Enterprise
  • PowerBI pointed at Jet Enterprise cube using the Direct Connector.

This tutorial will show you:
  • How to add custom Calculated Measures (like [Revenue Amount] and [Amount as a Percent of Revenue]) to your Jet Enterprise cubes
  • How to visualize different time periods in the same pivot table (Month and YTD).
  • How to use interactions and slicers in PowerBI to simultaneously filter multiple visualizations in the same dashboard.
0 Comments

3 lessons I learned at CollisionConf 2017 that can change your company.

6/5/2017

0 Comments

 
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.
Picture
"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.  
  • Could you convert or retain more customers by automating business processes?  
  • Could you reduce loss or waste with better analytics?
  • Would you make different staffing or scheduling decisions with more regularly updated reporting?  

​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.
Picture
"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.
  • What assumptions are you making about your customers?
  • Is there data that can validate your assumptions? 

Thought for the Day

Picture
0 Comments

Data Warehouse and Cubes Tips and Tricks - March 2017

19/3/2017

0 Comments

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

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:  
  1. Users reporting directly against the data warehouse can use the predefined column.
  2. The cubes will process faster during their overnight load.

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

Jet Enterprise & TimeXtender:  Add Dynamics NAV drillthrough actions to Excel Pivot Tables.

28/2/2017

1 Comment

 

On the Shoulders of Giants

In his brilliant LinkedIn post (a collaboration with Sam McCurdy from Jet Reports and Peter Ørum from TARGIT) , Jan De Dyker summarized Adding Actions to NAV2013+ RTC from Targit using Jet Enterprise.
​
I, ever the inquisitive SSAS nerd with a transatlantic flight to kill, tried to recreate his efforts in Excel. I won't repeat the entirety of the content from his post, so you should definitely refer to it as a framework, but in adapting Jan's code for use Excel, I learned (read struggled) a lot and came up with some tweaks I'd like to share.

Foiled by Unsafe Links

To my chagrin, if you follow Jan's blog verbatim, you won't see the Action link in Excel--even if your settings are 100% correct because Excel deems the DynamicNAV protocol 'unsafe'. Insult to injury, if your URL settings are incorrect, Jet Data Manager won't necessarily throw an error -- you just won't see an Action in Excel.
Picture
As Jan points out DynamicsNAV:// is a valid proprietary URL protocol can be used to launch the RTC client within the Targit application. Unfortunately, due to Excel SSAS connection string settings, URLS that don't begin with HTTP:// or HTTPS:// are deemed unsafe (and therefore won't show).
Again, as described earlier, you won't see URL security compatibility warnings in JDM; instead, you have to open the SSAS cube up in Visual Studio (TimeXtender, if you're reading this, I hope you change that!) 
Picture
Despite my efforts to use the DynamicsNAV protocol within Excel, I was unable to modify Excel's connection properties and change Safety Options to 1, which, I assume is the parameter Targit uses. Given that this is true, our only recourse is to use the NAV web client (through the browser) instead of the RTC desktop application.
Picture
Find out more about connection strings here: https://msdn.microsoft.com/en-us/library/microsoft.analysisservices.adomdclient.adomdconnection.connectionstring.aspx

Accessing NAV via Web Client

If you're anything like me, you don't spend quality time writing URL queries for the NAV client, the best way to get a proper link is to navigate to the page you want to drill to and then customize the URL in your web browser.

In this example, I'm navigating to the Chart of Accounts page.
​

Original (messy) link:
  • http://localhost:8080/DynamicsNAV80/WebClient/List.aspx?company=CRONUS%20International%20Ltd.&bookmark=19%3bDwAAAAJ7BDEAMAAwADA%3d&mode=View&page=16&i=2&ni=1

Shortened link:
  • http://localhost:8080/DynamicsNAV80/WebClient/List.aspx?company=CRONUS%20International%20Ltd.&page=16

Now with filters:
  • http://localhost:8080/DynamicsNAV80/WebClient/List.aspx?company=CRONUS%20International%20Ltd.&page=16&filter='No.' IS '1001'

Review this link for more NAV URL syntax: https://msdn.microsoft.com/en-us/library/hh997130(v=nav.71).aspx ​

The Right Target Type for your Action

Jan used [Hierarchy Members] as the Target Type for the Action:
  • "Select a single hierarchy. The action will be associated with all members of the selected hierarchy. Attribute hierarchies appear in the list only if their AttributeHierarchyEnabled and AttributeHierarchyVisible properties are set to True."

I experimented with and prefer [Attribute Members] for its versatility:
  • "The only valid selection is a single attribute hierarchy. The target type of the action will be all members of the attribute wherever they appear (that is, the action will apply to user-defined hierarchies as well)."

From the definition, it's apparent that with [Hierarchy Members] you'll have to define duplicate Actions for each user-defined hierarchy. In the standard NAV project, there are 3 such hierarchies for [Item].  With the target type [Attribute Members] an Action is defined for one dimension attribute which is applied action across all user-defined hierarchies that reference said attribute.
  • Ex. by using the target type [Attribute Members] to [Item].[Item] the action is applied to hierarchies [by Inventory Posting Group], [by Product Posting Group], [by Item Category by Product Type].

Our action only works on the item level, but by targeting [Hierarchy Members] we assign the Action to 'all members of the selected hierarchy'. In doing so, users can (incorrectly) call the Action at the wrong hierarchy levels including [Inventory Posting Group] or [Product Posting Group]. By calling the Action at the [Inventory Posting Group] level users get no results from NAV, because there is no Item called 'Raw Materials'. Again, this can be remedied by using the [Attribute Members] target type.

Curiously, targeting [Attribute Members] does not work for parent-child dimensions. When viewing the lowest level of the [Chart of Accounts] hierarchy in the [GL Account] parent-child dimension, the Action was not available. The Action was, however, visible when viewing the [GL Account].[GL Account] dimension attribute on its own.

Find out more about [Target Types] at: https://msdn.microsoft.com/en-us/library/ff929231.aspx

Selecting the Ideal Dimension Attribute

I recommend applying the Action to the key level of a dimension; because, in the standard Jet project, the key level is the root level of all user-defined hierarchies.

Jan uses CurrentMember.Name to pass the [Item].[Item No] name into the Action URL:
  • "dynamicsnav://localhost:7046/DynamicsNAV80/CRONUS%20Nederland%20BV/runpage?page=30&$filter='Item'.'No.'%20IS%20'"+[Item].[Item No].Currentmember.Name+"'"

Presumably, this ensures that a lone item number is passed into the URL instead of 'Item No. - Description' as is the case with the [Item].[Item] key dimension level. Unfortunately, [Item].[Item No] was added purely for users with specific aesthetic requirements (they want to see the Item No. sans description), and it isn't as universally used as the [Item].[Item] key level.

We can bypass the problem Jan addressed with [Item].[Item No] by using CurrentMember.Properties("Key") on the key dimension level.
  • "http://localhost:8080/DynamicsNAV80/WebClient/List.aspx?company=CRONUS%20International%20Ltd.&page=20&filter='G/L Account No.' IS '"+[GL Account].[GL Account No].CurrentMember.Properties("Key")+"'"

Note: my assumption only holds true assuming you aren't using dimensions with composite keys (which is true for all dimensions in the standard Jet projects). For hints on overcoming composite keys see: https://msdn.microsoft.com/en-us/library/ms145528.aspx

Add MDX to your Action Captions

In Excel, you'll call your Action by the name you gave it (in Jan's example "Test NAV"). For a context-aware caption, select "Caption is MDX" and recycle the CurrentMember function developed earlier.
  • "Open " + [GL Account].[GL Account].CurrentMember.Properties("Key") + " in NAV"

This nuance came from: https://www.mssqltips.com/sqlservertip/3168/excel-actions-and-drill-down-for-sql-server-analysis-services/

That's All Folks

Again, kudos to Sam, Peter and Jan for all the groundwork they put into this topic. This post stands on the shoulders of giants who came before me!
If you need assistance implementing this in your Jet Enterprise or TimeXtender projects, please hit me up at Jae@OnyxReporting.com
1 Comment

Jet Professional Basics - Understanding FlowFields

19/2/2017

0 Comments

 
Almost 90% of spreadsheets have errors.

At an onsite  few weeks ago, I opened up a report built by my client, and within 30 seconds I identified  errors on his balance sheet reports that they had been using for several months!  Jet Reports promises to eliminate these errors by standardising and automation data extraction.  But what happens when you don't understand the data coming out of your system?  Time to learn bout FlowFields (predefined calculated fields in Dynamics NAV).
Picture
Key Walkawys:
FlowFields are predefined and precalculated fields in NAV
  • Balance on Chart of Accounts = sum(Amount) for G/L Entry
  • Inventory on Item Card= sum(Quantity) for Item Ledger Entry
  • Sales (LCY) on Customer  Card = sum(Amount (LCY) for Detailed Cust. Ledger Entry
FlowFields accept different FlowField Filters which modify the CalcFormula
  • Balance does not accept Date Filter while Balance At Date does.
  • Net Change accepts a date range while Balance At Date only keeps the highest date value.
  • Budget Amount accepts Budget Filter while Credit Amount is unaffected by Budget Filter
Any field ending in "Filter" is a FlowField filter and will modify the FlowField calculations.  It will not directly affect the set of records returned.
  • eg. Global Dimension 1 Code will filter a table based on the Default Dimension Value whereas Global Dimension 1 Filter will modify FlowField calculations.
For NAV 2013+ Jet Reports users using a Direct to SQL connector should see table definitions in the Browser.
For NAV2009R2 or earlier, refer to the following video.

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 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
<<Previous
Forward>>
    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