[CLOSED] Using JOINS in multiple reports to create a single report
Hello,
I created a couple of detailed reports and from there I create 2 more summary reports that will return a percentage of the detailed report. What I did was create a JOIN on the 2 summary reports so I can combine these data with another report. I am running into an issue where the columns that I want to show (realTimePct for LOADING and realTimePct for UNLOADING) are not showing the value of both columns...When I created the report, both columns are showing the same value instead of realTimePct for LOADING and realTimePct for UNLOADING.
I tried to search for any posts that may help me with this issue, but I haven't any luck.
Here's my code: -* File loading_unloading.shipper.fex TABLE FILE DETENTIONBYSHIPPERLOAD PRINT proNumber billOfLading shipperName puName puCity puState delName delCity delState schedDate/HMDYYI actualDate/HYYMDI releasedDate/HYYMDI realTime HEADING "" FOOTING "" WHERE ( realTime GT 2 ) AND ( reportFlag EQ 0 ); ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * UNITS=IN, LEFTMARGIN=0.500000, RIGHTMARGIN=0.500000, TOPMARGIN=0.500000, BOTTOMMARGIN=0.500000, SQUEEZE=ON, ORIENTATION=PORTRAIT, $ TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, TOPGAP=0.013889, BOTTOMGAP=0.027778, $ TYPE=TITLE, STYLE=BOLD, $ TYPE=TABHEADING, SIZE=12, STYLE=BOLD, $ TYPE=TABFOOTING, SIZE=12, STYLE=BOLD, $ TYPE=HEADING, SIZE=12, STYLE=BOLD, $ TYPE=FOOTING, SIZE=12, STYLE=BOLD, $ TYPE=SUBHEAD, SIZE=10, STYLE=BOLD, $ TYPE=SUBFOOT, SIZE=10, STYLE=BOLD, $ TYPE=SUBTOTAL, BACKCOLOR=RGB(210 210 210), $ TYPE=ACROSSVALUE, SIZE=9, $ TYPE=ACROSSTITLE, STYLE=BOLD, $ TYPE=GRANDTOTAL, BACKCOLOR=RGB(210 210 210), STYLE=BOLD, $ TYPE=REPORT, COLUMN=N7, WRAP=4.250000, $ TYPE=REPORT, COLUMN=N4, WRAP=4.555556, $ TYPE=REPORT, COLUMN=N3, WRAP=4.555556, $ ENDSTYLE END TABLE FILE DETENTIONBYSHIPPERLOAD PRINT proNumber billOfLading shipperName puName puCity puState delName delCity delState schedDate/HMDYYI actualDate/HYYMDI releasedDate/HYYMDI realTime HEADING "" FOOTING "" WHERE ( realTime GT 2 ) AND ( reportFlag EQ 0 ); ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * UNITS=IN, LEFTMARGIN=0.500000, RIGHTMARGIN=0.500000, TOPMARGIN=0.500000, BOTTOMMARGIN=0.500000, SQUEEZE=ON, ORIENTATION=PORTRAIT, $ TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, TOPGAP=0.013889, BOTTOMGAP=0.027778, $ TYPE=TITLE, STYLE=BOLD, $ TYPE=TABHEADING, SIZE=12, STYLE=BOLD, $ TYPE=TABFOOTING, SIZE=12, STYLE=BOLD, $ TYPE=HEADING, SIZE=12, STYLE=BOLD, $ TYPE=FOOTING, SIZE=12, STYLE=BOLD, $ TYPE=SUBHEAD, SIZE=10, STYLE=BOLD, $ TYPE=SUBFOOT, SIZE=10, STYLE=BOLD, $ TYPE=SUBTOTAL, BACKCOLOR=RGB(210 210 210), $ TYPE=ACROSSVALUE, SIZE=9, $ TYPE=ACROSSTITLE, STYLE=BOLD, $ TYPE=GRANDTOTAL, BACKCOLOR=RGB(210 210 210), STYLE=BOLD, $ TYPE=REPORT, COLUMN=N7, WRAP=4.250000, $ TYPE=REPORT, COLUMN=N4, WRAP=4.555556, $ TYPE=REPORT, COLUMN=N3, WRAP=4.555556, $ ENDSTYLE END TABLE FILE DETENTIONBYSHIPPERLOAD PRINT * ON TABLE HOLD AS DETENTIONBYSHIPPERLOADPCT END TABLE FILE DETENTIONBYSHIPPERLOADPCT PRINT realTimePct BY rank NOPRINT BY hoursBucket HEADING "" FOOTING "" WHERE ( shipperName EQ 'KRAFT' ) AND ( reportFlag EQ 1 ); ON TABLE SET PAGE-NUM OFF ON TABLE COLUMN-TOTAL AS 'TOTAL' realTimePct ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * UNITS=IN, LEFTMARGIN=0.500000, RIGHTMARGIN=0.500000, TOPMARGIN=0.500000, BOTTOMMARGIN=0.500000, SQUEEZE=ON, ORIENTATION=PORTRAIT, $ TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, TOPGAP=0.013889, BOTTOMGAP=0.027778, $ TYPE=TITLE, STYLE=BOLD, $ TYPE=TABHEADING, SIZE=12, STYLE=BOLD, $ TYPE=TABFOOTING, SIZE=12, STYLE=BOLD, $ TYPE=HEADING, SIZE=12, STYLE=BOLD, $ TYPE=FOOTING, SIZE=12, STYLE=BOLD, $ TYPE=SUBHEAD, SIZE=10, STYLE=BOLD, $ TYPE=SUBFOOT, SIZE=10, STYLE=BOLD, $ TYPE=SUBTOTAL, BACKCOLOR=RGB(210 210 210), $ TYPE=ACROSSVALUE, SIZE=9, $ TYPE=ACROSSTITLE, STYLE=BOLD, $ ENDSTYLE END TABLE FILE DETENTIONBYSHIPPERUNLOAD PRINT * ON TABLE HOLD AS DETENTIONBYSHIPPERUNLOADPCT END TABLE FILE DETENTIONBYSHIPPERUNLOADPCT PRINT realTimePct/F7.2 BY rank NOPRINT BY hoursBucket HEADING "" FOOTING "" WHERE ( shipperName EQ 'KRAFT' ) AND ( reportFlag EQ 1 ); ON TABLE SET PAGE-NUM OFF ON TABLE COLUMN-TOTAL AS 'TOTAL' realTimePct ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * UNITS=IN, LEFTMARGIN=0.500000, RIGHTMARGIN=0.500000, TOPMARGIN=0.500000, BOTTOMMARGIN=0.500000, SQUEEZE=ON, ORIENTATION=PORTRAIT, $ TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, TOPGAP=0.013889, BOTTOMGAP=0.027778, $ TYPE=TITLE, STYLE=BOLD, $ TYPE=TABHEADING, SIZE=12, STYLE=BOLD, $ TYPE=TABFOOTING, SIZE=12, STYLE=BOLD, $ TYPE=HEADING, SIZE=12, STYLE=BOLD, $ TYPE=FOOTING, SIZE=12, STYLE=BOLD, $ TYPE=SUBHEAD, SIZE=10, STYLE=BOLD, $ TYPE=SUBFOOT, SIZE=10, STYLE=BOLD, $ TYPE=SUBTOTAL, BACKCOLOR=RGB(210 210 210), $ TYPE=ACROSSVALUE, SIZE=9, $ TYPE=ACROSSTITLE, STYLE=BOLD, $ ENDSTYLE END JOIN DETENTIONBYSHIPPERLOADPCT.DETENTIO.shipperName IN DETENTIONBYSHIPPERLOADPCT TO UNIQUE DETENTIONBYSHIPPERUNLOADPCT.DETENTIO.shipperName IN DETENTIONBYSHIPPERUNLOADPCT AS J1 END SET JOINOPT=NEW
TABLE FILE DETENTIONBYSHIPPERLOADPCT PRINT hoursBucket realTimePct AS 'LOADING' realTimePct AS 'UNLOADING' BY rank HEADING "" FOOTING "" WHERE ( shipperName EQ 'KRAFT' ) AND ( reportFlag EQ 1 ); ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * UNITS=IN, LEFTMARGIN=0.500000, RIGHTMARGIN=0.500000, TOPMARGIN=0.500000, BOTTOMMARGIN=0.500000, SQUEEZE=ON, ORIENTATION=PORTRAIT, $ TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, TOPGAP=0.013889, BOTTOMGAP=0.027778, $ TYPE=TITLE, STYLE=BOLD, $ TYPE=TABHEADING, SIZE=12, STYLE=BOLD, $ TYPE=TABFOOTING, SIZE=12, STYLE=BOLD, $ TYPE=HEADING, SIZE=12, STYLE=BOLD, $ TYPE=FOOTING, SIZE=12, STYLE=BOLD, $ TYPE=SUBHEAD, SIZE=10, STYLE=BOLD, $ TYPE=SUBFOOT, SIZE=10, STYLE=BOLD, $ TYPE=SUBTOTAL, BACKCOLOR=RGB(210 210 210), $ TYPE=ACROSSVALUE, SIZE=9, $ TYPE=ACROSSTITLE, STYLE=BOLD, $ TYPE=GRANDTOTAL, BACKCOLOR='NONE', STYLE=BOLD, $ ENDSTYLE END
I appreciate any help!
Thanks!This message has been edited. Last edited by: Kerry,
Winnie
Webfocus 7.7.3
May 02, 2011, 01:16 PM
Francis Mariani
TABLE FILE DETENTIONBYSHIPPERLOADPCT
PRINT
hoursBucket
realTimePct AS 'LOADING'
realTimePct AS 'UNLOADING'
BY rank
WebFOCUS assumes the first column it encounters with the name "realTimePct" for both columns.
Specify the table name as prefix:
TABLE FILE DETENTIONBYSHIPPERLOADPCT
PRINT
hoursBucket
DETENTIONBYSHIPPERLOADPCT.realTimePct AS 'LOADING'
DETENTIONBYSHIPPERUNLOADPCT.realTimePct AS 'UNLOADING'
BY rank
(I did not take a look at the rest of your code).
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 02, 2011, 04:29 PM
Winnie
Thanks, Francis.
I tried adding the table name as prefix and I got this message.
1 0 NUMBER OF RECORDS IN TABLE= 2875 LINES= 2875 0 1 0 NUMBER OF RECORDS IN TABLE= 4401 LINES= 4401 0 (FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED
Winnie
Webfocus 7.7.3
May 02, 2011, 04:50 PM
Francis Mariani
This is why I stated "I did not take a look at the rest of your code" - you may have other errors, unrelated to the original question.
If you're joining flat files, the data has to be in the correct order, or you get this error.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 02, 2011, 04:59 PM
Mighty Max
Try sorting the data in the hold files by the field you are joining to. Also do a search on the forums for this focus error.
TABLE FILE DETENTIONBYSHIPPERLOAD
PRINT *
BY shipperName NOPRINT
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS DETENTIONBYSHIPPERLOADPCT
END
TABLE FILE DETENTIONBYSHIPPERUNLOAD
PRINT *
BY shipperName NOPRINT
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS DETENTIONBYSHIPPERUNLOADPCT
END
