Jet Enterprise & TimeXtender: Add Dynamics NAV drillthrough actions to Excel Pivot Tables.28/2/2017 On the Shoulders of GiantsIn 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 LinksTo 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 ClientIf 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:
Shortened link:
Now with filters:
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 ActionJan used [Hierarchy Members] as the Target Type for the Action:
I experimented with and prefer [Attribute Members] for its versatility:
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.
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 AttributeI 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:
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.
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 CaptionsIn 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.
This nuance came from: https://www.mssqltips.com/sqlservertip/3168/excel-actions-and-drill-down-for-sql-server-analysis-services/ That's All FolksAgain, 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
24/4/2017 02:59:44 pm
Hi Jae,
Reply
Your comment will be posted after it is approved.
Leave a Reply. |
Categories
All
|