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.

• Inserting Columns or Rows 
• Moving Columns or Rows around OR
• CHANGING THE SORT ORDER OF YOUR REPORT!

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.

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

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.  

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

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

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.

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.

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!) 

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 ​

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

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

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

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.

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

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.