There was another set of requirements that were important to the customer in my conference call yesterday. That was hierarchical reporting in nVision. The customer’s initial question was, “Can you do it, and how?” Obviously, the answer to the first part was “yes” (otherwise, I wouldn’t be writing this posting). However, there are actually 3 different answers to the second part, depending on what you mean by it.
So, let’s frame the discussion by listing out the 3 different things one could be trying to do with hierarchical reporting.
So, let’s look at each one to see how you would accomplish it with nVision.
Generating reports at different levels of detal. This is a standard feature of nVision, using the “scope” object. When running a report, you can tell nVision to generate instances using a scope. The scope allows for instances to be created based on values in the system or nodes on a tree. One little known feature of the scope is that you can pick multiple levels of the tree in the same scope definition, and it will process all of them. This works very well when a customer runs the nVision reports on a pre-defined schedule and wants to get a “package” of reports pre-generated at all levels of granularity.
If you want to do this in a more ad-hoc manner (in other words, allow the user to run a report for “his” part of the hierarchy), this is a little more cumbersome. One approach is to use row level security to eliminate the data, but this is done at the database level and the reporting tool actually doesn’t know that data was eliminated (so there is no way to show in the report what was included or pulled out other than looking at the detailed data).
Our PeopleSoft Solution Extender for nVision provides an ad-hoc solution that does address these needs. We’ve created a new report request page for nVision that allows the user to specify what nodes to use to filter the report without requiring the scope to be involved. This will allow the user to pick which part of the hierarchy to use for the data in the report at the point they want to run the report.
Generate a report with the hierarchy used within as the structure This is another standard feature of nVision that we added in PeopleTools 8 (called tree nPlosion). When designing the report, you can nPlode on a tree, and pick to nPlode through the tree. Because nPlosion will be inserting rows for the different levels of the tree, formatting is important, so we also added a robust style and formatting set of features that allows control over the font, color, indentation, etc. for each level of the hierarchy.
This works well for most situations. However, there are a couple of limitations in the implementation. The most striking of which is that every amount field in the results must use the same style (which is an issue in excel, where the number formatting is part of the style). This means that Dollars, Percentages, Headcount, and Square Feet all have the same number format applied (not desirable). A simple workaround is to use an instance hook to apply the appropriate formatting. However, we’ve also addressed this in the PeopleSoft Solutions Extender for nVision.
Looking at a report and drill using a hierarchy into a number in that report. Again, this is a standard feature in nVision (and meeting this requirement was considered as part of the design for the tree nPlosion functionality added in release 8). The way to accomplish this is to build a drilldown layout that has the nPlosion options set to drill to the next level of a tree.
One common mistake that people make when developing drilldown layouts is to qualify them too much (that’s why in your drilldown layout, you should pick the root node of the tree for filtering and then pick “next level” as your nPlosion option… If you’re already drilling from a lower node of the tree, it will override the criteria in the drilldown layout).
To use the layout, you merely pick the number you want to drill from, and then pick the layout to go to the next level (if it’s the first time, then you will probably be starting from the root of the tree). If you want to see the next level from your drilldown results, you drill from the number you want to see more detail from, and then re-pick the “to next level” drilldown layout. Here is an example layout for the department tree.
Another option is to create one layout that nPlodes through the whole tree. This will show in one step, all levels in the tree and the associated breakouts of supporting detail from the starting value. This is an important technique when you want to see organizational responsibility for a balance or expense item. For example, if you want to see the sales contribution from your profit and loss through your sales organization, you can develop a drilldown layout that nPlodes to all levels of the organization tree and use it to drill from the sales number on the profit and loss statement. It will then show you all levels of the organization tree, the contribution to that sales number (which is important from a Sarbanes Oxley perspective).