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?
April 20, 2006, 01:46 PM
Leah
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.
Leah
April 20, 2006, 02:20 PM
Big Guy
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.
April 20, 2006, 02:33 PM
codermonkey
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!
April 20, 2006, 03:42 PM
Big Guy
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,
April 20, 2006, 04:14 PM
Leah
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.
Leah
April 21, 2006, 09:22 AM
Big Guy
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.
April 21, 2006, 09:42 AM
Kamesh
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
April 21, 2006, 09:42 AM
Kamesh
quote:
Originally posted by Kamesh: 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 <RC " 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
April 21, 2006, 09:43 AM
codermonkey
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.
April 21, 2006, 10:03 AM
Leah
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
Leah
April 21, 2006, 10:49 AM
Big Guy
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.