I would like to have a COMPUTEd field (DIFF_PCT)in a RECAP row of an FRL report recomputed. As it stands, the RECAP row just has the summed total of the column. And since this is an ACROSS, I would like to use "Relative Column Addressing" because every third column needs to be recalculated, but I just cannot figure it out, *!@#$@^

I don't understand why the COMPUTE just doesn't get recomputed with the RECAP statement, that would be the most likely thing people would expect of a RECAP.

My Master:

FILE=R041H300       ,SUFFIX=FIX
SEGNAME=R041H300,SEGTYPE=S05
FIELDNAME   =DIVISION_NODE_NM2  ,E01         ,A50      ,A50      ,$
FIELDNAME   =METRIC_CD          ,E02         ,A02      ,A02      ,$
FIELDNAME   =METRIC_DESC        ,E03         ,A25      ,A25      ,$
FIELDNAME   =ASSET_CLASS_SORT   ,E04         ,A02      ,A02      ,$
FIELDNAME   =DELQ_DAYS_CNT2     ,E05         ,I4       ,A04      ,$
FIELDNAME   =METRIC_AMT1        ,E06         ,D20.2    ,A20      ,$
FIELDNAME   =METRIC_AMT2        ,E07         ,D20.2    ,A20      ,$
FIELDNAME   =DIFF_PCT           ,E08         ,D8.4     ,A08      ,$

My fex:

-SET &ECHO=ALL;

FILEDEF R041H300 DISK C:\IBI\APPS\TEST\R041H300.FTM
-RUN

TABLE FILE R041H300
SUM
METRIC_AMT1
METRIC_AMT2
COMPUTE DIFF_PCT/D6.2% = ( METRIC_AMT2 - METRIC_AMT1 ) / METRIC_AMT1 * 100;
BY
DIVISION_NODE_NM2
BY
METRIC_CD
BY
METRIC_DESC
ACROSS
ASSET_CLASS_SORT
FOR
DELQ_DAYS_CNT2
'0' AS 'Current' LABEL R00 OVER
'29' AS '1-29 days' LABEL R01 OVER
'59' AS '30-59 days' LABEL R02 OVER
'89' AS '60-89 days' LABEL R03 OVER
'119' AS '90-119 days' LABEL R04 OVER
'149' AS '120-149 days' LABEL R05 OVER
'150' AS '150 days or more' LABEL R06 OVER
RECAP R_TOTAL = R00 + R01 + R02 + R03 + R04+ R05 + R06;
AS 'Total' OVER
" " LABEL R9 OVER
RECAP R_30DAYS = R_TOTAL - R00 - R01;
AS '30 days or more' OVER
RECAP R_90DAYS = R_TOTAL - R00 - R01 - R02 - R03;
AS '90 days or more' OVER
" " LABEL R12
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
END

This is what I'm looking for:

OVER RECAP R_TOTAL(3,*,3) = (R_TOTAL(*-1) - R_TOTAL(*-2) ) / R_TOTAL(*-2);

Well, this isn't exactly working. The 5th, 7th, 14th, 16th... cells are blank.

Francis,

Works OK from DS 7.1.6 against 7.6.2 servers -

TABLE FILE CAR
SUM DCOST
    RCOST
    COMPUTE DIFF_PCT/D6.2% = (RCOST - DCOST) / DCOST * 100;
ACROSS SEATS
FOR BODYTYPE
'CONVERTABLE' AS 'Convertable' LABEL R00 OVER
'COUPE'       AS 'Coupe'       LABEL R01 OVER
'HARDTOP'     AS 'Hardtop'     LABEL R02 OVER
'ROADSTER'    AS 'Roadster'    LABEL R03 OVER
'SEDAN'       AS 'Sedan'       LABEL R04 OVER
RECAP R_TOTAL = R00 + R01 + R02 + R03 + R04;  AS 'Total' OVER
RECAP R_TOTAL(3,*,3) = (R_TOTAL(*-1) - R_TOTAL(*-2)) / R_TOTAL(*-2) * 100;
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET BYDISPLAY OFF
ON TABLE SET STYLE *
&&INCLUDE.EVAL FSLLAND.FEX
TYPE=DATA, LABEL=R_TOTAL, BACKCOLOR=SILVER, $
ENDSTYLE
END

T

Tony,

Thanks for verifying that code. Your code and my sample fex does indeed work correctly. It's failing in my real fex. I've found that I cannot resize a field by doing something like SUM AMOUNT/D15 and expect the positional field numbers to be the displayed fields only, which makes sense. There's actually very little that's different between my sample and real fexes so I will have to go through the code with a fine-toothed comb...

