[CLOSED] column totals need to display for each by field at the end of report
Hi All,
I want to display the total for each by field in totals section. Here is the example code
TABLE FILE CAR PRINT 'CAR.BODY.SEATS' AS 'SEATS' 'CAR.BODY.DEALER_COST' BY 'CAR.COMP.CAR' BY 'CAR.CARREC.MODEL' HEADING "" FOOTING "ON TABLE SET PAGE-NUM OFF ON TABLE COLUMN-TOTAL AS 'TOTAL' END
In this example i want to display the dealer_cost based on the seat number in two columns like below i.e. total: seatno - 2 dealer_cost - 101010$ seatno - 3 dealer_cost - 101010$
No need to display the description just numbers.
Please help me........ Thanks in advance.......!
WF 7.6.11, windows xp
Thakns, Koti.This message has been edited. Last edited by: Kerry,
WebFOCUS 8104/7703/769/764, Windows 7, Oracle 11g, DB2, QAD
July 09, 2012, 12:40 PM
Francis Mariani
Something like this?
DEFINE FILE CAR
TOT2/D6 = IF SEATS EQ 2 THEN DEALER_COST ELSE 0;
TOT4/D6 = IF SEATS EQ 4 THEN DEALER_COST ELSE 0;
TOT5/D6 = IF SEATS EQ 5 THEN DEALER_COST ELSE 0;
END
TABLE FILE CAR
PRINT
'CAR.BODY.SEATS' AS 'SEATS'
'CAR.BODY.DEALER_COST'
BY 'CAR.COMP.CAR'
BY 'CAR.CARREC.MODEL'
HEADING
""
FOOTING
"TOTAL SEATS 2: <TOT.TOT2"
"TOTAL SEATS 4: <TOT.TOT4"
"TOTAL SEATS 5: <TOT.TOT5"
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
July 09, 2012, 12:43 PM
ReddyP
TABLE FILE CAR SUM DEALER_COST ACROSS SEATS BY CAR BY MODEL END
7.7.01,windows2008 R2
July 10, 2012, 05:24 AM
koti
Hi Francis,
You are correct in this example. But in my case i have many number of rows under seats column its around 40 and these are displaying dynamically based on the car.
Please help me.......... thanks in advance.....!
Koti, WF 7.6.11 windows xp.
WebFOCUS 8104/7703/769/764, Windows 7, Oracle 11g, DB2, QAD
July 10, 2012, 10:57 PM
Francis Mariani
You could build something dynamic, though I'm not sure that this solves the "based on the car" requirement.
-SET &ECHO=ON;
TABLE FILE CAR
SUM
MIN.SEATS NOPRINT
BY SEATS
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN
-SET &SEAT_COUNT = &LINES;
DEFINE FILE CAR
-REPEAT ENDREP1 &SEAT_COUNT TIMES
-READFILE H001
&SEATS.TOT/D6 = IF SEATS EQ &SEATS THEN DEALER_COST ELSE 0;
-ENDREP1
-CLOSE H001
END
TABLE FILE CAR
PRINT
'CAR.BODY.SEATS' AS 'SEATS'
'CAR.BODY.DEALER_COST'
BY 'CAR.COMP.CAR'
BY 'CAR.CARREC.MODEL'
HEADING
""
FOOTING
-REPEAT ENDREP2 &SEAT_COUNT TIMES
-
-READFILE H001
-SET &TOTF = '<TOT.' || LJUST(&SEATS.LENGTH, &SEATS, 'A10') || 'TOT';
"TOTAL SEATS &SEATS|: &TOTF"
-ENDREP2
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
July 11, 2012, 03:29 AM
Danny-SRL
Koti, You wrote:
quote:
TABLE FILE CAR PRINT 'CAR.BODY.SEATS' AS 'SEATS' 'CAR.BODY.DEALER_COST' BY 'CAR.COMP.CAR' BY 'CAR.CARREC.MODEL' HEADING "" FOOTING "ON TABLE SET PAGE-NUM OFF ON TABLE COLUMN-TOTAL AS 'TOTAL' END
Could you post your example again between the CODE tags (press on the right-most icon of the message window)?
Also, do I understand correctly that at the end of your output, you want to see totals of DEALER_COST for 2 values of SEATS? Or for all values of SEATS? SEATS not being a sort field?
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
July 11, 2012, 11:23 AM
Francis Mariani
Are Koteswar and koti the same person?
-SET &ECHO=ON;
-* Determine dimension values to be shown it total rows
TABLE FILE CAR
SUM
MIN.SEATS NOPRINT
BY SEATS
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN
-SET &SEAT_COUNT = &LINES;
-* Set up dimension total columns
DEFINE FILE CAR
-REPEAT ENDREP1 &SEAT_COUNT TIMES
-READFILE H001
&SEATS.TOT/D6 = IF SEATS EQ &SEATS THEN DEALER_COST ELSE 0;
-ENDREP1
-CLOSE H001
END
-RUN
-* Create report
TABLE FILE CAR
PRINT
'CAR.BODY.SEATS' AS 'SEATS'
'CAR.BODY.DEALER_COST'
BY 'CAR.COMP.CAR'
BY 'CAR.CARREC.MODEL'
HEADING
"Car Report"
" "
FOOTING
-* Set up dimension total rows
-REPEAT ENDREP2 &SEAT_COUNT TIMES
-READFILE H001
-SET &TOTF = '<TOT.' || LJUST(&SEATS.LENGTH, &SEATS, 'A10') || 'TOT';
"TOTAL SEATS &SEATS|: &TOTF"
-ENDREP2
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END
-RUN
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server