In SQL I can create views to achieve this, can anyone help on how to do it in WF

I have a table like the one below:
Name trandate type countA Count B CountC
John 20110512 book1 105 27 18
John 20110512 book2 17 25 10
Amy 20110512 book1 108 7 78
Amy 20110512 book2 99 31 16
Bill 20110512 book1 125 2 41
Bill 20110512 book2 36 25 25
John 20110512 book3 41 22 2
Amy 20110512 book3 88 14 3

I need the report to look like the following:
Name trandate type cnta cntb cntc type cnta cntb cntc type cnta cntb cnt3
John 20110512 book1 105 27 18 book2 17 25 10 book3 41 22 2
Amy 20110512 book1 108 7 78 book2 99 31 16 book3 88 14 3
Bill 20110512 book1 125 2 41 book2 36 25 25 book3 0 0 0

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

TABLE FILE LIBRARY
SUM
MAX.NAME
MAX.TRANDATE
CNT.COUNTA
CNT.COUNTB
CNT.COUNTC
ACROSS TYPE
END

THX will give it a try

I get the following
Name trandate type cnta cntb cntc type cnta cntb cntc type cnta cntb cnt3

John 20110512 book1 3 3 3 book2 3 3 3 book3 2 2 2

Only 1 row and it appears to be counting the number of rows rather that adding the counts

What Don is suggesting is this.

EX -LINES 10 EDAPUT MASTER,TMP_DATA,CV,FILE
FILENAME=TMP_DATA, SUFFIX=FIX,$
SEGNAME=TMP_DATA, $
  FIELD=Name    ,ALIAS=  ,A4    ,A5 ,$
  FIELD=Trandate,ALIAS=  ,YYMD  ,A9 ,$
  FIELD=Type    ,ALIAS=  ,A5    ,A6 ,$
  FIELD=CountA  ,ALIAS=  ,I9    ,A4 ,$
  FIELD=CountB  ,ALIAS=  ,I9    ,A3 ,$
  FIELD=CountC  ,ALIAS=  ,I9    ,A2 ,$
  FIELD=Fill    ,ALIAS=  ,A1    ,A51,$

EX -LINES 9 EDAPUT FOCTEMP,TMP_DATA,CV,FILE
John 20110512 book1 105 27 18
John 20110512 book2 17  25 10
Amy  20110512 book1 108  7 78
Amy  20110512 book2 99  31 16
Bill 20110512 book1 125  2 41
Bill 20110512 book2 36  25 25
John 20110512 book3 41  22  2
Amy  20110512 book3 88  14  3

FILEDEF TMP_DATA DISK tmp_data.ftm

-RUN

TABLE   FILE TMP_DATA
 SUM
        Type
        CountA
        CountB
        CountC
 BY     Name
 BY     Trandate
 ACROSS Type      NOPRINT

END

quote:

Only 1 row and it appears to be counting the number of rows rather that adding the counts
END

Then you'll need to break the results by NAME and TRANDATE, and remove the CNT. prefix:

TABLE FILE LIBRARY
SUM
COUNTA
COUNTB
COUNTC
BY NAME
BY TRANDATE
ACROSS TYPE
END

Thx, yes I had removed the cnt. and added the by fields and it worked.

Trying to set a border using:
TYPE=DATA,
ACROSSCOLUMN=N3,
BORDER-LEFT=MEDIUM,
BORDER-LEFT-COLOR=RGB(166 185 219),

The border does not show up for each set of column data. only shows up on Book3 column.

BOOK1 BOOK2 BOOK3
Name Tran Date CNTA CNTB CNTC CNTA CNTB CNTC CNTA CNTB CNTC
John 20110512 105 27 18 2 17 25 10
John 20110512 105 27 18 2 17 25 10

What I am trying to accoumplish is to have a box around each set of column data so if anyone has other ideas I could use the help.

Something like this?:

