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

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.

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

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.

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