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.

1. Generate reports at different levels of detail. The example that comes up most often is generating a profit and loss for each level of an organization.


2. Generate a report with a hierarchy used within as the structure. The example that comes up most often here is using the account tree to generate the details and subtotals in a profit and loss statement.


3. Look at a report and drill using a hierarchy into a number in that report. An example of this is looking at the payroll number in a profit and loss statement and drill into the department tree to see how that is broken down (and repetitively drill to get additional levels of detail).

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).

Labels: nVision, Tree_Manager

Yesterday, I participated in a very interesting conference call with a PeopleSoft customer who has been struggling with certain aspects of drilling in nVision. Although I referred to several of the components of this previous posting on drilling in nVision, I realized that there are other aspects that weren’t discussed that warrant a follow-on posting.

Drilling to a PeopleSoft page

For those who read the previous drilling posting and downloaded the Journal drill layout that was part of that posting, you will see a "working" example of this. I forwarded a similar layout to the PeopleSoft Sales Support organization, so that they can use it as a template for building out examples of drilling to a peoplesoft page. One thing to note is that this drilldown layout is dependent on a defined name that is only populated when a report is run through the web (not on the client), so it will not work when you drill in 2-tier.

When enabling a layout to drill to a page, you will be using a feature that the PeopleSoft Portal uses for navigation: the URL syntax for opening a page with a piece of data. When scoping PeopleTools 8.4, we realized that although the syntax existed, it was relatively cumbersome to come up with this URL on your own. Therefore, we added a "copy URL" icon to the upper right of every PeopleSoft page (which shows up as an "HTTP" icon on the upper right). When you open a PeopleSoft page with a value (such as a purchase order), you can click on the image and the URL will be copied to the clipboard. You can then paste it into notepad or another editor to see what is needed to access the page with that piece of data.

When reviewing the URL, you will see the standard web server information (or URI), as well as the query string. Any needed parameters will show up as something similar to "&BUSINESS_UNIT=US001&PO_ID=10122274". When setting the layout up to drill to a specific value, you will be substituting cell references that contain the values you need in the right side of the each = sign.

Now that you know what the URL needs to look like, it is now time to add the links to your drilldown layout. The easiest way to do this is to follow a technique similar to that posted in the "enabling macros" posting in this blog. You use the drilldown layout to get real data in it (so that you can ensure that the references will give you the right values). When adding the links, you will be using the =hyperlink() function in excel, and then concatenating the string for the hyperlink.

An example of this is the following:

=hyperlink("http://servername.peoplesoft.com/psp/...
&q=BUSINESS_UNIT="&C5&"&JOURNAL_ID="&F5,"Journal Inquiry")

Keep in mind that the function must be put in the total row of the layout, and that you need to make sure that the column that contains the hyperlink function has the copy formulas setting in the nVision dialog, so that your formula will be copied to each row of data.

Obviously, if the drilldown result doesn’t have enough information to pass to the hyperlink, you may have to create a new drilldown layout (or extend the existing one). This may require adding fields to the query supporting the drill if the query doesn’t have that data.

Drilling from nVision to a query

Drilling from nVision to a query is almost the same as drilling from nVision to a page. There is a URL syntax for running PS/Queries, just like there is a URL syntax for opening pages. Therefore, you can drill from an nVision report using the same techniques listed above, as long as you know how to construct the URL for running a query.

This technique is put to use in PeopleTools 8.4 in the security queries. When navigating into the Security administration pages, there’s a page that has hyperlinks to run queries to tell you information about what users are in what roles, what permission lists have what pages and roles, etc.

The easiest way to find these hyperlinks is to use the Query Viewer, and right click on the "view" link for one of the queries in a search result to copy the shortcut. You can then copy it into notepad or another tool. This will give you the foundation for the hyperlink, but is missing the components of the URL that contain the runtime parameters (or prompts) for the query. In order to add the needed parameters, you merely append to the URL those parameters. In query, these parameters are identified by a BIND#, where the # is a sequential number. In other words, a query with 3 parameters will have the following appended to the end of the URL: "&BIND1=value1&BIND2=value2&BIND3=value3"

Therefore, an example of this is the following:

=hyperlink("http://servername.peoplesoft.com/psp/... &BIND1="&C5&"&BIND2="&F5&"&BIND3="&H5,"Drill to Query")

Limitations to this approach

The general limitations in this approach are as follows:

You must hard code the URLs into the layout (which can be an issue when you have both a development and test environment… A technique to minimize this issue is to put both URLs into each spreadsheet in different cells, and then switch the one you use as part of the move to production (using a defined name to accomplish this will minimize the number of cells you have to touch).

You need to have a different column for each target you want to go to (and, again, this is hard-coded into the report).

Products we've created to address these limitations

Due to the interest in this posting, we've created a product that dramatically improves drilling in nVision with minimal impact to your environment. Our nVision drilling snap-on allows drilling to pages and queries without requiring you to modify existing reports as listed above.

Take a look at the product page and even watch a pre-recorded demo of it in action.

If you want tighter integration between pages and reports, you're probably interested in our ERI products. The linkage is completely external from the drilldown layouts, and the menus are generated dynamically, based on the context. In addition, the ERI toolkit allows drilling from a PeopleSoft page back into reports without requiring modification of the PeopleSoft page. However, if you have a limited number of places to drill, and a limited number of drilldown layouts to use, ERI may be overkill.

Labels: Drilling, nVision