DEFINE FILE CAR
DUMMY/A1 = ' ';
END
TABLE FILE CAR
SUM SEATS AND DEALER_COST
BY DUMMY NOPRINT
BY CAR
ACROSS COUNTRY
ON DUMMY SUBFOOT
" "
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOPAGE
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=DATA,  COLUMN=CAR, BORDER-LEFT=LIGHT, BORDER-RIGHT=LIGHT, $
TYPE=TITLE, COLUMN=CAR, BORDER=LIGHT, $
$
TYPE=DATA,  ACROSSCOLUMN=SEATS, BORDER-LEFT=LIGHT, $
TYPE=TITLE, ACROSSCOLUMN=SEATS, BORDER-LEFT=LIGHT, BORDER-BOTTOM=LIGHT, $
TYPE=DATA,  ACROSSCOLUMN=DEALER_COST, BORDER-RIGHT=LIGHT, $
TYPE=TITLE, ACROSSCOLUMN=DEALER_COST, BORDER-RIGHT=LIGHT, BORDER-BOTTOM=LIGHT, $
$
TYPE=TITLE, STYLE=NORMAL, BORDER-BOTTOM=LIGHT, $
TYPE=ACROSSVALUE, BORDER=LIGHT, $
TYPE=ACROSSTITLE, BORDER=LIGHT, $
$
TYPE=SUBFOOT, BY=1, BORDER-TOP=LIGHT, $
ENDSTYLE
END

Thanks, I tried this and it works but when I go back to report painter it changes everything and the formating is gone again.

The perils of leaving the GUI.

You've gone beyond the capabilities of report painter, and when you go back in, it takes what it knows and tosses the rest.

Whew! I finally got it to work all within Report Painter ...

TABLE FILE CAR
SUM 
     CAR.BODY.SEATS
     CAR.BODY.DEALER_COST
BY  DUMMY NOPRINT 
BY  CAR.COMP.CAR
ACROSS CAR.ORIGIN.COUNTRY
     
ON DUMMY SUBFOOT
" "
ON TABLE SET PAGE NOPAGE
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE=DATA,
     COLUMN=N2,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=DATA,
     ACROSSCOLUMN=N1,
     BORDER-TOP=OFF,
     BORDER-LEFT=LIGHT,
$
TYPE=DATA,
     ACROSSCOLUMN=N2,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=OFF,
     BORDER-RIGHT=LIGHT,
$
TYPE=DATA,
     ACROSSCOLUMN=N5,
     BORDER-LEFT=LIGHT,
$
TYPE=DATA,
     ACROSSCOLUMN=N6,
     BORDER-RIGHT=LIGHT,
$
TYPE=TITLE,
     BORDER-BOTTOM=LIGHT,
     STYLE=NORMAL,
$
TYPE=TITLE,
     COLUMN=N2,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=TITLE,
     ACROSSCOLUMN=N1,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     STYLE=NORMAL,
$
TYPE=TITLE,
     ACROSSCOLUMN=N2,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-RIGHT=LIGHT,
     STYLE=NORMAL,
$
TYPE=TITLE,
     ACROSSCOLUMN=N5,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
$
TYPE=TITLE,
     ACROSSCOLUMN=N6,
     BORDER-BOTTOM=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=SUBFOOT,
     BY=1,
     BORDER-TOP=LIGHT,
$
TYPE=ACROSSVALUE,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=ACROSSTITLE,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
ENDSTYLE
END

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

Whew is right, I am trying the same thingin my report and it is grueling. I have 2 hidden columns with a total of 8 columns so I have to include those as well to get all the formatting.
The hidden columns presents problems, but this has helped me alot.

Thx sooooo much,

You're welcome! Glad it helped.

well now it seems they want a report that summarizes by month with qtr totals. I added the date field and formated it as YY and again for M as sum fields but the report lists each record.
Could you help again?

BOOK1 BOOK2 BOOK3
Name Year Month CNTA CNTB CNTC CNTA CNTB CNTC CNTA CNTB CNTC
John 2011 January 105 17 18 2 0 25 10 11 6
February 100 27 11 12 17 0 11 21 6
March 117 11 28 14 27 0 0 31 6
QTR Totals 322 55 59 28 38 25 22 63 18
Amy 2011 January 100 16 18 12 0 25 10 21 6
February 102 26 11 2 17 0 19 21 6
March 147 21 28 24 28 0 70 431 6
QTR Totals 349 63 67 38 45 25 99 473 18
I am pretty new to WF and just did mostly drag and drop type reports so you have been a trememdous help.

If this is still in your code:

BY TRANDATE

You will have to remove it as that may be the reason why you're still getting "detail" records even though you're aggregating by year and month.