WebFOCUS Newsletter July 2003 - Using a Subroutine to Handle Unconsolidated Data

Sometimes the data is held in a native accounting format (Figure 1). In this format, revenue figures are stored as negative numbers. On the financial report though, the detail numbers would always be shown as positive numbers, but would be aggregated in respect to the sign.

Figure 1

This will require a COMPUTE for the display field. In this situation, or where multiple lines have been combined as one, the value of the FOR field may not be readily available within COMPUTE statements. When lines are combined, a COMPUTE would pick up the lowest number as opposed to the first account referenced.

To accommodate this situation, we have created a new user-written subroutine called FMLINFO to provide the ability to accurately determine the FOR value. The syntax of this routine is FIELD/XXX = FMLINFO(‘FORVALUE’,’XXX’) where XXX is the format of the FOR field.

Consider the report in Figure 3. If it is based upon the preconsolidated data in Figure 2, no aggregation takes place, and the code can either be used to print or sum the data as shown here:

TABLE FILE EXAMP
SUM  AMNTS 
AS 'CURRENT,MONTH,ACTUALS'
FOR   CHILD
1000 WITH CHILDREN ALL AS CAPTION
LABEL R1

Figure 2

Figure 3

However, to produce the same report on unconsolidated data similar to that of Figure 1, additional processing of the data is required. This is where the FMLINFO functionality comes into play. It allows you to reverse the sign before it is actually written to the report page but after it has been aggregated. The code is as follows:

TABLE FILE EXAMP
SUM 	AMNTS

NOPRINT
COMPUTE AMT/D12.2 = IF FMLINFO
('FORVALUE','A4') LT '2500'
THEN (AMNTS * (  -  1))
ELSE AMNTS;

AS 'CURRENT,MONTH,ACTUALS'
FOR  CHILD
1000 WITH CHILDREN ALL ADD
AS CAPTION LABEL R1
ON TABLE SET FORMULTIPLE ON

Please note that the Use Multiple Values and the Consolidate check boxes would be checked for unconsolidated data reporting in the matrix window (Screen 1). The FMLINFO routine is only effective within a compute. If you use it in a DEFINE it will yield unpredictable results.

Screen 1

Another area where the FMLINFO routine is used is in drill-down financial reports. Assume that you wanted to drill down on the actual FOR field to another report. While the FOR field is selected for the report, its caption is displayed instead.

Regardless of this, you should still choose the FOR field as the object of your drill-down (Screen 2). When the report is displayed, the caption field will have the underline indicating its availability for drill-down. The value you would want to pass, however, would have to be based on the value you retrieved via a field computed using the FMLINFO routine shown in the following code:

TABLE FILE EXAMP
SUM  AMNTS    AS 'CURRENT,MONTH,ACTUALS'
COMPUTE ACTCHILD/A4 = FMLINFO('FORVALUE','A4');
NOPRINT FOR CHILD
1000 WITH CHILDREN ALL AS CAPTION LABEL R1

Screen 2

FMLINFO does not currently appear in the list of user-written sub-routines. Please keep this article in mind during your development efforts.