ONYX REPORTING LTD.
  • Welcome
  • Services
  • Blog
  • YouTube Channel
  • Contact
  • Domo IDEA Exchange
    • Schedule
    • Call for Presenters
  • Welcome
  • Services
  • Blog
  • YouTube Channel
  • Contact
  • Domo IDEA Exchange
    • Schedule
    • Call for Presenters
Search

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
Jan De Dycker link
24/4/2017 02:59:44 pm

Hi Jae,

I could've told you that Excel will be a nearly impossibe nut to crack. It's still the most used SSAS visualiser (and a relatively powerful one that doesn't ask for a big investment) for our customers, so you can guess I tried to go down that path.

That said, there's good reasons for this as this could allow Excel links to start all sorts of applications. With TARGIT, the content is managed from a central server (ANTserver), which reduces that risk greatly.

Anyway, it's a nice to have and we have since this article implemented it successfully more than once.

Reply

Your comment will be posted after it is approved.


Leave a Reply.

    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
    Data Science
    Domo
    Excel Tricks
    Executive Training & Leadership
    Extract
    Jet Enterprise
    Jet Essentials
    New Release
    NP Function
    Onyx Reporting
    Planning
    Power Pivot
    Python
    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
  • Domo IDEA Exchange
    • Schedule
    • Call for Presenters