I've recently gotten my VBA coding juices going again, and finally decided to crank out something that I've been talking about for quite some time (you'll can expect a blog entry next week that discusses my other VBA effort this week).
When I was responsible for nVision, the feature was called "Multiple Field nPlosion".
What is it?
In a nutshell, multiple field nPlosion is the ability to show slices of data for two different fields in the same axis in an nVision report. For those familiar with nVision, you can take a row or a column and tell it to generate data for values of a field, or a part of a tree. Currently, the product is limited to doing this for one field.
However, there are a lot of situations where you would want to use multiple fields for this (which is a standard OLAP requirement). One example is generating your profit and loss statement using the account tree, but when the tree hits the account details, to show the breakout of how those account values are represented by different products. In other applications, such as HR, there are a lot more examples that are simpler, such as showing benefits elections by manager over time.
So, how do you do it?
The first time I discussed how to handle this was about 10 years ago with Tom Pitra of Pitra consulting, fellow nVisionary of high standing, who successfully implemented this for a major PeopleSoft customer in the Minneapolis area.
The approach is to leverage the fact that nVision keeps its original criteria in place when running a report and then puts in criteria for values or nodes when it generates the children rows for nPlosion (generally stored in context cells in nVision... Row 1 or Column 1 in the layout). This allows the context of the values to be retained for drilldowns. We also leverage this artifact for several of our products, most notably our Excel Add-in.
So, here are the steps used to accomplish mutliple field nPlosion:
Here is what the final report would look like by showing departments within accounts.
Cool. Gimme the Stuff!
I've packaged this up into a single nVision layout with the macros embedded. You run the report, then push the button on the sheet to run the macro, and then run the resulting report (which will be put into E:\PT846\NVISION\LAYOUT\NPLODE01.XNV ). This means you will need two nVision report requests for this. ONe for the file attached here, and one for NPLODE01.XNV.
Here is where you can download this layout: OriginalnPlosionTest.xnv
This example has a lot of limitations in it, so you'll want to tweak it based on your own needs. Here are a few I can think of:
All of these things can be addressed by extending the work already present here.