At yesterday's Advanced nVision tips and techniques webinar, I discussed the ability to determine from your ledger data, where the values came from (such as AP, AR, Asset Management, etc.). This was one of the most popular topics of the webinar, and yet I haven't taken the time yet to post it to the blog.

What is it?

This is a special drilldown layout that you can use in nVision that will look at the underlying transactions and categorize them by the system in which the financial numbers came from. When combined with the Auto-Drill feature of our Excel Add-in product, your users can automatically be directed from a management report, to the breakout of a number by system, into the transaction detail of the source system, and even into the application to see the transaction itself. This can all be done by double-clicking on the numbers you want to explore in more detail.

Cool. How is this accomplished?

The starting point is figuring out how to categorize transactions into the source systems. The Journal Header contains a field (SYSTEM_SOURCE) that is tantalizingly close to allowing you to accomplish this, but there are over 100 values in it, and those values are defined as translate values (meaning there's no additional metadata other than description to help organize it). What is needed is the ability to add additional metadata to help accomplish this.

Here enters the tree. There are a few nice things that come from using a tree to help accomplish this

• You can categorize the values without modifying the original data structure of the journal tables
• nVision knows how to nPlode by tree node automatically

In fact, one technique that I've used over and over is to use trees to fix data model issues in the application (such as creating a common reporting structure when the underlying values are not constent across business unit).

Here are the steps

You can download all the code from here, but here is exactly what was done to accomplish this

1. Create a view over PSXLATITEM to give you the a source of valid values for your tree
2. Create a tree structure that uses the view for the details. You can create your own node table if you want to track other metadata by source system, but for the purposes of this example, I used the default tree node table
3. Create a tree over the structure that groups the low level SYSTEM_SOURCE values into nodes the represent each source system (or subledger)
4. Create a matrix drilldown layout that nPlodes the rows using your tree, and uses a query against the journal line and journal header tables
5. One optional step is to deploy the Grey Sparling Excel Add-in to allow the double-clicking to automatically pick the drills to run based on the source system in the drill layout.

And, that, my friends, is how it's done.

Labels: Financials, nVision, Tree_Manager