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.
Labels: nVision, Tree_Manager