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,

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

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
 

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.

• ACROSS Column title behaviour improvement
  
• Set the default behaviour for date related language
  
• -READFILE to read a file using the metadata instead of variable definitions
  

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.

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

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