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
Corvette

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

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

The normal way to do this is The MacGyver Technique.

Specifically the topic Eliminating Rows Selectively

Use the link on the forum Tips a Techniques page

or go directly there

http://techsupport.informationbuilders.com/bestpractices/macgyver/toc.html

There might be an easier way

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, 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.

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, What KENFR wants is a the contents of a column displayed if it is not missing.

That's what I posted just above.

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!

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

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.

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.

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,

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,