Using Data as Header for ACROSS

January 11, 2007, 05:09 PM

Hi all -

I have a report that kinda looks like this:
TABLE FILE CAR
PRINT
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY
ACROSS BODYTYPE
ON TABLE PCHOLD FORMAT EXL2K
END

The business wants to output the report to an Excel spreadsheet so they can in turn run other processes on it. But, they require that the Bodytype value appear on the header line for EACH print column as opposed to the Bodytype value spanning all three print columns. See desired output below:

COUNTRY COUPE COUPE COUPE HARDTOP
DEALER_COST RETAIL_COST SALES DEALER_C
------- ----------- ----------- ----- --------
ENGLAND 9,999.99 9,999.99 999 9,999.99

Notice Coupe appear on all three print columns, then Hardtop appears on the next 3 columns, etc. (Can't show the rest since the text wraps).

I tried doing this
PRINT DEALER_COST AS '
but, it didn't work.

Any suggestions?

January 11, 2007, 05:59 PM

FrankDutch

what WF version do you work with?

January 11, 2007, 06:40 PM

Francis Mariani

DEFINE FILE CAR
BODYTYPE_TEXT/A10 = 'BODYTYPE';
END
TABLE FILE CAR
PRINT
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY
ACROSS BODYTYPE AS ''
ACROSS BODYTYPE_TEXT AS ''
ON TABLE PCHOLD FORMAT EXL2K
END

If it's acceptable to have the text 'BODYTYPE' under instead of over the BODYTYPE, then here's a quick and dirty solution which hopefully works for your real-world problem.

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 12, 2007, 04:36 PM

Anatess

I'm on 7.1.4

Francis -
Excellent suggestion, except, the ACROSS value still spans across all three fields. I was hoping the ACROSS value will appear on EVERY print column.

January 12, 2007, 05:13 PM

susannah

do it the hard way
use HEADALIGN=BODY
and make a HEADER line with COLSPAN
HEADING
"<+0> nothing <+>bodytype<+0>bodytype<+>bodytype"
TYPE=HEADING,LINE=1,ITEM=1,COLSPAN=6,$
TYPE=HEADING,LINE=1,ITEM=2,COLSPAN=1,$
...etc
tinker around till you get this header row to behave like you want it to.

January 13, 2007, 08:25 PM

Piipster

quote:
Originally posted by Piipster:
FILEDEF FSEQ DISK C:\IBI\APPS\BASEAPP\FSEQ.MAS
-RUN
JOIN BLANC WITH DEALER_COST IN CAR TO CONTROL IN FSEQ AS J1
DEFINE FILE CAR
BLANC/A1 = ' ';
DISP_VALUE/D9 = IF COUNTER EQ 1 THEN DEALER_COST
ELSE IF COUNTER EQ 2 THEN RETAIL_COST
ELSE SALES;
DISP_TITLE/A12 =
IF COUNTER EQ 1 THEN 'DEALER_COST'
ELSE IF COUNTER EQ 2 THEN 'RETAIL_COST'
ELSE 'SALES';
END
TABLE FILE CAR
SUM
DISP_VALUE AS ''
BY COUNTRY
ACROSS BODYTYPE AS '' NOPRINT
ACROSS COUNTER NOPRINT
ACROSS BODYTYPE AS ''
ACROSS DISP_TITLE AS ''
WHERE COUNTER LE 3;
ON TABLE PCHOLD FORMAT EXL2K
END

...and the FSEQ.MAS ...
FILE=FSEQ, SUFFIX=FIX, DATASET=FSEQ.MAS
SEGNAME=SEG1, SEGTYPE=S1, $
FIELD=CONTROL, ALIAS=BLANK, USAGE=A1, ACTUAL=A1, $
SEGNAME=SEG2, SEGTYPE=S1, PARENT=SEG1, OCCURS=VARIABLE, $
FIELD=WHATEVER, ALIAS=WHATEVER, USAGE=A1, ACTUAL=A1, $
FIELD=COUNTER, ALIAS=ORDER, USAGE=I4, ACTUAL=I4, $

Make sure there is a space before the word FILE

	*Frank*
	prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7