 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [Solved] Two column output of data using OVER
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [Solved] Two column output of data using OVER Read-Only TopicGo | Search | Notify | Tools |
| [Solved] Two column output of data using OVER | Login/Join |
| Silver Member |
I have a requirement to generate a report which five columns of data stacked which needs to display in two cloumns when the output format is PDF. Here is how I have things set up currently: TABLE FILE PCDISB SUM INVOICE_NBR AS 'Invoice Nbr: ' OVER INVOICE_AMT AS 'Invoice Amt: ' IN 3 OVER PRSN_ID AS 'Person Id: ' IN 3 OVER NAME AS 'Cardholder: ' IN 3 OVER VENDOR_NM1 AS 'Vendor: ' IN 3 BY WARRANT_NBR PAGE-BREAK NOPRINT BY POSTING_DT NOPRINT BY INVOICE_NBR NOSPLIT NOPRINT WHERE POSTING_STATUS EQ 'H'; This looks good for one column: Invoice Nbr: 9999999 Invoice Amt: -24.00 Person Id: 99999999 Cardholder: XXXXX XXXXXXXXX Vendor: XXXX Invoice Nbr: 9999999 Invoice Amt: 23.00 Person Id: 99999999 Cardholder: XX XXXXXX Vendor: XXXXX XXX XXXXX Invoice Nbr: 9999999 Invoice Amt: 78.65 Person Id: 99999999 Cardholder: XXXXX XXXXXXXX Vendor: XXXXXXX XXXXX Invoice Nbr: 9999999 Invoice Amt: 253.80 Person Id: 99999999 Cardholder: XXXXX XXXXXXXX Vendor: XXXXXXX XXX ... however, I need for the display to be like: Invoice Nbr: 9999999 Invoice Nbr: 9999999 Invoice Amt: -24.00 Invoice Amt: 23.00 Person Id: 99999999 Person Id: 99999999 Cardholder: XXXXXX XXXXXXXXX Cardholder: XX XXXXXX Vendor: XXXX Vendor: XXXXX XXX XXXXX Invoice Nbr: 9999999 Invoice Nbr: 9999999 Invoice Amt: 78.65 Invoice Amt: 253.80 Person Id: 99999999 Person Id: 99999999 Cardholder: XXXXX XXXXXXXX Cardholder: XXXXX XXXXXXXXX Vendor: XXXXXX XXXXX Vendor: XXXXXXX XXX ... Any ideas on how to accomplish this?This message has been edited. Last edited by: BGronli, 7.7, z/OS & Win7, excel & pdf 8.2 testing | ||
|
| Guru |
Compute row and col numbers yourself based on the record row and then: ACROSS COLNBR NOPRINT BY ROWNBR NOPRINT and then page break after a certain number of rows. You may need to mess with the styling a bit to get it just right. WebFOCUS 7.7.03/8.0.08 Dev Studio 7.7.03/8.0.08 App Studio 8.0.08 Windows 7 ALL Outputs | |||
|
| Platinum Member |
Waxing nostalgic -- that was my first project in FOCUS (mainframe CMS) in 1982 - make two or three columns for bank addresses, telephone numbers, etc..... J's suggestion will get you there...WebFOCUS is new but many many things are the same. Vivian Vivian Perlmutter Aviter, Inc.  WebFOCUS Keysheet Rel. 8.0.2 (Almost) 1001 Ways to Work with Dates thru Rel. 8.0.2 Focus since 1982 WebFOCUS since the beginning Vivian@aviter.com | |||
|
| Silver Member |
Using J's suggestion I have gotten the data to display in two columns, but there are three things wrong with it. [LIST] Brad DEFINE FILE PCDISB
FIRST/A14V = TRIMV('T', PRSN_FRST_NM, 14, ' ', 1, FIRST);
MIDDLE/A14V = TRIMV('T', PRSN_MID_NM, 14, ' ', 1, MIDDLE);
LAST/A22V = TRIMV('T', PRSN_LAST_NM, 22, ' ', 1, LAST);
NAME1/A50 = FIRST||(' '|MIDDLE)||(' '|LAST);
NAME/A50V = TRIMV('T', NAME1, 50, ' ', 1, NAME);
END
-***
TABLE FILE PCDISB
SUM
INVOICE_AMT
PRSN_ID
NAME
VENDOR_NM1
WARRANT_NBR POSTING_DT
COMPUTE CNTC/I9 = IF CNTC LT 2 THEN CNTC + 1 ELSE 1;
COMPUTE CNTR/I9 = IF CNTR LT 999999 AND CNTC EQ 2 THEN CNTR ELSE
IF CNTR LT 999999 THEN CNTR + 1 ELSE 1;
BY WARRANT_NBR NOPRINT
BY POSTING_DT NOPRINT
BY INVOICE_NBR
WHERE POSTING_STATUS EQ 'H';
&WHROLO
&WHRINV
&WHRBDT
&WHREDT
ON TABLE SET ACROSSPRT COMPRESS
ON TABLE HOLD
END
-RUN
-**
SET PRINTPLUS = OFF
TABLE FILE HOLD
PRINT
INVOICE_NBR AS 'Invoice Nbr: ' OVER
INVOICE_AMT AS ' Invoice Amt: ' OVER
PRSN_ID AS ' Person Id: ' OVER
NAME AS ' Cardholder: ' OVER
VENDOR_NM1 AS ' Vendor: '
BY WARRANT_NBR PAGE-BREAK NOPRINT
BY POSTING_DT NOPRINT
BY INVOICE_NBR NOSPLIT NOPRINT
ACROSS CNTC NOPRINT
BY CNTR NOPRINT
ON WARRANT_NBR SUBHEAD
"Warrant Nbr: <WARRANT_NBR <+1 Date: <POSTING_DT <+1 Total: <ST.INVOICE_AMT"
-CONT
ON TABLE PCHOLD FORMAT &fmt
ON TABLE SET PAGE NOPAGE
-IF &fmt EQ 'PDF' THEN GOTO ONPDF;
ON TABLE SET LINES 999999
ON TABLE SET PAPER 999999
ON TABLE SET BYDISPLAY ON
-GOTO ONFIN;
-ONPDF
-*ON TABLE SET HTMLCSS ON
-ONFIN
ON TABLE SET STYLE *
TYPE=REPORT, FOCEXEC=NONE, FONT=ARIAL, SIZE=9, COLOR=BLACK,
BACKCOLOR=NONE, STYLE=NORMAL, GRID=OFF, TITLETEXT='&TABTITLE', $
TYPE=TITLE, STYLE=BOLD, $
TYPE=REPORT, PAGESIZE=LETTER, ORIENTATION=LANDSCAPE, SQUEEZE=ON,
BOTTOMMARGIN=0.0, TOPMARGIN=0.5, LEFTMARGIN=0.5, RIGHTGAP=.1, $
TYPE=DATA, JUSTIFY=LEFT, $
TYPE=SUBHEAD, STYLE=BOLD, COLOR=BLACK, SIZE=10, BOTTOMGAP=0.05,
JUSTIFY=CENTER, $
ENDSTYLE
END
-RUN Here is how the report looks. Invoice Nbr: 0156100 . Invoice Amt: -24.00 . Person Id: 00033142 . Cardholder: EVELYN RODRIGUEZ . Vendor: FDLE . Invoice Nbr: . 0156101 Invoice Amt: . 23.00 Person Id: . 00033055 Cardholder: . AL WILLIS Vendor: . DELTA AIR LINES Invoice Nbr: 0156106 . Invoice Amt: 78.65 . Person Id: 00040679 . Cardholder: CHRIS MCMURRAY . Vendor: TRAVEL LODGE . Invoice Nbr: . 0156107 Invoice Amt: . 253.80 Person Id: . 00047184 Cardholder: . KAREN DUEHRING Vendor: . HAMPTON INNThis message has been edited. Last edited by: BGronli, 7.7, z/OS & Win7, excel & pdf 8.2 testing | |||
|
| Guru |
You are going to have to mess with the styling to justify things properly. I know a downside to using over is that the first column seems to align differently than the rest of the columns (there is extra space). These kinds of reports you will have to mess with until you get it just right. WebFOCUS 7.7.03/8.0.08 Dev Studio 7.7.03/8.0.08 App Studio 8.0.08 Windows 7 ALL Outputs | |||
|
| Silver Member |
Thanks for the replies J and Vivian. I have made some progress today. I now have data which looks like:
Invoice Nbr: 0156100 .
Invoice Amt: -24.00 .
Person Id: 00033142 .
Cardholder: EVELYN RODRIGUEZ .
Vendor: FDLE .
. Invoice Nbr: 0156101
. Invoice Amt: 23.00
. Person Id: 00033055
. Cardholder: AL WILLIS
. Vendor: DELTA AIR LINES
Invoice Nbr: 0156106 .
Invoice Amt: 78.65 .
Person Id: 00040679 .
Cardholder: CHRIS MCMURRAY .
Vendor: TRAVEL LODGE .
. Invoice Nbr: 0156107
. Invoice Amt: 253.80
. Person Id: 00047184
. Cardholder: KAREN DUEHRING
. Vendor: HAMPTON INN That just leaves me compressing it to move the right column up in line with the left. So far, ON TABLE SET ACROSSPRT COMPRESShas not worked. I've also been looking at the "Working with mainling labels and multi-pane pages" from Creating Reports with WebFOCUS Language, but will need to play with that one more. Although I did not include the pieces in the code I posted, this report does have a heading, subhead, and footer. 7.7, z/OS & Win7, excel & pdf 8.2 testing | |||
|
| Platinum Member |
This is a simplistic example of what you are trying to do. DEFINE FILE CAR CNTR/I2 = IF CNTR EQ 2 THEN 1 ELSE CNTR + 1; ROWNUMBER/I2 = IF CNTR LT 2 THEN ROWNUMBER + 1 ELSE ROWNUMBER; END TABLE FILE CAR SUM COUNTRY OVER MODEL OVER DEALER_COST AS 'DCOST1' OVER RETAIL_COST AS 'RCOST1' OVER SALES AS 'SALES1' ACROSS CNTR BY ROWNUMBER END If you print out your row and column numbers you will see what is happening and be able to fix your problem. Hopefully, this will set you on the right path. Vivian Vivian Perlmutter Aviter, Inc. WebFOCUS Keysheet Rel. 8.0.2 (Almost) 1001 Ways to Work with Dates thru Rel. 8.0.2 Focus since 1982 WebFOCUS since the beginning Vivian@aviter.com | |||
|
| Virtuoso |
To avoid the staggered effect, change PRINT to SUM (as in Vivian's example). - Jack Gross WF through 8.1.05 | |||
|
| Silver Member |
Vivian and Jack, thanks again for the responses. I set Vivian's example up and it looks good except that the titles only appear next to the first column. I was able to get the titles in both columns with the following:
DEFINE FILE PCDISB
FIRST/A14V = TRIMV('T', PRSN_FRST_NM, 14, ' ', 1, FIRST);
MIDDLE/A14V = TRIMV('T', PRSN_MID_NM, 14, ' ', 1, MIDDLE);
LAST/A22V = TRIMV('T', PRSN_LAST_NM, 22, ' ', 1, LAST);
NAME1/A50 = FIRST||(' '|MIDDLE)||(' '|LAST);
NAME/A50V = TRIMV('T', NAME1, 50, ' ', 1, NAME);
VDR_NM/A31V = TRIMV('T', VENDOR_NM1, 31, ' ', 1, VDR_NM);
INV_AMT/D13.2 = INVOICE_AMT;
INV_AMTA/A16 = FTOA(INV_AMT, '(D13.2)', INV_AMTA);
LINE1/A20 = 'Invoice Nbr: '|INVOICE_NBR;
LINE2/A29 = 'Invoice Amt: '|INV_AMTA;
LINE3/A19 = 'Person Id: '|PRSN_ID;
LINE4/A62V = 'Cardholder: '|NAME;
LINE5/A42V = 'Vendor: '|VDR_NM;
END
-***
TABLE FILE PCDISB
SUM LINE1 LINE2 LINE3 LINE4 LINE5 INVOICE_NBR INVOICE_AMT PRSN_ID NAME
VENDOR_NM1
BY WARRANT_NBR
BY POSTING_DT
BY INVOICE_NBR NOPRINT
WHERE POSTING_STATUS EQ 'H';
&WHROLO
&WHRINV
&WHRBDT
&WHREDT
ON TABLE HOLD
END
-RUN
-***
DEFINE FILE HOLD
CNTC/I2 = IF CNTC EQ 2 THEN 1 ELSE CNTC + 1;
CNTR/I2 = IF CNTC LT 2 THEN CNTR + 1 ELSE CNTR;
END
-***
TABLE FILE HOLD
SUM
LINE1 AS ' ' OVER
LINE2 AS ' ' IN +2 OVER
LINE3 AS ' ' IN +2 OVER
LINE4 AS ' ' IN +2 OVER
LINE5 AS ' ' IN +2 OVER
BY WARRANT_NBR NOPRINT
-*BY POSTING_DT NOPRINT
BY INVOICE_NBR NOSPLIT NOPRINT
ACROSS CNTC
BY CNTR
HEADING CENTER
"&HEAD1"
"&HEAD2"
"&HEAD3"
" "
FOOTING BOTTOM
" "
"Report run on &DATEMDYY for information as of &CURDT <+0>Page<TABPAGENO"
ON WARRANT_NBR SUBHEAD
"Warrant Nbr: <WARRANT_NBR <+1 Date: <POSTING_DT <+1 Total: <ST.INVOICE_AMT"
" "
ON TABLE PCHOLD FORMAT &fmt
ON TABLE SET PAGE NOPAGE
ON WARRANT_NBR PAGE-BREAK I still get the staggered data. With the column and row counters showing and as I expected they would be.
CNTC
1 2
CNTR
1 Invoice Nbr: 0156100 .
Invoice Amt: -24.00 .
Person Id: 00033142 .
Cardholder: EVELYN RODRIGUEZ .
Vendor: FDLE .
1 . Invoice Nbr: 0156101
. Invoice Amt: 23.00
. Person Id: 00033055
. Cardholder: AL WILLIS
. Vendor: DELTA AIR LINES
2 Invoice Nbr: 0156106 .
Invoice Amt: 78.65 .
Person Id: 00040679 .
Cardholder: CHRIS MCMURRAY .
Vendor: TRAVEL LODGE .
2 . Invoice Nbr: 0156107
. Invoice Amt: 253.80
. Person Id: 00047184
. Cardholder: KAREN DUEHRING
. Vendor: HAMPTON INN Changing from PRINT to SUM had no effect. Is there anything else you can see that I have done that is not allowing the data to display both columns on a single row?This message has been edited. Last edited by: BGronli, 7.7, z/OS & Win7, excel & pdf 8.2 testing | |||
|
| Platinum Member |
Take off your by fields (BY WARRANT_NBR NOPRINT -*BY POSTING_DT NOPRINT BY INVOICE_NBR NOSPLIT NOPRINT) in the final TABLE FILE and see if that helps. Each line and row should be unique. VivianThis message has been edited. Last edited by: Vivian, Vivian Perlmutter Aviter, Inc. WebFOCUS Keysheet Rel. 8.0.2 (Almost) 1001 Ways to Work with Dates thru Rel. 8.0.2 Focus since 1982 WebFOCUS since the beginning Vivian@aviter.com | |||
|
| Virtuoso |
You need to pre-compute the Page, Column and Row number for each instance, and then report... SUM ... BY Page PAGE-BREAK NOPRINT BY Row NOPRINT ACROSS Column NOPRINT Note -- The arithmetic is simpler if all the indexes (overall order, Page, Column, Row) are zero-based. | |||
|
| Silver Member |
Thank you J and Vivian for the replies. I was not able to use either of your last responses, but I was able to work it out (with Vivians, I needed the by fields for the subhead and to prevent splitting. J's I just was not able to make work). Here is the code I have that works for me.
DEFINE FILE PCDISB
FIRST/A14V = TRIMV('T', PRSN_FRST_NM, 14, ' ', 1, FIRST);
MIDDLE/A14V = TRIMV('T', PRSN_MID_NM, 14, ' ', 1, MIDDLE);
LAST/A22V = TRIMV('T', PRSN_LAST_NM, 22, ' ', 1, LAST);
NAME1/A50 = FIRST||(' '|MIDDLE)||(' '|LAST);
NAME/A50V = TRIMV('T', NAME1, 50, ' ', 1, NAME);
VDR_NM/A31V = TRIMV('T', VENDOR_NM1, 31, ' ', 1, VDR_NM);
INV_AMT/D13.2 = INVOICE_AMT;
INV_AMTA/A16 = FTOA(INV_AMT, '(D13.2)', INV_AMTA);
LINE1/A20 = 'Invoice Nbr: '|INVOICE_NBR;
LINE2/A29 = 'Invoice Amt: '|INV_AMTA;
LINE3/A19 = 'Person Id: '|PRSN_ID;
LINE4/A62V = 'Cardholder: '|NAME;
LINE5/A42V = 'Vendor: '|VDR_NM;
END
-***
TABLE FILE PCDISB
SUM LINE1 LINE2 LINE3 LINE4 LINE5 INVOICE_NBR INVOICE_AMT
VENDOR_NM1 WARRANT_NBR POSTING_DT
BY WARRANT_NBR NOPRINT
BY POSTING_DT NOPRINT
BY INVOICE_NBR NOPRINT
WHERE POSTING_STATUS EQ 'H';
&WHROLO
&WHRINV
&WHRBDT
&WHREDT
ON TABLE HOLD
END
-RUN
-***
DEFINE FILE HOLD
CNTC/I2 = IF CNTC EQ 2 THEN 1 ELSE CNTC + 1;
CNTR/I2 = IF CNTC LT 2 THEN CNTR + 1 ELSE CNTR;
CNTC/I2 = IF WARRANT_NBR NE LAST WARRANT_NBR THEN 1 ELSE CNTC;
CNTR/I2 = IF WARRANT_NBR NE LAST WARRANT_NBR THEN CNTR + 1 ELSE CNTR;
INVOICE/A7 = IF CNTC EQ 2 THEN LAST INVOICE_NBR ELSE INVOICE_NBR;
END
-***
TABLE FILE HOLD
SUM INVOICE_AMT NOPRINT
BY WARRANT_NBR NOPRINT
SUM
LINE1 AS ' ' OVER
LINE2 AS ' ' IN +2 OVER
LINE3 AS ' ' IN +2 OVER
LINE4 AS ' ' IN +2 OVER
LINE5 AS ' ' IN +2 OVER
INVOICE_AMT AS '' NOPRINT
BY WARRANT_NBR NOPRINT
BY POSTING_DT NOPRINT
BY INVOICE NOSPLIT NOPRINT
ACROSS CNTC NOPRINT
BY CNTR NOPRINT
HEADING CENTER
"&HEAD1"
"&HEAD2"
"&HEAD3"
"</1"
FOOTING BOTTOM
" "
"Report run on &DATEMDYY for information as of &CURDT <+0>Page<TABPAGENO_AMT"
ON WARRANT_NBR SUBHEAD
"Warrant Nbr: <WARRANT_NBR <+1 Date: <POSTING_DT <+1 Total: <SUM.INVOICE
" "
-CONT
ON TABLE PCHOLD FORMAT &fmt
ON TABLE SET PAGE NOPAGE
-IF &fmt EQ 'PDF' THEN GOTO ONPDF;
ON TABLE SET LINES 999999
ON TABLE SET PAPER 999999
ON TABLE SET BYDISPLAY ON
-GOTO ONFIN;
-ONPDF
ON WARRANT_NBR PAGE-BREAK
-ONFIN
ON TABLE SET STYLE *
-IF &fmt EQ 'EXL07' THEN GOTO RPTEXL;
TYPE=REPORT, FOCEXEC=NONE, FONT=ARIAL, SIZE=9, COLOR=BLACK,
BACKCOLOR=NONE, STYLE=NORMAL, GRID=OFF, PAGESIZE=LETTER,
ORIENTATION=PORTRAIT, SQUEEZE=ON, BOTTOMMARGIN=0.5,
TOPMARGIN=0.5, LEFTMARGIN=0.5, $
-GOTO RPTCONT;
-RPTEXL
TYPE=REPORT, FOCEXEC=NONE, FONT=ARIAL, SIZE=9, COLOR=BLACK,
BACKCOLOR=NONE, STYLE=NORMAL, GRID=OFF, TITLETEXT='&TABTITLE', $
-RPTCONT
TYPE=TITLE, STYLE=BOLD, $
TYPE=HEADING, LINE=1, STYLE=BOLD, SIZE=12, $
TYPE=HEADING, LINE=2, STYLE=BOLD+ITALIC, SIZE=10, $
-IF &ABBR NE 'DIS' AND &ABBR NE 'DAA' THEN GOTO HEADING2;
TYPE=HEADING, LINE=3, STYLE=BOLD+ITALIC, SIZE=10, $
TYPE=HEADING, LINE=4, STYLE=BOLD+ITALIC, SIZE=10, $
TYPE=HEADING, LINE=5, STYLE=BOLD+ITALIC, SIZE=7, $
TYPE=HEADING, LINE=6, STYLE=BOLD+ITALIC, SIZE=7, $
-GOTO CONTDATA;
-HEADING2
TYPE=HEADING, LINE=3, STYLE=BOLD+ITALIC, SIZE=10, $
TYPE=HEADING, LINE=4, STYLE=BOLD+ITALIC, SIZE=7, $
TYPE=HEADING, LINE=5, STYLE=BOLD+ITALIC, SIZE=7, $
-CONTDATA
TYPE=DATA, TOPGAP=0.05, $
TYPE=SUBHEAD, LINE=1, STYLE=BOLD, COLOR=BLACK, SIZE=10,
JUSTIFY=CENTER, $
TYPE=FOOTING, STYLE=NORMAL, COLOR=BLACK, SIZE=9, $
TYPE=FOOTING, OBJECT=TEXT, ITEM=2, POSITION=9, $
-DONE_STYLE
ENDSTYLE
END
-RUN
7.7, z/OS & Win7, excel & pdf 8.2 testing | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [Solved] Two column output of data using OVER
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [Solved] Two column output of data using OVERCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |