Focal Point
[solved]SHOW COLUMN VALUE ON RECAP IN FML

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4917037036

May 10, 2013, 11:50 AM
Kamesh
[solved]SHOW COLUMN VALUE ON RECAP IN FML
I am creating an FML report and would like to show the column value on RECAP also.

see the following code.

DEFINE FILE CAR
PERIOD/A20=IF CAR EQ 'AUDI' THEN '12/31/11'
ELSE IF CAR EQ 'BMW' THEN '1/1/12 - 12/31/12'
ELSE IF CAR EQ 'DATSUN' THEN '12/31/12'
ELSE IF CAR EQ 'JENSEN' THEN '1/1/12 - 12/31/12'
ELSE IF CAR EQ 'MASERATI' THEN '1/1/12 - 12/31/12'
ELSE '';
END

TABLE FILE CAR
SUM
PERIOD DEALER_COST RETAIL_COST
FOR CAR
AUDI AS 'ROW1' OVER
BMW AS 'ROW2' OVER
DATSUN AS 'ROW3' OVER
BAR AS '-' OVER
RECAP NET/D8=R1 + R2 - R3 ; AS '' OVER
JENSEN AS 'ROW5' OVER
MASERATI AS 'ROW6' OVER
BAR AS '=' OVER
RECAP PER/D8.1%=((R6 / R4) * 100); AS 'PERCENT'
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'DATSUN' OR 'JENSEN' OR 'MASERATI'
ON TABLE SET PAGE-NUM NOPAGE
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT,GRID=OFF,FONT=ARIAL,SIZE=9,$
TYPE=TITLE,COLUMN=LIFE,JUSTIFY=RIGHT,$
ENDSTYLE
END
-EXIT

From the above output, I want to see the PERIOD column value in front of RECAP value.

How can I show that?

Thanks in advance,

This message has been edited. Last edited by: Kamesh,


WFConsultant

WF 8105M on Win7/Tomcat
May 10, 2013, 01:08 PM
Rao D
Is this something you are looking for?

DEFINE FILE CAR
PERIOD/A20=IF CAR EQ 'AUDI' THEN '12/31/11'
ELSE IF CAR EQ 'BMW' THEN '1/1/12 - 12/31/12'
ELSE IF CAR EQ 'DATSUN' THEN '12/31/12'
ELSE IF CAR EQ 'JENSEN' THEN '1/1/12 - 12/31/12'
ELSE IF CAR EQ 'MASERATI' THEN '1/1/12 - 12/31/12'
ELSE '';
END

TABLE FILE CAR
SUM
PERIOD
BY CAR NOPRINT
SUM
DEALER_COST RETAIL_COST
BY CAR NOPRINT
FOR CAR
AUDI AS 'ROW1' OVER
BMW AS 'ROW2' OVER
DATSUN AS 'ROW3' OVER
BAR AS '-' OVER
RECAP NET/D8=R1 + R2 - R3 ; AS 'NET' OVER
JENSEN AS 'ROW5' OVER
MASERATI AS 'ROW6' OVER
BAR AS '=' OVER
RECAP PER/D8.1%=((R6 / R4) * 100); AS 'PERCENT'
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'DATSUN' OR 'JENSEN' OR 'MASERATI'
ON TABLE SET PAGE-NUM NOPAGE
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT,GRID=OFF,FONT=ARIAL,SIZE=9,$
TYPE=TITLE,COLUMN=LIFE,JUSTIFY=RIGHT,$
ENDSTYLE
END
-EXIT


WebFOCUS - ver8201
[ReportingServers: Windows 64bit;
Client: tomcat and IIS on windows 2012
AppStudio

May 10, 2013, 01:40 PM
Kamesh
Thanks Rao for looking in to it but that's not what I am looking for. Anyway I found a solution.

set my desired output on the Nodata value and that solved my purpose.

DEFINE FILE CAR
PERIOD/A20=IF CAR EQ 'AUDI' THEN '12/31/11'
ELSE IF CAR EQ 'BMW' THEN '1/1/12 - 12/31/12'
ELSE IF CAR EQ 'DATSUN' THEN '12/31/12'
ELSE IF CAR EQ 'JENSEN' THEN '1/1/12 - 12/31/12'
ELSE IF CAR EQ 'MASERATI' THEN '1/1/12 - 12/31/12'
ELSE '';
END

SET NODATA='12/31/12'
TABLE FILE CAR
SUM
PERIOD DEALER_COST RETAIL_COST
FOR CAR
AUDI AS 'ROW1' OVER
BMW AS 'ROW2' OVER
DATSUN AS 'ROW3' OVER
BAR AS '-' OVER
RECAP NET/D8=R1 + R2 - R3 ; AS '' OVER
JENSEN AS 'ROW5' OVER
MASERATI AS 'ROW6' OVER
BAR AS '=' OVER
RECAP PER/D8.1%=((R6 / R4) * 100); AS 'PERCENT'
WHERE CAR EQ 'AUDI' OR 'BMW' OR 'DATSUN' OR 'JENSEN' OR 'MASERATI'
ON TABLE SET PAGE-NUM NOPAGE
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT,GRID=OFF,FONT=ARIAL,SIZE=9,$
TYPE=TITLE,COLUMN=LIFE,JUSTIFY=RIGHT,$
ENDSTYLE
END
-EXIT


WFConsultant

WF 8105M on Win7/Tomcat