When I run my report and it outputs in Excel the ACROSS by EQNUM is creating duplicate columns for some reason instead of putting all the values in just one unique column for each EQNUM. See screen shot and my code below, why is it doing this?





TABLE FILE T_PROD_INVOICE
SUM
TOTALCOST/P12.2CM
BY REGION
BY SITEID
ACROSS EQNUM
WHERE ( EQNUM EQ 'PMSPRINKLR' OR 'SPRINKLR' OR 'FIREALRM' OR 'PMFIREALRM' OR 'BURGALRM' OR 'FIRE-EXT' ) AND (( T_PROD_INVOICE.ENTERDATE GE DT(2008-02-03 00:00:00) ) AND ( T_PROD_INVOICE.ENTERDATE LE DT(2008-06-24 23:59:00) )) AND ( T_PROD_POLINE.GLDEBITACCT CONTAINS '69300400000' OR '65110000000' OR '67810000000' OR '65120000000' );
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT EXL2K

TABLE FILE CAR
SUM 
SALES/P12.2CM
BY COUNTRY
BY CAR
ACROSS BODYTYPE
ON TABLE SET PAGE-NUM OFF 
ON TABLE SET BYDISPLAY ON 
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT EXL2K
END

I can't see your screen shot but is this sample code similar to what you want to do? I don't see the problem you are having.

Ginny, go here to see the image screen shot, to see the problem I am having.

http://img364.imageshack.us/img364/8656/excelhj5.jpg

Rob,

Try SET HOLDLIST = PRINTONLY before your fex.

If that works then the reason you're seeing duplicate coluns is the /P12.2C inline format that you are applying. WF, by default, puts the original field into the hold file as well as the reformatted one. The HOLDLIST setting stops that happening so only the reformatted one is output.

T

quote:

Originally posted by Tony A:
Rob,

Try SET HOLDLIST = PRINTONLY before your fex.

If that works then the reason you're seeing duplicate coluns is the /P12.2C inline format that you are applying. WF, by default, puts the original field into the hold file as well as the reformatted one. The HOLDLIST setting stops that happening so only the reformatted one is output.

T

This didn't work.

Also, I tried removing the CM format from the data and that didn't work.

I don't get it, this is so weird.

quote:

Originally posted by Tom Flynn:
Rob,

It's because you are doing a JOIN to T_PROD_POLINE.

Table the data, summarize it with BY's, HOLD it, then display the result from the HOLD...

Hope this helps...

Tom

Still doesn't work....

JOIN
LEFT_OUTER T_PROD_INVOICE.T_PROD_INVOICE.PONUM IN T_PROD_INVOICE TO UNIQUE
T_PROD_POLINE.T_PROD_POLINE.PONUM IN T_PROD_POLINE AS J0
END
JOIN
LEFT_OUTER T_PROD_POLINE.T_PROD_POLINE.REFWO IN T_PROD_INVOICE TO UNIQUE
T_PROD_WORKORDER.T_PROD_WORKORDER.WONUM IN T_PROD_WORKORDER AS J1
END
JOIN
LEFT_OUTER T_PROD_WORKORDER.T_PROD_WORKORDER.SITEID IN T_PROD_INVOICE TO UNIQUE
T_PROD_MAX_ATTRIBUTES_LOCATION.T_PROD_MAX_ATTRIBUTES_LOCATION.SITEID
IN T_PROD_MAX_ATTRIBUTES_LOCATION AS J2
END
TABLE FILE T_PROD_INVOICE
SUM
TOTALCOST
BY REGION
BY SITEID
BY EQNUM
WHERE ( T_PROD_POLINE.EQNUM EQ 'PMSPRINKLR' OR 'SPRINKLR' OR 'FIREALRM' OR 'PMFIREALRM' OR 'BURGALRM' OR 'FIRE-EXT' ) AND (( T_PROD_INVOICE.ENTERDATE GE DT(2008-02-03 00:00:00) ) AND ( T_PROD_INVOICE.ENTERDATE LE DT(2008-06-24 23:59:00) )) AND ( T_PROD_POLINE.GLDEBITACCT CONTAINS '69300400000' OR '65110000000' OR '67810000000' OR '65120000000' );
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE HOLD AS HOLDRONPAIDS FORMAT FOCUS
END

TABLE FILE HOLDRONPAIDS
SUM
TOTALCOST
BY REGION
BY SITEID
ACROSS EQNUM
ON TABLE SET PAGE-NUM OFF
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K

Rob,
make sure your extract file contains JUST EXACTLY the fields you want, and nothing else.
do a research PRINT of that file
BEFORE you put it into excel...
and BEFORE you ask us to debug your JOIN code.
Why in the world are you HOLDing as a FOCUS file...
just HOLD it.
then do a research dump

Rob,

Another tack, maybe.
1. If you output in HTML, do you have the same problem?
2. When I look at you output, you have TOTALCOST as a title: this is due to your format change "/P12.2CM". Also you have 3 occurences of BURGALRM. Could it be that in your database you have variations of the value? Maybe some unprintable characters? Maybe your EQNUM field is VARCHAR and in some places it has extra blanks tacked on?