I am still unable to get my COLUMN-TOTAL to produce any value. I simply get a line that says Total and nothing else. Is there anything else that I can do?

Big Guy, your history is gone as is my mind today.

First, is the field your totaling numeric, could you post the FEX.

As some would ask, can you create your problem using the CAR file?

Are you using a subfoot, that is ON TABLE SUBFOOT.

As I recall some one asked if ON TABLE NOTOTAL was still in the FEX, if so delete it.

My two cents for now.

Here is the FEX as it currently stands.

-* File Unpaid Invoice.FEX
-* Default Mode: Report FEX
SET GRAPHSERVURL=http://localhost/ibi_apps/IBIGraphServlet
SET ASNAMES = ON
SQL SQLORA SET DATETIME ON
SQL
SELECT R.REGION_NAME AS RegionName,
fc.fcompany_name as ForeignCompanyName,
ri.invoice_num as InvoiceNumber,
ri.INVOICE_DATE as InvoiceDate,
ri.total_amount as TotalAmount,
e.cost_center as CostCenter,
e.exch_name as ExchangeName
FROM PCMSREGION R,
PCMS.MARKET M,
pcms.fcompany fc,
pcms.fcompany_detail fcd,
pcms.contract c,
pcms.fcomp_contract fcc,
pcms.exchange e,
pcms.exch_contract ec,
pcms.rev_invoice ri,
pcms.exch_contract_rinvoice ecr
WHERE R.REGION_PK = ®ION_PK
AND M.REGION_FK = R.REGION_PK
AND E.MARKET_FK = M.MARKET_PK
AND ec.exchange_fk = e.exchange_pk
AND ecr.rev_invoice_fk = ri.rev_invoice_pk
AND ecr.EXCH_CONTRACT_FK = ec.exch_contract_pk
AND ec.fcomp_contract_fk = fcc.fcomp_contract_pk
AND fcc.CONTRACT_FK = c.CONTRACT_PK
AND fcc.FCOMPANY_DETAIL_FK = fcd.FCOMPANY_DETAIL_PK
AND fc.fcompany_pk = fcd.fcompany_fk
AND ri.PAYMENT_RECVD = 'N'
ORDER BY ri.invoice_num;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS DDD
END

DEFINE FILE DDD
INVDATE/MtDYY = HDATE(INVOICEDATE,'MDYY');
TOTAMT/P11.2M = TOTALAMOUNT;
END

TABLE FILE DDD
HEADING CENTER
"Unpaid Revenue Invoices For ""
PRINT
EXCHANGENAME AS 'Exchange'
COSTCENTER AS 'Cost Center'
BY INVOICENUMBER AS 'Invoice No.'
BY INVDATE AS 'Invoice Date'
BY FOREIGNCOMPANYNAME AS 'Foreign Vendor'
BY TOTAMT AS 'Total Due'
BY EXCHANGENAME NOPRINT
BY COSTCENTER NOPRINT
ON TABLE COLUMN-TOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
SIZE=9,
$
ENDSTYLE
END

I am not using a SUBFOOT and the ON TABLE NOTOTAL has been removed. Does the format of P11.2M make the column non-numeric? I don't know. Any help would be appreciated.

I think FOCUS will only COLUMN-TOTAL verb objects, not BY fields. Try putting a NOPRINT on the BY TOTAMT and adding it a verb object.

I tested this on a table of ours and it seems to fix the issue, if I'm understanding what you need correctly.

Good luck!

Well, codermonkey, your post was right. As soon as I made it a print field and not a by field, I did get a total. However, the total is not correct. Let me try and explain how. When we create an invoice, many different exchanges can be added to it. We keep a running total when an exchange is added and put the total dollar amount as the invoice amount. When we are printing this report, they want to see all invoices that are unpaid and all exchanges included in the invoice. The problem is for each exchange on the invoice, it adds the total invoice amount in. When we get a new invoice then it should add the amount into the total, not for each record. There are a total of 59 invoices and 139 exchanges. Does this clarify my problem for you or does it make it worse?

This message has been edited. Last edited by: Big Guy,

Try

SUM FST.TOTAMT
BY INVOICENUMBER AS 'Invoice No.'
BY INVDATE AS 'Invoice Date'
BY FOREIGNCOMPANYNAME AS 'Foreign Vendor'
BY TOTAMT AS 'Total Due'
BY EXCHANGENAME NOPRINT
BY COSTCENTER NOPRINT

before the PRINT and see what happens

If it produces the right number then play form there.

