All-

I am writing a report using a left outer join.

one data-set looks like this:

ID, Color
0001, Red
0001, Green
0001, Blue
0003, Red

another data-set looks like this:

ID, Name
0001,Joe Smith
0002,Bob Smith
0003,Jane Smith

The join looks like this

JOIN
LEFT_OUTER ID IN UD0 TO MULTIPLE ID IN UD1
AS J5
END

(There's a define on this that reads as such.)

DEFINE FILE UD1
CNTR/I9 WITH COLOR = 1;
END

I go on to write a SUM

TABLE UD1
SUM CNTR AS ''
BY NAME
ACROSS COLOR
ON COLOR SUBTOTAL AS '*Total'

END

Which gives the output:

                          Red     Green     Blue     *Total
Joe Smith       .     1           1           1           3
Bob Smith      0     .           .            .            0
Jane Smith     .     1           .            .            1

This is almost exactly what my supervisor needs to see. However, how do i suppress the first SUM/ACROSS (i think the above text will show up in red for emphasis) column from printing?


Thanks for your input, it's appreciated,

Drew

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

How about straightening out your code first?

The sample report you posted does not correspond with the (incorrect code) - ACROSS and BY are reversed - SUBTOTAL should be ACROSS-TOTAL, etc.

Let's start with this:

FILEDEF UD0 DISK ud0.txt
FILEDEF UD1 DISK ud1.txt
-RUN

-WRITE UD0 0001 Red
-WRITE UD0 0001 Green
-WRITE UD0 0001 Blue
-WRITE UD0 0003 Red

-WRITE UD1 0001 Joe Smith
-WRITE UD1 0002 Bob Smith
-WRITE UD1 0003 Jane Smith

FILEDEF MASTER DISK UD0.mas
-RUN

-WRITE MASTER FILENAME=UD0, SUFFIX=FIX
-WRITE MASTER SEGNAME=UD0
-WRITE MASTER FIELDNAME=ID    , USAGE=A04, ACTUAL=A04, $
-WRITE MASTER FIELDNAME=FIL1  , USAGE=A01, ACTUAL=A01, $
-WRITE MASTER FIELDNAME=COLOUR, USAGE=A05, ACTUAL=A05, $
-RUN

FILEDEF MASTER DISK UD1.mas
-RUN

-WRITE MASTER FILENAME=UD1, SUFFIX=FIX
-WRITE MASTER SEGNAME=UD1
-WRITE MASTER FIELDNAME=ID    , USAGE=A04, ACTUAL=A04, $
-WRITE MASTER FIELDNAME=FIL1  , USAGE=A01, ACTUAL=A01, $
-WRITE MASTER FIELDNAME=NAME  , USAGE=A10, ACTUAL=A10, $
-RUN

JOIN
LEFT_OUTER ID IN UD1 TO MULTIPLE ID IN UD0
AS J5
END
-RUN

TABLE FILE UD1
SUM CNT.UD1.ID AS ''
BY NAME AS 'Name'
ACROSS COLOUR AS '' ACROSS-TOTAL AS 'Total'

ON TABLE NOTOTAL
ON TABLE SET PAGE NOPAGE
ON TABLE SET ONLINE-FMT STANDARD
END
-RUN

Gives you:

                     Blue   Green  Red    Total  
  Name                                           
  -----------------------------------------------
  Bob Smith       1      0      0      0      1  
  Jane Smith      0      0      0      1      1  
  Joe Smith       0      1      1      1      3  

Francis,

How do i suppress the very first column?

Data surrounded in underscores:

quote:

                     Blue   Green  Red    Total  
  Name                                           
  -----------------------------------------------
  Bob Smith       _1_      0      0      0      1  
  Jane Smith      _0_      0      0      1      1  
  Joe Smith       _0_      1      1      1      3  

Thanks for your response.

That column is there because of your left outer join. If you add "WHERE UD1.ID IS NOT MISSING" Bob will not appear at all, because he has a blue colour which is not in the cross-referenced table.

How do i get bob to both appear and the first column to be suppressed?

That's the sixty-four dollar question. WebFOCUS normally wants to report on data that actually exists.

You could try creating an intermediary HOLD file as in this example, but if there are many ACROSS COLUMN values this may not be a good idea.

FILEDEF UD0 DISK ud0.txt
FILEDEF UD1 DISK ud1.txt
-RUN

-WRITE UD0 0001 Red
-WRITE UD0 0001 Green
-WRITE UD0 0001 Blue
-WRITE UD0 0003 Red

-WRITE UD1 0001 Joe Smith
-WRITE UD1 0002 Bob Smith
-WRITE UD1 0003 Jane Smith

FILEDEF MASTER DISK UD0.mas
-RUN

-WRITE MASTER FILENAME=UD0, SUFFIX=FIX
-WRITE MASTER SEGNAME=UD0
-WRITE MASTER FIELDNAME=ID    , USAGE=A04, ACTUAL=A04, $
-WRITE MASTER FIELDNAME=FIL1  , USAGE=A01, ACTUAL=A01, $
-WRITE MASTER FIELDNAME=COLOUR, USAGE=A05, ACTUAL=A05, $
-RUN

FILEDEF MASTER DISK UD1.mas
-RUN

-WRITE MASTER FILENAME=UD1, SUFFIX=FIX
-WRITE MASTER SEGNAME=UD1
-WRITE MASTER FIELDNAME=ID    , USAGE=A04, ACTUAL=A04, $
-WRITE MASTER FIELDNAME=FIL1  , USAGE=A01, ACTUAL=A01, $
-WRITE MASTER FIELDNAME=NAME  , USAGE=A10, ACTUAL=A10, $
-RUN

SET HOLDLIST=PRINTONLY
SET ASNAMES=ON
SET HOLDFORMAT=ALPHA
SET PAGE=NOLEAD
-RUN

JOIN
LEFT_OUTER ID IN UD1 TO ALL ID IN UD0
AS J5
END
-RUN

TABLE FILE UD1
SUM
CNT.UD0.ID AS ''
BY NAME
ACROSS COLOUR AS COL
ON TABLE HOLD AS H001
END
-RUN

?FF H001

TABLE FILE H001
PRINT 
NAME AS 'Name'
Blue
Green
Red
ON TABLE SET ONLINE-FMT STANDARD
END
-RUN

Using Francis' example code up to the join, but then putting in this code:

JOIN LEFT_OUTER ID IN UD1 TO MULTIPLE ID IN UD0 AS J5

DEFINE FILE UD1
NR/I5S = IF COLOUR EQ ' ' THEN 0 ELSE 1;
END

TABLE FILE UD1
SUM NR AS ''
BY NAME AS 'Name'
ACROSS COLOUR AS '' ACROSS-TOTAL AS 'Total'

ON TABLE NOTOTAL
ON TABLE SET PAGE NOPAGE
ON TABLE SET NODATA ' '
ON TABLE SET ONLINE-FMT STANDARD
END
-RUN  

will get you Bob to appear and the first column to not appear.
The first column actually still is there, but since every item in the column is showing as spaces, you don't see it any more.