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.

This blog post is inspired by a true story.

Actually, chances are you've lived it already.  You've got a Jet Report that's supposed to balance, but for some reason, you're just a little bit off.  It's the end of the month, and we know the report 'worked fine' last month, but someone added a new G/L Account, Cost Center, or Item Category Code, and now you're spending hours tweaking things to make it balance. 

Like searching for the last submarine in a drawn out game of Battleship, the process can be tedious if you don't have a methodical search method, so we thought we'd share ours.

To search for missing accounts, we'll use a combination of Jet Reports' NP("Join"), NL("Filter") and NP("Difference").  Check out our 12 minute demo for balancing a trial balance.

The inspiration for this post came from a user seeking to balance distributions from a food bank (see image below).  

At first glance, the problem may seem completely different than a trial balance that won't balance, but actually the resolution process is exactly the same.

At the top, arranged in columns are the filter combinations for each category of distribution. The sum of the 6 column combinations, the [Added] column, 2,286,863, should sum up to the [Direct] column, 2,284,446, but the [Diff] column indicates there is a gap of 2417.

The user needs to tweak the 6 column filter combinations to cover the 2417 worth of excluded distributions.

The Onyx Reporting Solution:

1. Use NL("Filter") to identify all the [Entry No.] that ARE INCLUDED in each column (position them in the Yellow slots).  
2. Furthest to the right, we'll find all the entries that SHOULD be in included -- the [Direct] column.   
3. Use NP(Difference) to identify the missing entries
4. Finally, either use NP("Join") to list out missing Entry No., or pass the results into an NL("Table") so we can see the filter combinations that are missing from our report.

At Onyx Reporting, we're data people, systematic thinkers, skilled trainers, and most importantly, dedicated to our client's success.  Contact us for support on your next Jet Reports project.

Alternatively, email us your Jet Professional or Jet Enterprise problem, and we may feature the solution in next weeks' blog post!

My insider over at Jet Reports Canada tells me there's a new release of Jet Reports 2017.  It's so new, they don't even have release notes on it up yet (or at least I haven't seen them yet!) 

In addition to updating Jet Professional they also updated Jet Enterprise so head over to the downloads page and get on it!

This new version includes many new features, and upgrading is FREE for all Jet Reports customers current on their Annual Enhancement Program (AEP).  If upgrading Jet Professional can usually be an install update (as long as you're not using the Admin console; however updating Jet Enterprise can be a slightly more involved process.  If you need support with that, give us a holler over at Onyx Reporting.
 
Summary of New Jet Professional Features 

• Jet Web Portal scheduling of reports  
• Improved Drilldown experience for reports downloaded from the Web Portal
• Transactional level drilldown in GL function-based finance reports for Dynamics GP users
• Faster and easier report options tool
• Folders for reports in the Jet Web Portal
• Refined interface and improved performance in the Jet Web Portal
• Ability to download the Jet Excel add-in from the Jet Web Portal

We haven't tested out all the new features of Jet Professional, but in our last blog review of the major features of Jet 2017, we were excited to see the quality of life improvements offered by the Jet Web Portal.

Last week we explored 6 dashboard design tips to improve the aesthetics of existing dashboards.  In this post, we share design tips from our analysts about dashboard and OLAP cube requirements gathering as well as costly mistakes to avoid.

Tailor your dashboard to the audience AND the process they're trying to optimize.

• How important is this measure to a user compared to other measures?
• What other measures would provide comparative context?
• Is there a sequence of measures a user would follow to drill into a question?
• How might the answer to the previous questions differ by users at different levels of the organizational hierarchy?

Express the data in a way relevant to the audience.

• What's the scope of your dashboard? (Company, department, individuals, a supplier)
• What level of summarization or detail is appropriate?
• What unit of measure is approrpiate for each measure?
• What complementary information should I include?
• Again, how might the answers differ as scope or user changes?

Match the tool to the user. 

• Managers and executives may prefer a dashboard that provides a non-interactive executive or strategic summary, while business or data analysts may prefer to construct their own analyses against raw data straight from the data warehouse, while mid-level managers may require a live operational report.
• Did you choose the right chart?
• Should this be a PowerBI dashboard, a SQL query, an SSRS report, a Jet Professional Report, an Excel PivotTable?
• How often does the data need to be refreshed?  Do operational decisions depend on activity or status changes in the last 15 minutes?  Last 3 hours?  Last 24 hours?

Kicking off a project with an overly complex problem quickly  leads to project stall or paralysis.

• Complexity can arise when a variety of inputs are required to calculate a measure (eg. 'true profitability or cost allocation). A bottom-up approach of first identifying simple measures which aggregate into increasingly complex summarizations can encourage quick wins and maintain momentum.
• If your team insists on a top-down approach, divide and conquer. Decompose complex strategic objectives goals, or measures into composite pieces.

  In your data warehouse, cubes and dashboards, avoid using metrics or abbreviations who's meanings are not immediately obvious.

• The use of generic names like "Global Dimension 2", abbreviations or entity codes may affect adoption rates and user acceptance because they only resonate with a handful of seasoned analysts and fall flat with a broader audience.
• In your visualizaitons, stave-off confusion or frustration by opting for verbose titles, captions, and axis labels while avoiding legends.

KISS (Keep it simple).  Avoid clutter, non-succinct graphics, or unintelligible widgets. 

• 3D effects, sparklines, temperature gauge charts, mapping charts, and pie charts are common visualization elements that add spice and sizzle to a dashboard; however, when poorly executed, these elements take up significant space without communicating a lot of information.
• Ask yourself if you could express the same information with less ink, color or space.

Avoid failing to match metrics with goals.

• "How does this dashboard aid a business process?" It's not enough to showcase the activities of a department.  Keep strategic goals or operational activities in mind. Does the dashboard provide enough context for viewers to reach an actionable conclusion?

Don't wait to get started.

• Many are tempted to wait until development has finished before they begin laying out their dashboards, when in truth, report requirements should drive or at least inform development efforts.

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.

​CubeValue functions enable Excel wizards to create powerful dashboards OUTSIDE of Pivot Tables.


Advantages

• Easier to maintain and apply filters across multiple pages or reporting areas
• Easier to change one report without it affecting the layout of the rest of the spreadsheet.
• Does not require the use of proprietary software (and by extension skills transfer to other projects)
• Can build calculations outside of pivot tables (no more =GETPIVOTDATA() )

Learn something new?  Sign up for our newsletter. 

If you're interested in working with our trainers or consultants, check out our services page.