Your suggestion produced the same result as the COLUMN-TOTAL and printed the value on every output line. I really only want that total to appear at the end of the report. Let me ask some questions. Is it possible to do a select of records, total the value I am looking for and hold that value to output later? I would then do a second select to get all the detail and at the end of that, could I then output the total from the first select? It sounds to me like this could be done, but I have not found a way to do this yet. Thanks for any suggestions.

you can do this in two ways,

1ST METHOD
----------
DEFINE FILE CAR
RC/P17.2=IF COUNTRY NE LAST COUNTRY THEN RC + RETAIL_COST ELSE RC;
END

TABLE FILE CAR
PRINT *
BY COUNTRY
ON TABLE SUBFOOT
"TOTAL END


2ND WAY
--------

TABLE FILE CAR
SUM DST.RETAIL_COST
BY COUNTRY
ON TABLE SAVE AS RC
END

-READ RC &RCOST
-RUN

TABLE FILE CAR
PRINT *
BY COUNTRY
ON TABLE SUBFOOT
"TOTAL &RCOST "
END


Hope any of this one will solve your issue.

WFConsultant

WF 8105M on Win7/Tomcat

Try something like this....
DEFINE FILE DDD
INVDATE/MtDYY = HDATE(INVOICEDATE,'MDYY');
TOTAMT/P11.2M = TOTALAMOUNT;
END

TABLE FILE DDD
HEADING CENTER
"Unpaid Revenue Invoices For ""
SUM TOTAMT AS 'Total Due'
BY INVOICENUMBER
BY INVDATE
PRINT
EXCHANGENAME AS 'Exchange'
COSTCENTER AS 'Cost Center'
BY INVOICENUMBER AS 'Invoice No.'
BY INVDATE AS 'Invoice Date'
BY FOREIGNCOMPANYNAME AS 'Foreign Vendor'
BY EXCHANGENAME NOPRINT
BY COSTCENTER NOPRINT
ON TABLE COLUMN-TOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
SIZE=9,
$
ENDSTYLE
END

Let me know if I've interpreted your needs correctly.

You could. But I may have put to many sort fields the NOPRINT was left off to see what was there.

Try

SUM FST.TOTAMT NOPRINT
BY INVOICENUMBER AS 'Invoice No.'
BY INVDATE AS 'Invoice Date'
BY FOREIGNCOMPANYNAME AS 'Foreign Vendor'

If the TOTAMT is on the invoice date level
Then
ON TABLE SUMMARIZE TOTAMT AS 'INVOICED TOTAL'

Here is a fex I run and a sample of the output.
[/code]
SET STYLEMODE = FIXED
SET PAGE = NOLEAD
JOIN CLEAR *
JOIN TERM_CYT AND STU_ID IN RTSPTBL_UNO_PROD TO ALL TERM_CYT AND STU_ID IN
RTPGTBL_UNO_PROD AS AJPG
END
TABLE FILE RTSPTBL_UNO_PROD
HEADING
"UNDERGRADUATE CALCULATED CLASS AVERAGE"
"EXCLUDES GRADES OF ' ' AND 'W'"
"INCLUDES SPU'S"
" " SUM RT335/P10.03 NOPRINT RT340/P12.03 NOPRINT
COMPUTE CAVG/P10.03 = RT340 / RT335; AS 'CLASS,AVERAGE'
BY TERM_CYT AS 'TERM CODE'
BY CLASS_LEVEL AS 'CLASS LEVEL'
ON CLASS_LEVEL SUMMARIZE MULTILINES CAVG
ON TERM_CYT SUMMARIZE MULTILINES CAVG AS 'UGRAD TERM AVG'
ON TABLE SUMMARIZE CAVG AS 'AVERAGE OF TERMS'
IF TERM_CYT EQ '20013' OR '20003' OR '19993'
IF OFFICIAL_GRADE NE 'W' OR ' '
IF RT14E EQ 'U'
IF ACAD_PGM_SEQ EQ 1
END [\code]
UNDERGRADUATE CALCULATED CLASS AVERAGE
EXCLUDES GRADES OF ' ' AND 'W'
INCLUDES SPU'S

CLASS
TERM CODE CLASS LEVEL AVERAGE
--------- ----------- -------
19993 FR 2.729

JR 3.137

SO 2.983

SPU 3.572

SR 3.308

UGRAD TERM AVG 19993 3.034

20003 FR 2.745

JR 3.187

SO 3.011

SPU 3.528

SR 3.310

UGRAD TERM AVG 20003 3.044

20013 FR 2.761

JR 3.117

SO 2.940

SPU 3.514

SR 3.249

UGRAD TERM AVG 20013 3.007


AVERAGE OF TERMS 3.028

Thanks to all who provided information for my problem. The report is now working just fine and is displaying the correct total. Again, THANKS for the help. I do appreciate it.