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] Need help with WF report on creating temp table
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Need help with WF report on creating temp table
 Login/Join
 
Gold member
posted
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,



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Reply With QuoteReport This Post
Guru
posted Hide Post
TABLE FILE LIBRARY
SUM
MAX.NAME
MAX.TRANDATE
CNT.COUNTA
CNT.COUNTB
CNT.COUNTC
ACROSS TYPE
END
 
Posts: 291 | Location: Greater Cincinnati  | Registered: May 11, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
THX will give it a try



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Reply With QuoteReport This Post
Gold member
posted Hide Post
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



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
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


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6278 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Thx, yes I had removed the cnt. and added the by fields and it worked.



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Reply With QuoteReport This Post
Gold member
posted Hide Post
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.



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks, I tried this and it works but when I go back to report painter it changes everything and the formating is gone again.



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6278 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Whew! I finally got it to work all within Report Painter ... Sweating

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,



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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,



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You're welcome! Glad it helped. Smiler



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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.



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Need help with WF report on creating temp table

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