Focal Point
[SOLVED] formatting acrossvalue to line up with column header

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4577004516

January 13, 2012, 04:45 PM
Joy_Jacques
[SOLVED] formatting acrossvalue to line up with column header
Hi all,

I have read through the forums and can't find what I'm looking for, so I'll post my question.

I have code like this:

TABLE FILE CAR
SUM DEALER_COST
BY MODEL
ACROSS COUNTRY
END

I have added all sorts of options to the styling but one thing I can't do is move the values down one line. They want the Country values to show up on the same line as MODEL formatted as Excel.

When I do this in any format, HTML, EXL2K, I can't get the values down one line. I tried moving them down manually: (this is my code, not the car example)

ON SEGMENT SUBHEAD
"Production Year"
" "
"Segment<+0>2007<+0>2008<+0>2009<+0>2010<+0>2011<+0>2012<+0>2013<+0>2014<+0>2015<+0>2016<+0>2017"
ON TABLE SET PAGE NOLEAD
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON, GRID=ON, FONT='ARIAL',$
TYPE=SUBHEAD, BORDER-TOP=MEDIUM, BORDER-LEFT=MEDIUM, BORDER-RIGHT=MEDIUM, STYLE=BOLD,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=1, POSITION=0.00, JUSTIFY=RIGHT,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=2, POSITION=P2, JUSTIFY=RIGHT,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=3, POSITION=P3, JUSTIFY=RIGHT,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=4, POSITION=P4, JUSTIFY=RIGHT,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=5, POSITION=P5, JUSTIFY=RIGHT,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=6, POSITION=P6, JUSTIFY=RIGHT,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=7, POSITION=P7, JUSTIFY=RIGHT,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=8, POSITION=P8, JUSTIFY=RIGHT,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=9, POSITION=P9, JUSTIFY=RIGHT,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=10, POSITION=P10, JUSTIFY=RIGHT,$

and I could get them to move down, but now they are all returned in 1 cell.

Any way to make this work?

Thanks!
Joy

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


7.7.2, Windows, html
January 13, 2012, 06:58 PM
Mighty Max
Whenever you ACROSS you always get that extra line. It's kind of annoyance and most of the time customer understand. Looking at you code it looks like you are missing TYPE=SUBHEAD, HEADALIGN=BODY, $. Also you should add a COLSPAN=1, to your subhead items. POSITION is generally used in PDF output format not really used in Excel output format. Having it in the stylesheet doesn't hurt anything. Take a look at this.
  
DEFINE FILE CAR
SEGMENT/A1=''
END

TABLE FILE CAR
SUM
   DEALER_COST

BY SEGMENT NOPRINT
BY MODEL AS ''
ACROSS COUNTRY AS '' NOPRINT
ON SEGMENT SUBHEAD
"Production Year"
" "
"Segment<+0>2007<+0>2008<+0>2009<+0>2010<+0>2011<+0>2012<+0>2013<+0>2014<+0>2015<+0>2016<+0>2017"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=REPORT, SQUEEZE=ON, GRID=ON, FONT='ARIAL',$
TYPE=SUBHEAD, HEADALIGN=BODY,$
TYPE=SUBHEAD, BORDER-TOP=MEDIUM, BORDER-LEFT=MEDIUM, BORDER-RIGHT=MEDIUM, STYLE=BOLD,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=1,  POSITION=0.00, JUSTIFY=RIGHT, COLSPAN=1,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=2,  POSITION=P2,   JUSTIFY=RIGHT, COLSPAN=1,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=3,  POSITION=P3,   JUSTIFY=RIGHT, COLSPAN=1,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=4,  POSITION=P4,   JUSTIFY=RIGHT, COLSPAN=1,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=5,  POSITION=P5,   JUSTIFY=RIGHT, COLSPAN=1,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=6,  POSITION=P6,   JUSTIFY=RIGHT, COLSPAN=1,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=7,  POSITION=P7,   JUSTIFY=RIGHT, COLSPAN=1,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=8,  POSITION=P8,   JUSTIFY=RIGHT, COLSPAN=1,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=9,  POSITION=P9,   JUSTIFY=RIGHT, COLSPAN=1,$
TYPE=SUBHEAD, LINE=3, OBJECT=TEXT, ITEM=10, POSITION=P10,  JUSTIFY=RIGHT, COLSPAN=1,$
$
ENDSTYLE
END



WebFOCUS 8.1.05M Unix Self-Service/MRE/Report Caster - Outputs Excel, PDF, HTML, Flat Files
January 14, 2012, 08:36 PM
dbeagan
Joy, does this work for you?

 TABLE FILE CAR
SUM DEALER_COST
BY MODEL AS ''
ACROSS COUNTRY AS 'MODEL'
ON TABLE SET ACROSSTITLE SIDE
ON TABLE SET ACROSSLINE SKIP
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
END
 



WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF
January 16, 2012, 11:04 AM
Francis Mariani
Well, it should, shouldn't it?!

You've uncovered another amazing new (probably) v7.7 option I was not aware about!

That's three new options within a few days - ones that were not covered in a recent "New Features" session at the local IBI User Forum.




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
January 16, 2012, 02:44 PM
Joy_Jacques
Thanks everyone.

dbeagan yours does work, but in using CAR as my code sample I didn't add enough information, I have multiple BY columns and the users wants them because he's going to pivot the table in excel. So I have to show those coumn headings. I just need to move the ACROSS columns down.

I will tell them that for now they will have to manually remove them.


7.7.2, Windows, html
January 17, 2012, 03:57 PM
<FreSte>
Hi,

If you are using the output using in Excel to pivot, maybe you can use this (as per manual)
It will create a pivot table for you:

TABLE FILE CAR
HEADING
"CAR File PivotTable"
"Sum of Sales by Car Across Model"
PRINT SALES
BY CAR
ACROSS MODEL
ON TABLE COLUMN-TOTAL
ON TABLE SET STYLE *
TYPE=HEADING, LINE=1, FONT='ARIAL', COLOR=PURPLE, SIZE=16, STYLE=BOLD,$
TYPE=HEADING, LINE=2, FONT='ARIAL', COLOR=PURPLE, SIZE=12, STYLE=BOLD,$
TYPE=DATA, FONT='ARIAL', COLOR=PURPLE,$
TYPE=GRANDTOTAL, FONT='ARIAL', COLOR=PURPLE, SIZE=16, STYLE=BOLD,$
ENDSTYLE
ON TABLE PCHOLD AS CARPIVOT FORMAT EXL2K PIVOT
PAGEFIELDS COUNTRY
CACHEFIELDS RETAIL_COST TYPE
END

January 18, 2012, 09:47 AM
GamP
Or:
TABLE FILE CAR
SUM DEALER_COST AS ''
BY CAR 
BY MODEL
ACROSS COUNTRY
ON TABLE SET ASNAMES ON
ON TABLE HOLD
END
TABLE FILE HOLD
PRINT *
END



GamP

- Using AS 8.2.01 on Windows 7 - IE11.
in Focus since 1988