DEFINE FILE CAR ACROSS_FLD/A10 = COUNTRY; END TABLE FILE CAR SUM SALES DEALER_COST RETAIL_COST BY ACROSS_FLD WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE HOLD AS H1 END DEFINE FILE CAR ACROSS_FLD/A10 = 'Total'; END TABLE FILE CAR SUM SALES DEALER_COST RETAIL_COST BY ACROSS_FLD WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE HOLD AS H2 END TABLE FILE H1 SUM SALES DEALER_COST RETAIL_COST BY ACROSS_FLD ON TABLE HOLD AS ALL_REC MORE FILE H2 END TABLE FILE ALL_REC SUM SALES/D20CM DEALER_COST/D20CM RETAIL_COST/D20CM ACROSS ACROSS_FLD AS '' END
quote:Originally posted by BabakNYC:
Could you show us what the output would look like?
DEFINE FILE CAR DUMMY /I1 = 0; END TABLE FILE CAR SUM SALES/D8CM AS 'Total,Sales' BY DUMMY NOPRINT SUM SALES/D8CM AS 'Sales' DEALER_COST/D8CM AS 'Dealer,Cost' RETAIL_COST/D8CM AS 'Retail,Cost' BY DUMMY NOPRINT ACROSS COUNTRY AS '' WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; END
DEFINE FILE CAR ACROSS_FLD/A10 = COUNTRY; END TABLE FILE CAR SUM SALES/D8CM AS 'MEAS' BY TOTAL COMPUTE ACROSS_ID /I2 = 1; BY TOTAL COMPUTE MEAS_ID /I2 = 1; BY TOTAL COMPUTE MEAS_TXT /A20V= 'Sales'; BY ACROSS_FLD WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE HOLD AS H10 END -RUN TABLE FILE CAR SUM DEALER_COST/D8CM AS 'MEAS' BY TOTAL COMPUTE ACROSS_ID /I2 = 1; BY TOTAL COMPUTE MEAS_ID /I2 = 2; BY TOTAL COMPUTE MEAS_TXT /A20V= 'Dealer Cost'; BY ACROSS_FLD WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE HOLD AS H11 END -RUN TABLE FILE CAR SUM RETAIL_COST/D8CM AS 'MEAS' BY TOTAL COMPUTE ACROSS_ID /I2 = 1; BY TOTAL COMPUTE MEAS_ID /I2 = 3; BY TOTAL COMPUTE MEAS_TXT /A20V= 'Retail Cost'; BY ACROSS_FLD WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE HOLD AS H12 END -RUN DEFINE FILE CAR ACROSS_FLD/A10 = 'Total'; END TABLE FILE CAR SUM SALES/D8CM AS 'MEAS' BY TOTAL COMPUTE ACROSS_ID /I2 = 99; BY TOTAL COMPUTE MEAS_ID /I2 = 4; BY TOTAL COMPUTE MEAS_TXT /A20V= 'Sales'; BY ACROSS_FLD WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE HOLD AS H2 END -RUN TABLE FILE H10 SUM MEAS BY ACROSS_ID BY MEAS_ID BY MEAS_TXT BY ACROSS_FLD ON TABLE HOLD AS ALL_REC MORE FILE H11 MORE FILE H12 MORE FILE H2 END -RUN TABLE FILE ALL_REC SUM MEAS ACROSS ACROSS_FLD AS '' ACROSS MEAS_ID NOPRINT ACROSS ACROSS_ID NOPRINT ACROSS MEAS_TXT AS '' END -RUN
quote:Originally posted by MartinY:
Here two options for you
-1- That puts the Total on left sideDEFINE FILE CAR DUMMY /I1 = 0; END TABLE FILE CAR SUM SALES/D8CM AS 'Total,Sales' BY DUMMY NOPRINT SUM SALES/D8CM AS 'Sales' DEALER_COST/D8CM AS 'Dealer,Cost' RETAIL_COST/D8CM AS 'Retail,Cost' BY DUMMY NOPRINT ACROSS COUNTRY AS '' WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; END
-2- Where Total is on rightDEFINE FILE CAR ACROSS_FLD/A10 = COUNTRY; END TABLE FILE CAR SUM SALES/D8CM AS 'MEAS' BY TOTAL COMPUTE ACROSS_ID /I2 = 1; BY TOTAL COMPUTE MEAS_ID /I2 = 1; BY TOTAL COMPUTE MEAS_TXT /A20V= 'Sales'; BY ACROSS_FLD WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE HOLD AS H10 END -RUN TABLE FILE CAR SUM DEALER_COST/D8CM AS 'MEAS' BY TOTAL COMPUTE ACROSS_ID /I2 = 1; BY TOTAL COMPUTE MEAS_ID /I2 = 2; BY TOTAL COMPUTE MEAS_TXT /A20V= 'Dealer Cost'; BY ACROSS_FLD WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE HOLD AS H11 END -RUN TABLE FILE CAR SUM RETAIL_COST/D8CM AS 'MEAS' BY TOTAL COMPUTE ACROSS_ID /I2 = 1; BY TOTAL COMPUTE MEAS_ID /I2 = 3; BY TOTAL COMPUTE MEAS_TXT /A20V= 'Retail Cost'; BY ACROSS_FLD WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE HOLD AS H12 END -RUN DEFINE FILE CAR ACROSS_FLD/A10 = 'Total'; END TABLE FILE CAR SUM SALES/D8CM AS 'MEAS' BY TOTAL COMPUTE ACROSS_ID /I2 = 99; BY TOTAL COMPUTE MEAS_ID /I2 = 4; BY TOTAL COMPUTE MEAS_TXT /A20V= 'Sales'; BY ACROSS_FLD WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE HOLD AS H2 END -RUN TABLE FILE H10 SUM MEAS BY ACROSS_ID BY MEAS_ID BY MEAS_TXT BY ACROSS_FLD ON TABLE HOLD AS ALL_REC MORE FILE H11 MORE FILE H12 MORE FILE H2 END -RUN TABLE FILE ALL_REC SUM MEAS ACROSS ACROSS_FLD AS '' ACROSS MEAS_ID NOPRINT ACROSS ACROSS_ID NOPRINT ACROSS MEAS_TXT AS '' END -RUN
DEFINE FILE CAR DUMMY /I1 = 0; END TABLE FILE CAR SUM SALES NOPRINT DEALER_COST NOPRINT RETAIL_COST NOPRINT BY DUMMY NOPRINT SUM SALES/D8C AS 'Sales' DEALER_COST/D8CM AS 'Dealer,Cost' RETAIL_COST/D8CM AS 'Retail,Cost' BY DUMMY NOPRINT ACROSS COUNTRY AS '' COMPUTE TOTAL/D8CM = C1 + C2 + C3; AS 'Total,Measures' WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE SET PAGE NOLEAD ON TABLE SET STYLE * grid=off, $ ENDSTYLE END
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | ||
WebFOCUS App Studio 8.2.06 standalone on Windows 10 |
DEFINE FILE CAR ACROSS_FLD/A10 = COUNTRY; END TABLE FILE CAR SUM COMPUTE MEAS/A20V = FPRINT(SEATS, 'D8C', 'A20V'); BY TOTAL COMPUTE ACROSS_ID /I2 = 1; BY TOTAL COMPUTE MEAS_ID /I2 = 1; BY TOTAL COMPUTE MEAS_TXT /A20V= 'Seats'; BY ACROSS_FLD WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE HOLD AS H10 END -RUN TABLE FILE CAR SUM COMPUTE MEAS/A20V = FPRINT(DEALER_COST, 'D8CM', 'A20V'); BY TOTAL COMPUTE ACROSS_ID /I2 = 1; BY TOTAL COMPUTE MEAS_ID /I2 = 2; BY TOTAL COMPUTE MEAS_TXT /A20V= 'Dealer Cost'; BY ACROSS_FLD WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE HOLD AS H11 END -RUN TABLE FILE CAR SUM COMPUTE MEAS/A20V = FPRINT(RETAIL_COST, 'D8CM', 'A20V'); BY TOTAL COMPUTE ACROSS_ID /I2 = 1; BY TOTAL COMPUTE MEAS_ID /I2 = 3; BY TOTAL COMPUTE MEAS_TXT /A20V= 'Retail Cost'; BY ACROSS_FLD WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE HOLD AS H12 END -RUN DEFINE FILE CAR ACROSS_FLD/A10 = 'Total'; END TABLE FILE CAR SUM COMPUTE MEAS/A20V = FPRINT(SALES, 'D8CM', 'A20V'); BY TOTAL COMPUTE ACROSS_ID /I2 = 99; BY TOTAL COMPUTE MEAS_ID /I2 = 4; BY TOTAL COMPUTE MEAS_TXT /A20V= 'Sales'; BY ACROSS_FLD WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE HOLD AS H2 END -RUN TABLE FILE H10 SUM MEAS BY ACROSS_ID BY MEAS_ID BY MEAS_TXT BY ACROSS_FLD ON TABLE HOLD AS ALL_REC MORE FILE H11 MORE FILE H12 MORE FILE H2 END -RUN TABLE FILE ALL_REC SUM MEAS ACROSS ACROSS_FLD AS '' ACROSS MEAS_ID NOPRINT ACROSS ACROSS_ID NOPRINT ACROSS MEAS_TXT AS '' ON TABLE SET STYLE * TYPE=DATA, COLUMN=MEAS, JUSTIFY=RIGHT, $ ENDSTYLE END -RUN
quote:Originally posted by MartinY:
Using the solution that you've chosen, this is one way without too many chances to original code
DEFINE FILE CAR DUMMY /I1 = 0; END TABLE FILE CAR SUM SALES NOPRINT SEATS NOPRINT DEALER_COST NOPRINT RETAIL_COST NOPRINT BY DUMMY NOPRINT SUM COMPUTE FMT/A8 = DECODE COUNTRY ('ENGLAND' 'D8C!L' 'JAPAN' 'D8CM' ELSE 'D8C!E'); NOPRINT SEATS/D8C AS 'Sales' DEALER_COST/FMT AS 'Dealer,Cost' RETAIL_COST/FMT AS 'Retail,Cost' BY DUMMY NOPRINT ACROSS COUNTRY AS '' COMPUTE TOTAL/D8CM = C1; AS 'Total,Sales' WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE PCHOLD FORMAT XLSX ON TABLE SET PAGE NOLEAD ON TABLE SET STYLE * grid=off, $ ENDSTYLE END
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | ||
WebFOCUS App Studio 8.2.06 standalone on Windows 10 |
quote:Originally posted by Tony A:
Don't forget that dynamic formatting is possible. This will ensure that your numerics in MS Excel are still numbers.
I know that Martin has put a lot of effort into his examples, and aplaud him for that, such a helpful Guy. But Martin, you're working the data too hardDEFINE FILE CAR DUMMY /I1 = 0; END TABLE FILE CAR SUM SALES NOPRINT SEATS NOPRINT DEALER_COST NOPRINT RETAIL_COST NOPRINT BY DUMMY NOPRINT SUM COMPUTE FMT/A8 = DECODE COUNTRY ('ENGLAND' 'D8C!L' 'JAPAN' 'D8CM' ELSE 'D8C!E'); NOPRINT SEATS/D8C AS 'Sales' DEALER_COST/FMT AS 'Dealer,Cost' RETAIL_COST/FMT AS 'Retail,Cost' BY DUMMY NOPRINT ACROSS COUNTRY AS '' COMPUTE TOTAL/D8CM = C1; AS 'Total,Sales' WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN'; ON TABLE PCHOLD FORMAT XLSX ON TABLE SET PAGE NOLEAD ON TABLE SET STYLE * grid=off, $ ENDSTYLE END
T
quote:Originally posted by Tony A:
Don't forget that dynamic formatting is possible.