Blog

Created on Friday, 29 May 2009 18:16

Written by Larry Grey

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:

1. Run your nVision report, nPloding the outermost field first. This gets you the rows for that field, formatting, as well as the first set of numbers.
2. Run a macro that fixes the inserted rows, so that they have all the criteria as if they were designed using the nVision designer. To reduce the size of the file, nVision only puts the values or nodes specification into the nPloded rows (or columns). This means that the overall total row is the only place that identifies the field and/or tree and other criteria that was used to generate the nPloded value. Because this logic was written when there were a lot more limitations on cell sizes and other resources in excel, it was done this way.
3. Then, a macro should swap criteria as needed for the second run. In other words, it will need to take the fields used as labels in the first run as well as the values that should not be nPloded in the second nPlosion and turn those into non-nVision specifications (I just prepend a # in my code). Also, the macro will need to turn any label columns that are reserved for the second run into nVision specifications.
4. Then, the macro will need to append the new nPlosion specification to the places where the new nPlosion rows need to be. In my macro, I chose to do this where the lowest-level values cam in from the first run (e.g. account numbers).
5. The last step of the macro is to save the spreadsheet as a layout in the nVision/layout directory.
6. Then, you run the new layout as an nVision report. All of the processing your macro did will cause nVision to only nPlode the second field where you want, while inheriting the original criteria and location in the report. Thus, double nPloding

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

Limitations

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:

• It only nPlodes the second column at detail values
• It only works for 2 fields
• It only nPlodes rows and not columns
• It isn't completely automated, which means the user has to do several steps to get the results
• It hasn't been adapted to work on the process scheduler
• Finally, there's no designer for the criteria and specifications. YOu have to put in the #'s yourself to make it work.

All of these things can be addressed by extending the work already present here.

Labels: nVision

nvision