I need help creating the following in report: I grouped all the items I need into 1 column, and now I need to break this column by "groups". This is my script and what it does:
TABLE FILE WORKORDER PRINT PROJECT AS 'PROJ#' WORKORDER AS 'WO' OVER UNIT AS 'UNIT' OVER AMOUNT AS 'QTY' BY PROJECT NOPRINT ON TABLE PCHOLD FORMAT EXL2K
This prints the following:
PROJ# 001 WO 111 UNIT 1 QTY 5 PROJ# 001 WO 222 UNIT 2 QTY 3 PROJ# 002 WO 521 UNIT 1 QTY 6 PROJ# 002 WO 521-6 UNIT 6 QTY 1
But what I need it to do is to group by project horizontaly, and then take it to the next line when the project number changes. Like this:
PROJ# 001 WO 111 WO 222 UNIT 1 UNIT 2 QTY 1 QTY 3 PROJ# 002 WO 521 WO 521-6 UNIT 1 UNIT 6 QTY 6 QTY 1 ETC...
Thanks for your help...This message has been edited. Last edited by: sergio.g1,
webFOCUS 765
October 30, 2013, 04:30 PM
Danny-SRL
What are the formats of WORKORDER, UNIT and AMOUNT?
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
October 30, 2013, 04:37 PM
sergio.g1
The WORKORDER is Alpha. UNIT and AMOUNT are numeric.
webFOCUS 765
October 30, 2013, 04:49 PM
Francis Mariani
Maybe someone can explain why OVER and ACROSS don't work so well together.
SET PAGE=NOLEAD
SET NODATA=''
TABLE FILE CAR
SUM
COMPUTE TXT1/A10 = 'WEIGHT'; AS ''
WEIGHT AS ''
COMPUTE TXT2/A10 = 'HEIGHT'; AS ''
HEIGHT AS '' OVER
COMPUTE TXT2/A10 = 'LENGTH'; AS ''
LENGTH AS ''
COMPUTE TXT2/A10 = 'WIDTH'; AS ''
WIDTH AS ''
BY COUNTRY AS ''
ACROSS BODYTYPE AS ''
END
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
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
October 31, 2013, 10:47 AM
DavSmith
quote:
Originally posted by Francis Mariani: Maybe someone can explain why OVER and ACROSS don't work so well together.
SET PAGE=NOLEAD
SET NODATA=''
TABLE FILE CAR
SUM
COMPUTE TXT1/A10 = 'WEIGHT'; AS ''
WEIGHT AS ''
COMPUTE TXT2/A10 = 'HEIGHT'; AS ''
HEIGHT AS '' OVER
COMPUTE TXT2/A10 = 'LENGTH'; AS ''
LENGTH AS ''
COMPUTE TXT2/A10 = 'WIDTH'; AS ''
WIDTH AS ''
BY COUNTRY AS ''
ACROSS BODYTYPE AS ''
END
The use of AS '' is causing the SUMmed matrix data values to shift left, as we want in this case, but the ACROSS title value, Bodytype, is not shifting left with their data. I could not come up with a quick work-around.
In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
October 31, 2013, 10:51 AM
Francis Mariani
This seems to generate what you're looking for:
TABLE FILE CAR
SUM
WEIGHT
HEIGHT
OVER
LENGTH
WIDTH
BY COUNTRY
ACROSS BODYTYPE AS ''
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON,
FONT='ARIAL', SIZE=9, BORDER=1, BORDER-COLOR=SILVER, $
TYPE=TITLE, STYLE=BOLD, $
TYPE=ACROSSVALUE, STYLE=BOLD, JUSTIFY=CENTER, COLOR=BLUE, $
TYPE=DATA, COLUMN=COUNTRY, STYLE=BOLD, COLOR=BLUE, $
ENDSTYLE
END
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
October 31, 2013, 05:16 PM
sergio.g1
Thanks everyone for your help. A combination between:
quote:
SET PAGE=NOLEAD SET NODATA=''
TABLE FILE CAR SUM COMPUTE TXT1/A10 = 'WEIGHT'; AS '' WEIGHT AS '' COMPUTE TXT2/A10 = 'HEIGHT'; AS '' HEIGHT AS '' OVER COMPUTE TXT2/A10 = 'LENGTH'; AS '' LENGTH AS '' COMPUTE TXT2/A10 = 'WIDTH'; AS '' WIDTH AS '' BY COUNTRY AS '' ACROSS BODYTYPE AS '' END
and
quote:
TABLE FILE CAR SUM WEIGHT HEIGHT
OVER
LENGTH WIDTH
BY COUNTRY ACROSS BODYTYPE AS ''
ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * TYPE=REPORT, SQUEEZE=ON, FONT='ARIAL', SIZE=9, BORDER=1, BORDER-COLOR=SILVER, $