Inter-row and Inter-column Calculations in WebFOCUS Financial Reporting Painter

By Harry Lotrowski

The WebFOCUS Financial Reporting Painter is one of the few tools that allows for inter-row and inter-column calculations. From within the Developer Studio Matrix tab, you can do calculations based on cell values regardless of where they are shown on the page. However, there are a few things you will to consider when creating these calculations using RECAP.

First, you need to understand that you can only reference cells to the left or above the cell you are calculating. If you were calculating a value in cell 18, for instance, you would only have the values in cells labeled 1 through 17 available. You would not be able to reference cells 19 through 25.

Figure 1 shows the reality of this. Notes 1 and 4 show columnar references to a field to the right and above the calculation. This appears as valid. However, Note 2 shows the result of a calculation referencing a location to the right of the calculation. At the point in time the calculation occurred, the value of this cell was zero. Note 3 refers to a cell that is not yet in the internal matrix, so the value appears as missing.

However, since you can reference values multiple times, you can make rows invisible, yet if you create a row with the needed values before your recap, they are available to your RECAP statement. Figure 2 represents the logical view of this. In Figure 3, we show the painted statements (Rows 2 and 3 are marked invisible) and the result with the desired figures.

There may be an additional issue when creating complex calculations in the Financial Report painter. Because internally every referenced field is stored in the internal matrix, the column numbers may not be what you think they are.

For instance, if you were to reformat a field on the report, both the original and the reformatted field are held in the matrix. So the field you think to be column 1 is in actuality column 2. Consider the example in Figure 4: Although we had requested a recap based upon column references, they only worked for the first two columns.

Because the painter only recognizes five columns in the request, it cannot satisfy the recaps for anything past column 5. In code, however, we show what would have to be coded (outside of the painter). After creating a hold file we see what is actually in the internal matrix. However, all this is now moot.

Starting with release 5.3 there is a setting (available in the Set Dialog in release 7.1) that instructs the processing logic to only count displayed columns. This command is SET CNOTATION=PRINTONLY. Running the original request (Figure 6) now yields the correct result (Figure 7).

Armed with the knowledge of how WebFOCUS processes RECAPS, you can paint the most complex financial analysis reports.