The blank cells issue is caused by an extra field being added to the internal matrix (I suppose):

DEFINE FILE CAR
HEAD1/A100 = 'TEST CAR FRL REPORT';
END

TABLE FILE CAR
SUM DCOST
    RCOST
    COMPUTE DIFF_PCT/D6.2% = (RCOST - DCOST) / DCOST * 100;
ACROSS SEATS
FOR BODYTYPE
'CONVERTABLE' AS 'CONVERTABLE' LABEL R00 OVER
'COUPE'       AS 'COUPE'       LABEL R01 OVER
'HARDTOP'     AS 'HARDTOP'     LABEL R02 OVER
'ROADSTER'    AS 'ROADSTER'    LABEL R03 OVER
'SEDAN'       AS 'SEDAN'       LABEL R04 OVER
RECAP R_TOTAL = R00 + R01 + R02 + R03 + R04;  AS 'TOTAL' OVER
RECAP R_TOTAL(3,*,3) = (R_TOTAL(*-1) - R_TOTAL(*-2)) / R_TOTAL(*-2) * 100;

ON TABLE SUBHEAD
"<HEAD1"

ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET BYDISPLAY OFF
ON TABLE SET STYLE *
TYPE=DATA, LABEL=R_TOTAL, BACKCOLOR=SILVER, $
ENDSTYLE
END

Hey Francis,

I am not an EMR, FML, FRL person, but, in testing your CAR code, I changed:

RECAP R_TOTAL(3,*,3) = (R_TOTAL(*-1) - R_TOTAL(*-2)) / R_TOTAL(*-2) * 100;

to

RECAP R_TOTAL(3,3,3) = (R_TOTAL(*-1) - R_TOTAL(*-2)) / R_TOTAL(*-2) * 100;

and all buckets recalculated. Don't know if this is what you are looking for;

thought I'd add my 2 cents worth since you are not getting much help; no change required....

Tom,

Thanks for trying It does, indeed, appear work in the example, but, as expected, it does not work in my real-world fex.

The RECAP column addressing is as follows:

quote:

value(s,e,i)[/format]=

where

s - Is the starting column
e - Is the ending column (it may be * to denote all columns).
i - Is the increment factor

RECAP calcname(1,*,2)=expression;

The asterisk means to continue the RECAP for all odd-numbered columns (beginning in column 1, with an increment of 2, for all columns).

If I don't use *, the recalculation does not happen for the other occurrences of the column.

It appears to work for the CAR example because, by coincidence, Seats 4 and Seats 5 have only one row of values.

Here's another fex to illustrate this:

DEFINE FILE CAR
HEAD1/A100 = '<img src="http://www.informationbuilders.com/images/bulletin.jpg"></img>';
END

TABLE FILE CAR
SUM 
DCOST
RCOST
COMPUTE DIFF_PCT/D8.4% = (RCOST - DCOST) / DCOST * 100;
ACROSS COUNTRY 
FOR SEATS
'1' LABEL R01 OVER
'2' LABEL R02 OVER
'3' LABEL R03 OVER
'4' LABEL R04 OVER
'5' LABEL R05 OVER
'6' LABEL R06 OVER
RECAP RTOTAL1 = R01 + R02 + R03 + R04 + R05 + R06;  AS 'TOTAL' OVER
RECAP RTOTAL1(3,*,3) = (RTOTAL1(*-1) - RTOTAL1(*-2)) / RTOTAL1(*-2) * 100; OVER

RECAP RTOTAL2 = R01 + R02 + R03 + R04 + R05 + R06;  AS 'TOTAL' OVER
RECAP RTOTAL2(3,3,3) = (RTOTAL2(*-1) - RTOTAL2(*-2)) / RTOTAL2(*-2) * 100; 

-*ON TABLE SUBHEAD
-*"<HEAD1"

ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET BYDISPLAY OFF
END

Look at England, Italy and W Germany. England is OK for both versions. Italy and W Germany are OK for RECAP version 1, they're just totals of the percentages for RECAP version 2.

Now if the SUBHEAD is uncommented, things stop working for RECAP version 1.

Needless to say, this is quite annoying, and I've lost a few neurons over this.

Thanks very much.

Oh, and I'm not an EMR, FML, FRL person either! I was wondering if there are any improvements to the "Matrix" tab in Report Painter because being presented with a series of rows and columns doesn't appear to me to be very intuitive to point and click to create these inter-column and inter-row calculations.

Cheersthanksalot.