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:
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: