So, ever wonder where all that "stuff" goes when you use the dialogs to lay out an nVision report? I'm sure many readers don't wonder because they already know. However, even those who do know don't always know the potential for using that knowledge.

All the "stuff" goes into content cells. For row, column, and sheet specifications, it's stored in row 1 and column A (I talked with the folks at Actuate responsible for e.Spreadsheet, and they use a similar construct, but instead of the top and left, they store their report definition information to the right and bottom of the report).

So, why do I care?
Good question. The reason you care is that you can do all sorts of stuff with the contents cells to make nVision do new and interesting things. I've worked with customers who've built VB modules that either create or update the content cells to either leverage corporate standards, or simplify the report design process. A good example is replacing a detailed ledger with a summary ledger automatically (this is something that I wish I had addressed as the product manager for nVision, because it's pretty cumbersome for a business user to understand when to use different summary ledgers that may be set up for them).

Here are some examples of things that I've seen customers do with knowledge of content cells.

1. Summary ledger substitution.
2. Streamlined, standardized, report design using a VBA application instsead of nVision dialogs
3. Mulitple field nPlosion on a single axis (this technique was developed by my friend, Tom Pitra of Pitra consulting).
4. Making calculated columns (or rows) drillable.
5. Extension of security (by having the contents cell have a blank value depending on the user running the report).
6. Faster report design (this is the most common usage... copy a content cell down and then modify part of it using the formula bar in Excel).

Okay, so what's in these cells?

Another good question. All content cells in nVision begin with a % sign. They are also made up of prefixes that identify what type of specification is in that cell. For example, if you have taken column C and designated that that column contain the local ledger, then the contents cell (C1) would have the following:

%,LLOCAL

If, instead of using a ledger, the same column used the column B.MONETARY_AMOUNT of query NVS_JOURNALS, then the contents cell (C1)would have the following:

%,QNVS_JOURNALS,CB.MONETARY_AMOUNT

As a 3rd example, let's say that row 6 had criteria to filter on the field ACCOUNT using the tree ACCTROLLUP, tree node INCSTMT, and nPlosion options to nPlode to all levels, including details. The contents cell (A6) would have the following:

%,FACCOUNT,TACCTROLLUP,XBYNNNY01,NINCSTMT

Now, that previous one may be a little complex because of the XBYNNNY01 string (which is actually the nPlosion options that were set). My suggestion is to use the UI to make the settings you want in that circumstance to learn what the different flags mean (each Y and N represents a check box either selected or not selected).

I created the following table to explain the different prefixes and what they mean (I don't remember what R is, so I apologize up front).

C=Column
F=Field
L=Ledger
N=Node
Q=Query
T=Tree
U=Metric
V=Value
Y=Period

The reason I had this table handy was because I used it to decompose inherited criteria in a drilldown, so that the user can see the context easily. The logic using the % and the prefixes is exactly the same. After the drill is run, all inherited criteria is stored in a defined name called NvsInstSpec. I used a combination of the Mid function and the Find function to split the InstSpec into multiple cells (and then did a VLOOKUP to the table to tell the type). The resulting inherited criteria from an example drill looks something like this:

Query CASE_FACTS
Column B.CASE_OPEN_COUNT
Field CUST_ID
Tree CUSTOMER
Node AMERICAS

The NvsInstSpec has the following value:

%,QCASE_FACTS,CB.CASE_OPEN_COUNT,FCUST_ID,TCUSTOMER,NAMERICAS

As you can see, much easier to understand.

Labels: nVision