Issue: Across fields always creates a blank space between the title and the actual value which make filtering hard when exporting to excel.
Note the BLANK LINE to the right of "MODEL"

Sample Code :

-SET &ONorOFF = OFF ;
SET DROPBLNKLINE = &ONorOFF
-RUN
TABLE FILE CAR SUM DCOST BY MODEL ACROSS COUNTRY
"SET DROPBLNKLINE = &ONorOFF.EVAL"
ON TABLE PCHOLD FORMAT XLSX AS 'DROP_&ONorOFF.EVAL'
END

Sample Results :

SET DROPBLNKLINE = OFF					
	COUNTRY				
	ENGLAND	FRANCE	ITALY	JAPAN	W GERMANY
MODEL					
100 LS 2 DOOR AUTO	.	.	.	.	5,063
2000 4 DOOR BERLINA	.	.	4,915	.	.
2000 GT VELOCE	.	.	5,660	.	.
2000 SPIDER VELOCE	.	.	5,660	.	.
2002 2 DOOR	.	.	.	.	5,800
2002 2 DOOR AUTO	.	.	.	.	6,000
3.0 SI 4 DOOR	.	.	.	.	10,000
3.0 SI 4 DOOR AUTO	.	.	.	.	11,000
504 4 DOOR	.	4,631	.	.	.
530I 4 DOOR	.	.	.	.	8,300
530I 4 DOOR AUTO	.	.	.	.	8,400
B210 2 DOOR AUTO	.	.	.	2,626	.
COROLLA 4 DOOR DIX AUTO	.	.	.	2,886	.
DORA 2 DOOR	.	.	25,000	.	.
INTERCEPTOR III	14,940	.	.	.	.
TR7	4,292	.	.	.	.
V12XKE AUTO	7,427	.	.	.	.
XJ12L AUTO	11,194	.	.	.	.

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

Are you looking for ACROSSTITLE=SIDE ?
See this Martin vK post

BY MODEL AS '' removes the blank line but gives you no title for the BY column.

With SET ACROSSTITLE = SIDE and ACROSS COUNTRY AS 'MODEL', you can trick to get the title. With some styling you can left align, but now you loose the across title. Or you could combine with ACROSS COUNTRY AS 'MODEL \ COUNTRY'

Thanks Guys, I like that...

SET ACROSSTITLE = SIDE
SET PAGE = NOLEAD
-RUN
TABLE FILE CAR SUM DCOST BY CAR AS '' BY MODEL AS '' ACROSS COUNTRY AS 'Car, Model / Countries'
"This is using: 'SET ACROSSTITLE = SIDE'"
-*ON TABLE PCHOLD FORMAT XLSX AS 'DROP_ACROSSTITLE_SIDE'
END

Works as expected. However, how can we do that with associated Column Titles for my By Fields? I guess it may be a NFR such as DROPBYACROSSBLANKLINE...

Almost There (maybe)...
I need City, Category, 1997/12/01, 1997/11/01, and 1997/10/01 to be displayed over the associated columns, all on a single line. Here's the simplified code using GGSALES:

SET EMPTYREPORT = ON
SET ACROSSTITLE = SIDE
SET PAGE = NOLEAD
TABLE FILE GGSALES
SUM DOLLARS BY LOWEST 2 CITY BY CATEGORY ACROSS HIGHEST 3 DATE AS ''
WHERE CITY GE 'New York'
WHERE DATE GE 19971001
ON TABLE PCHOLD FORMAT XLSX
END

Perhaps this will meet your needs:

SET ACROSSTITLE = SIDE
SET PAGE = NOLEAD
TABLE FILE GGSALES
    SUM DOLLARS        AS ''
     BY LOWEST 2 CITY  AS ''
     BY CATEGORY       AS ''
 ACROSS HIGHEST 3 DATE AS 'CITY / CATEGORY'
WHERE CITY GE 'New York'
WHERE DATE GE 19971001
ON TABLE PCHOLD FORMAT XLSX
END  

dbeagan,

That's what I have in my 9:42 post, formatted differently. Thanks for the suggestion, unfortunately, it doesn't work as explained in my 14:30 post.

It's all to do with "ACRSVRBTITL"...

I changed "SKIP" to "DROP" for consistency with other BLANK line SETs.

It looks like what I need is a NFR that'll add DROP as an option to ACRSVRBTITL which would remove that blank space completely.

The line I'm seeing is the area where the ACROSS verb title would be.

Right now the values are:
OFF: always show title regardless of number of verb objects and
HIDEONE: does not show title if there is only one verb object.
NFR: DROP: hides the title and removed the area reserved for the title.

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

Doug,

Have you tried the trick posted by Dan here: http://forums.informationbuild...547035126#1547035126

 
TABLE FILE GGSALES
SUM DOLLARS 
BY LOWEST 2 CITY NOPRINT
BY CATEGORY NOPRINT
ACROSS HIGHEST 3 DATE AS ''
  COMPUTE XCITY1/A70 = CITY ; AS 'CITY 1'
  COMPUTE XCITY2/A70 = CITY ; AS 'CITY 2'
  COMPUTE XCATEGORY/A70 = CATEGORY ; AS 'CATEGORY'
WHERE CITY GE 'New York'
WHERE DATE GE 19971001
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
  TYPE=REPORT, COLUMN=XCITY1, SEQUENCE=1, $
  TYPE=REPORT, COLUMN=XCITY2, SEQUENCE=2, $
  TYPE=REPORT, COLUMN=XCATEGORY, SEQUENCE=3, $
ENDSTYLE
END
 

Thanks Pku, I've not yet tried that. But I will. I used another one, from Walter Walter Brengel, as a work-around that prompted my NFR.

quote:

Originally posted by Doug:
Thanks Pku, I've not yet tried that. But I will. I used another one, from Walter Walter Brengel, as a work-around that prompted my NFR.

Thanks for sharing @Pku.

Doug, you should try that. This gives you exactly what you need.

 
SET PAGE-NUM = OFF

TABLE FILE GGSALES
SUM DOLLARS AS ''
BY LOWEST 2 CITY NOPRINT
BY CATEGORY NOPRINT
ACROSS HIGHEST 3 DATE AS ''
  COMPUTE XCITY1/A70 = CITY ; AS 'City'
  COMPUTE XCATEGORY/A70 = CATEGORY ; AS 'Category'
WHERE CITY GE 'New York'
WHERE DATE GE 19971001
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=XCITY1, SEQUENCE=1, $
TYPE=REPORT, COLUMN=XCATEGORY, SEQUENCE=2, $
ENDSTYLE
END

-RUN
 

I agree that the New Feature Request (NFR) would make it A LOT easier, and you should, in fact we ALL should submit that NFR so that IBI knows that there is a big interest in it. But that is a GREAT work around.

How about if I add a POLL to this post as well?
That should let them know how many of us would like this. The More, The Better...
Done: New Poll...Vote On This Now, Please.

This is the Final Work-Around:

DEFINE FILE GGSALES
MYDATE/A10=FPRINT(DATE,'I8YYMD','A10');
END

TABLE FILE GGSALES
SUM DOLLARS AS ' '
BY LOWEST 2 CITY 
BY CATEGORY 
ACROSS HIGHEST 3 MYDATE AS ''
WHERE CITY GE 'New York'
WHERE DATE GE 19971001
ON TABLE HOLD AS HOLDGGS
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
END

TABLE FILE HOLDGGS
PRINT *
ON TABLE SET PAGE NOLEAD
ON TABLE PCHOLD FORMAT XLSX
END