Is there a way to skip a field that does not exist in an OVER? I'm trying to print lines of notes under some data but dont want a blank line when the note field is missing. I would use a SUBFOOT but that is making the last note before the SUBTOTAL fall under the SUBTOTAL (because of some sorting issues required in the report)
Its basically like this:
PRINT MODEL BY MANUFACTURE OVER NOTEFIELD END
NOTEFIELD is usually null, but I want it to print under model when it exists, but not leave a blank line when its null.
This is the results I would like: FORD Windstar Tauraus note: This model has been discontinued. F150 Chevrolet Silverado CorvetteThis message has been edited. Last edited by: Kerry,
8105 Tomcat and AIX reporting server input: Teradata, SQL, DB2, Essbase, Oracle, text output:html, excel, PDF,
November 22, 2008, 07:41 AM
FrankDutch
I'm sure there are many ways to get this done. It depends how this information is in your database but suppose you have a field INSTOCK per model that holds always an 'A' for Available and an 'D' for discontinued, then I would do this.
DEFINE FILE CAR
NEWMODEL/A50=MODEL;
LINENO/I1=1;
END
TABLE FILE CAR
PRINT
NEWMODEL
BY MANUFACTURE
BY LINENO
ON TABLE HOLD AS HOLD1
END
DEFINE FILE CAR
NEWMODEL/A50="note: This model has been discontinued";
LINENO/I1=2;
END
TABLE FILE CAR
PRINT
NEWMODEL
BY MANUFACTURE
BY LINENO
WHERE INSTOCK EQ 'D';
ON TABLE HOLD AS HOLD2
END
-RUN
TABLE FILE HOLD1
PRINT *
ON TABLE HOLD AS HOLD3
MORE
FILE HOLD2
END
-RUN
TABLE FILE HOLD3
PRINT NEWMODEL AS ''
BY LINENO NOPRINT
BY MANUFACTURE
END
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
November 22, 2008, 11:26 AM
<JG>
The normal way to do this is The MacGyver Technique.
Specifically the topic Eliminating Rows Selectively
DEFINE FILE CAR
REMARK/A3=IF INSTOCK EQ 'D' THEN '*)' ELSE '';
END
TABLE FILE CAR
PRINT
MODEL
REMARK AS ''
BY MANUFACTURE
ON TABLE SUBFOOT
"*) not in stock..."
END
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
November 23, 2008, 04:11 AM
<JG>
Frank, that unfortunately leaves a blank line
however
TABLE FILE CAR
PRINT
CAR OVER
MODEL
BY CAR NOPRINT
ON CAR SUBFOOT
"SALES <SALES"
WHEN SALES NE 0;
ON TABLE SET STYLE *
TYPE=SUBFOOT,HEADALIGN=BODY,$
ENDSTYLE
END
Delivers what is wanted I believe.
November 24, 2008, 08:53 AM
PBrightwell
What you want is a conditional subfoot. You need to have a BY statement on the field you are checking or you may not get the results you want.
TABLE FILE CAR
PRINT
MODEL
BY CAR
BY MODEL NOPRINT
ON MODEL SUBFOOT
"This model has been discontinued"
WHEN MODEL EQ '100 LS 2 DOOR AUTO'
END
Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes
November 24, 2008, 10:02 AM
<JG>
Pat, What KENFR wants is a the contents of a column displayed if it is not missing.
That's what I posted just above.
November 24, 2008, 10:53 AM
FrankDutch
I would suggest to test this little code.
-* File test111.fex
DEFINE FILE CAR
NEWMODEL/A50=MODEL;
LINENO/I1=1;
END
TABLE FILE CAR
PRINT
MODEL NEWMODEL SALES
BY CAR
BY LINENO
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD1
END
DEFINE FILE CAR
NEWMODEL/A50='note: This model has been discontinued';
LINENO/I1=2;
END
TABLE FILE CAR
PRINT
MODEL NEWMODEL SALES
BY CAR
BY LINENO
WHERE SALES LT 5000;
ON TABLE HOLD AS HOLD2
END
-RUN
TABLE FILE HOLD1
PRINT *
ON TABLE HOLD AS HOLD3
MORE
FILE HOLD2
END
-RUN
TABLE FILE HOLD3
PRINT NEWMODEL AS ''
BY CAR
BY MODEL NOPRINT
BY LINENO NOPRINT
END
and tell me if it works and gives you the desired result!
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
November 24, 2008, 05:10 PM
Frans
Or in one pass:
DEFINE FILE CAR DISC/A40 = IF SEATS LE 2 THEN 'This model has been discontinued' ELSE MODEL; END
TABLE FILE CAR PRINT MODEL AS '' OVER DISC AS '' BY CAR WHERE DISC NE 'This model has been discontinued'; MORE FILE CAR WHERE DISC EQ 'This model has been discontinued'; END
I've got to read up on the MORE statement; I've not used it before. That looks like a possibility. The subfoot would be difficult because of a sort issue. Thanks for the suggestions
8105 Tomcat and AIX reporting server input: Teradata, SQL, DB2, Essbase, Oracle, text output:html, excel, PDF,
November 25, 2008, 10:14 AM
PBrightwell
quote:
This is the results I would like: FORD Windstar Tauraus note: This model has been discontinued. F150 Chevrolet Silverado Corvette
This is what you need
PRINT
MODEL
BY MANUFACTURE
BY
NOTEFIELD NOPRINT
ON NOTEFIELD SUBFOOT
"<NOTEFIELD "
WHEN NOTEFIELD IS NOT MISSING
END
JG Your way will print the CAR for every Model.
Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes
November 25, 2008, 03:41 PM
KenFR
PBrightwell, that works except for manufacture has a SUBTOTAL and when there is a notefield on the last manufacture; it prints below the subtotal instead of above like I would expect it to.
8105 Tomcat and AIX reporting server input: Teradata, SQL, DB2, Essbase, Oracle, text output:html, excel, PDF,
November 25, 2008, 04:12 PM
KenFR
quote:
sub
In the output below I would think the "notefield test data3" would be below CROWN VIC instead of under the subtotal
BY MANUFACTURE NOPRINT BY NOTEFIELD NOPRINT ON NOTEFIELD SUBFOOT " WHEN NOTEFIELD IS-NOT MISSING
ON MANUFACTURE SUBHEAD " ON MANUFACTURE SUBTOTAL COST AS ' Total'
FORD WINDSTAR 100 200 notefield test data1 FOCUS 200 320 F150 175 220 TOWN CAR 150 300 notefield test data2 CROWN VIC 210 400 Total FORD 835 1440 notefield test data3 CHEVROLET BLAZER 152 240 TAHOE 120 330.....
8105 Tomcat and AIX reporting server input: Teradata, SQL, DB2, Essbase, Oracle, text output:html, excel, PDF,
November 25, 2008, 05:30 PM
PBrightwell
Instead of doing subtotal, try a recap with a subfoot on car.
ON CAR RECAP STTL/D15.2=ST.SALES;
ON CAR SUBFOOT
"<CAR <STTL"
This message has been edited. Last edited by: PBrightwell,
Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes