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:

First Character:

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

Here's a neat tip that I wish I figured out when I was responsible for tree manager. Had that occurred, it would have been part of the product, versus a blog tip (oh well... you can't have everything).

I had a little help in figuring out this approach from one of our customers as we discussed the need to document the values in the chart of accounts for business users (using trees).

Chart of account listings
In order to make it easier for business users to understand the structure of the chart of accounts, especially when looking at individual chartfields, many customers have need to generate highly formatted reports, incorporating the trees. One of the major complaints about tree manager is the limited printing functionality available in it (and I can remember many long meetings trying to figure out a quick and easy way to deliver printing for large trees without writing a completely new tool to generate it).

Here is a quick synopsis of the major limitations of printing in tree manager.

• It cannot be run easily by users unless they manually open it up in tree manager.
• The results are generated in HTML in the browser, which means that the results have limited use and cannot be saved easily for future use (not to mention that printing large trees is a significant issue from the browser)
• It does not show the set of detail values in the system or any attributes of those values. This is critical when the audience needs the listing to document both the tree and the values represented in the tree (versus just documenting the tree itself).

With nVision, tree nPlosion, and nVision styles, it's possible to do all this without programming. A side benefit is that the resulting report could be used to drill into other detail (for example, one could look at the project report which shows all projects and activities hierarchically and drill into the journals posted to a given category of projects... Pretty Cool, right?)

Creating a tree/value listing report can be accomplished using the following steps:

• Build a query against the detail value table that the tree was built on. This query is merely to get the "facts" into the nVision report (much of the content of the report will come from the fact that you will be nPloding using a tree in it). As such, all it needs is the key and an aggregate field. Using Project as an example, the query would have the following:
  
  • The key field by which the tree would be linked. In this example, it would be Project ID.
  
  
  • An aggregate field that caused a count of the number of rows (values) to be displayed. PS/nVision requires this for matrix reports.
  
  
• Built a matrix layout in nVision for the report. Because the nPlosion rules cause the tree/dimension to be linked into the report along with all fields available in the tree/dimension and detail values, much of the content of the report will be the nPlosion and labels of values in the tree and detail value table.
• nPloded the rows on the appropriate tree, using full tree nPlosion to detail values.


• Set the source of the sheet to be the query that was created.


• Labeled the first column to show the tree node ID and the project id


• Labeled the second column to show the tree node description and the project description.


• Labeled the third column to show the project manager.


• Labeled the fourth column to pick the count field from the query as the source
• Applied the stylesheet to the report in nVision.

This report can now be generated through nVision and distributed to the users using standard reporting tools.

Labels: nVision, Tree_Manager