Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] conditional OVER?
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] conditional OVER?
 Login/Join
 
Silver Member
posted
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,


8105 Tomcat and AIX reporting server
input: Teradata, SQL, DB2, Essbase, Oracle, text
output:html, excel, PDF,
 
Posts: 35 | Location: DFW Texas | Registered: May 30, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
<JG>
posted
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
 
Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
<JG>
posted
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.
 
Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 755 | Location: TX | Registered: September 25, 2007Reply With QuoteReport This Post
<JG>
posted
Pat, What KENFR wants is a the contents of a column displayed if it is not missing.

That's what I posted just above.
 
Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
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


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 423 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
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,
 
Posts: 35 | Location: DFW Texas | Registered: May 30, 2008Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 755 | Location: TX | Registered: September 25, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
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,
 
Posts: 35 | Location: DFW Texas | Registered: May 30, 2008Reply With QuoteReport This Post
Silver Member
posted Hide Post
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,
 
Posts: 35 | Location: DFW Texas | Registered: May 30, 2008Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 755 | Location: TX | Registered: September 25, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] conditional OVER?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.