This is a question that came up on the ITToolbox forum peopletools-l. I decided that it would be a good idea to flush out my answer in additional detail (and post it here). Interestingly, as I did my research, I discovered that how commitment control uses trees was more complex than I thought. I also discovered that one of my statements in my response was inaccurate.
Here is a link to the post.
Can you do it?
The answer to whether you can use them in GL is still yes, and the method by which you use them is the same as how you would use them elsewhere. For those who don’t want to read all the way through my blog post that discusses trees, here is some background on winter and summer trees.
Winter and Summer Trees
The naming of summer and winter trees comes from whether they have leaves on them or not. So, just as with a deciduous tree, a summer tree has leaves and a winter tree doesn’t.
The first question that may come to mind is “why have leaves in one type of tree, but not another?” The answer to that question is whether the hierarchy is logically separate from the items being organizaed by the tree. For example, in a position hierarchy, each position belongs at a specific place in the hierarchy, so there are no need for leaves. By contrast, in most department hierarchies, departments are rolled up into offices, regions, divisions, etc., which means that departments are the leaf, and the rollups are not actually departments (and are the nodes).
When these trees are used by products, such as nVision and Query, those tools understand how to gather the underlying information for subtotalling and filtering based on the tree type (the join for a winter tree uses the nodes for joining directly to the data table, whereas the join for a summer tree uses the nodes to identify the leaves to join to the data table).
Why would you want to use Winter Trees in GL?
This is a good question, because at first blush, most reporting is done against aggregations of chartfields in GL. However, there is one very good scenario where you may want to eliminate the leaves and have each node in the tree be a chartfield value in your ledger table: budgeting.
When you do budgeting, quite often it is just too cumbersome to budget everything at the lowest level of granularity in which you capture transaction data (i.e. your journals). If you make the decision that you will create summary accounts that represent different rollups in your hierarchy, then a winter tree will work for you.
The delivered PeopleSoft demo database has an example of a winter tree on accounts (and interestingly enough, it’s called “CONTROL_BD_ACCOUNTS”.
One thing to keep in mind, however, when having aggregate chartfields values that you use in your trees, is that they are real chartfield values that could have values posted to them unless you do something to specifically prevent that. The Financials developers have done exactly that for the budgeting scenario. When you set up a GL account, you can specify it as being a Budgetary Only account, which will allow you to post data into budget ledgers, but not actual ledgers for that account.
I think it’s also important to mention Spring Trees, because they are similar to winter trees in several ways (and this is something I got wrong in my answer on ITTOOLBOX). Strictly speaking, though, Spring Trees are summer trees.
This is because Spring Trees do have both leaves and nodes. However, in a Spring Tree, the Leaf and the Node attributes are stored in the same table and use the same field.
This is a relatively curious, because a winter tree would accomplish exactly the same thing, especially since the aggregate accounts (i.e. the ones referenced on nodes) are not duplicated on the leaves. I think the main reason for this is that the tree nodes themselves drive the commitment control rules for determining whether you have budget or not, whereas the leaves are the level at which the data is posted. However, it looks like I need to do a bit more research on this.
Over the past 24 hours, I’ve had lots of folks wanting to learn more about the HCM reporting examples in yesterday’s post. I decided to record a flash demo that shows how one would use the queries as well as the nVision reports (and drills).
In order to simplify the navigation in the demo, I did use the nVision Drilling Snap-on (which is separately licensable, but is not required to use the queries and nVision objects in the project). However, it does make it much easier to find and use them together.
Click here to watch the HCM Reporting in action…
Labels: Drilling, HCM, nVision, PeopleSoft, Query, Tree_Manager
After reviewing my last posting, I realized that although I covered the specifics of style definitions and nVision, I missed a critical set of information. You see, there are limitations in Excel as well as nVision that can easily be worked around if you understand the following:
The posting and formatting process nVision follows
Because nVision uses excel for storing results and for formatting, a basic understanding of the steps that are followed, and excel behavior will give you some insight as to the hooks available for formatting outside of the nVision stylesheet functionality discussed in the previous posting.
So, in a nutshell, here are the steps followed in nVision in running a report once it has retrieved the data to be included:
Step 1: Insert rows or columns in the worksheet
It inserts the appropriate number of rows or columns for the results of the nPlosion. When it does this, it calls the excel function to do this. This is important to know, because the standard excel behavior for an insert occurs. You may be thinking “yea… so…?”
So… This means you can leverage some of the cool things that excel does on an insert with respect to data ranges and formatting:
- If you insert a row or column in the middle of a range (either a named range, or a range that is used in a formula, chart, etc), the references in the range are automatically updated. This means that you can bind a chart to nPloded data without having to write a macro!
- If you insert a row or column in the middle of two cells that have the same formatting, the new cell in the new row or column is given the formatting as those around it. This means that if you apply conditional formatting to a cell in the row or column with the nPlosion specified as well as the one above it, all nPloded rows or columns will also have the conditional formatting applied (even though styles in Excel don’t support conditional formatting).
In other words, by paying attention to where nVision inserts rows or columns, you have additional control over the behavior of the results.
Step 2: Paste the data into the worksheet
This is done using some bulk APIs available in excel. Because it only pastes the values (and formulas for formula nPlosion), it does not override the formatting.
Step 3: Apply the styles
I’m not sure whether this is done prior to pasting the data, but it actually doesn’t matter because the paste data step doesn’t affect the formatting of it. The important point is that the default behavior of excel for inserting a row is applied prior to the step of applying the appropriate nVision styles .
One additional point is that excel styles are not all or nothing from a formatting perspective. In other words, you can define a style to only include the cell color and nothing else. This gives you a lot of flexibility, and also allows you to retain formatting in the original layout as needed. Because of this, you can think of the formatting as being applied in layers (where you can override part of what comes from a lower layter).
Putting this into practice
Although there are a couple of examples above as to how to put it into practice, I believe the following example does a pretty good job of putting it all together.
Imagine that you have a report that shows payroll costs, FTEs, and Percent over/under budget as three different colums. It nPlodes the total amount for a general manager into the department detail. Notice that the number formatting for each column should be different: currency for column 1, a number with no decimals for column 2, and percentage for column 3.
Because the nVision stylesheets do not support multiple amount styles for row nPlosion, the number formatting needs to come from the report layout, where the columns are created. Therefore, the report developer would need to do the following:
- Apply the number formatting for the different columns as needed in the nVision layout(one for dollars, one for percentage, etc.)
- Modify the amount styles (the ones that end in “A”) to exclude number formatting. You may want to save off a new stylesheet workbook for use in this type of report in the future.
- When you run the reports, the number formatting won’t be applied from the styles, while the other formatting, such as font size, border, and pattern will be applied.
So, you’ve created this really cool nVision report that gets all the data you want, but you can’t for the life of you figure out how to get it to format the way you want. You’ve gone through all the documentation, and have reached the point where you’re wondering where you can get a lamb to sacrifice to the gods of nVision. In order to save countless sheep out there, I’ve tried to describe how it works and what you should do.
In the beginning….
Originally, formatting in nVision was pretty straightforward. You would format the cells you wanted, and the formatting of the contents cells were used for any rows or columns inserted through nPlosion. Because the options for nPlosion were limited, there wasn’t much need to deal with it.
Along came tree nPlosion…
Then, we added the ability to nPlode multiple levels in a tree. This was great, because there was less hard-coding needed in reports, but it added an issue of how to format the different levels different ways. We needed to create an infrastructure to support this.
This infrastructure was created to allow formatting to be applied to rows or columns that nVision inserts due to the nPlosion feature (which shows supporting detail dynamically). It pulls together multiple elements in an effort to simplify the effort for applying these formatting rules.
- It understands the inherent structure of an nVision report, where there are
- Headings that precede a set of children rows or columns and label them.
- Labels that are used to describe rows or columns.
- Numeric amounts.
- Base formatting for rows or columns inserted, where the cells are not populated by nVision.
- It understands that results are generated at multiple levels of granularity, and that the different levels need specialized formatting rules.
- It leverages the formatting functionality delivered in excel, and allows the rules to be applied in combination with each other using Excel styles:
- Number formatting
- Indentation and other alignment features
- Font formatting, including size, name, and emphasis
- Outline formatting, such as that used to designate a total line.
- Background color.
In addition, an understanding of the way that nVision inserts rows and applies formatting allows customers additional flexibility in applying formatting, such as conditional formatting (which is not supported in style definitions in excel). Conditional formatting allows the data in the report to control the format (such as highlighting a row of data that falls outside an acceptable threshold).
It’s important to note that nVision styles are not applied to rows or columns that existed in the layout prior to running the report. They are only applied to rows inserted due to nPlosion.
More on nVision Stylesheets
In order to support defining different formatting for rows, columns, different levels of aggregation, and different types of fields in a report, nVision has implicit knowledge of specific style names in excel that have different meanings. In other words, when it comes time to apply styles to an nVision report, it looks for excels styles with specific names and applies them. This means that there are different style names for these different attributes.
Therefore, Excel styles that nVision utilizes has the following naming convention:
R (for row), or C (for Column)
Second and Third Characters:
The level number (00 = detail, 01 = first level, 02 = second level)
Fourth and Fifth Characters:
B (for Base), A (for Amount), L (for label), H (for heading)
This means that R01H is the formatting to be applied to the heading row for a row nPloded at the first level of the hierarchy.
In order to simplify the maintenance of these styles, spreadsheets are delivered with the styles populated and displayed. Customers can maintain the styles within these worksheets and then click a button to apply (or copy) the styles to a given nVision layout. After the style names have been copied to the layout, they can be modified directly in the layout without affecting other reports.
Labels: nVision, Tree_Manager
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.
- 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.
- 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.
- 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).