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
Win10 / IE11
AHTML EXL2K PDF
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'
WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
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' ENDWorks 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
Win10 / IE11
AHTML EXL2K PDF
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,
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
Focus, WebFocus 8105 on Windows
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.
That should let them know how many of us would like this
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
|Powered by Social Strata|