Running the following fex in PDF and HTML output gives me three years, 1997, 1996 and 1990. Running it in EXL2K output gives me 35431, 35065 and 32874. Is there something wrong with the DEFINE? ORDER_DATE is defined as MDY in the GGORDER master.

DEFINE FILE GGORDER
YEAR1/YY = ORDER_DATE;
YEAR/A4YY = YEAR1;
QTY/D6 = QUANTITY;
END

TABLE FILE GGORDER
SUM
QTY AS ''
ACROSS HIGHEST YEAR AS ''
BY PRODUCT_DESCRIPTION

HEADING
"GG ORDERS BY PRODUCT"
ON TABLE PCHOLD FORMAT EXL2K

ON TABLE SET STYLE *
TYPE=REPORT, FONT='ARIAL', SIZE=9, $
TYPE=HEADING, STYLE=BOLD, $
TYPE=ACROSSVALUE, JUSTIFY=RIGHT, COLOR=NAVY, STYLE=BOLD, $
ENDSTYLE
END

Thanks,

This message has been edited. Last edited by: Francis Mariani,

Wouldn't that (what's wrong with this fex) be the format of the destination column in Excel? Setting that format of that column to a date will convert those numbers?

That would change tose to : 1/1/1997, 1/1/1996, and 1/1/1990.

I would include the slashes in the dates so that Excel automatically sees the dates as dates.

Perhaps leaving it as format YY will stop the number.

Solution: make the reported YEAR field a plain (non-date) A4.

(you need the define with A4YY in order to cast the YY to a character variable;
but you can then redefine the A4YY as an A4, without changing the fieldname, so no change in the TABLE code)

DEFINE FILE GGORDER
YEAR1/YY = ORDER_DATE;
YEAR/A4YY = YEAR1; 
YEAR/A4 = YEAR;    <==
QTY/D6 = QUANTITY;
END
. . .

And of course that will work in the other output formats as well.

DEFINE FILE GGORDER
YEAR/YY = ORDER_DATE;
QTY/D6 = QUANTITY;
END

Indeed Waz is right. Just leave it as YY.
I have no idea why an A4YY field behaves like this in excel, but if you really want to kknow, I'd suggest to open a case with IB asking for an explanation.

Thanks everyone. I didn't really require a solution, I wanted to know what was wrong with the code. It's strange that the A4YY field is treated as numeric for EXL2K only.

This message has been edited. Last edited by: Francis Mariani,

quote:

It's strange that the A4YY field is treated as numeric for EXL2K only.

Not really Francis, as Excel will treat anything that looks like a numeric as numeric. But that's Good ol' Microshaft interpreting what they think you want because, of course, no Microshaft user ever knows what they want to do, do they!

T

I simplified the DEFINE and it works:

DEFINE FILE GGORDER
YEAR/YY = ORDER_DATE;
QTY/D6 = QUANTITY;
END

TABLE FILE GGORDER
SUM
QTY AS ''
ACROSS HIGHEST YEAR AS ''
BY PRODUCT_DESCRIPTION

HEADING
"GG ORDERS BY PRODUCT"
ON TABLE PCHOLD FORMAT PDF

ON TABLE SET STYLE *
TYPE=REPORT, FONT='ARIAL', SIZE=9, $
TYPE=HEADING, STYLE=BOLD, $
TYPE=ACROSSVALUE, JUSTIFY=RIGHT, COLOR=NAVY, STYLE=BOLD, $
ENDSTYLE
END

So, now YEAR is a DATE field passed to Excel which doesn't treat it as a number, go figure!

Thanks,