I have a report that I am working on. We have an invoice file that contains fields like invoice number and total for the invoice. When we generate the invoice, our user selects which items from another file are to be included in the invoice.

My problem lies in when I go to print a report about the invoices, they want to see the invoice number, date, vendor, and total from the first file, along with exchange name and company number from the second file. When I do this, I can get the total to be correct and not list all supporting detail, or I can get the detail to print correctly, but no totals appear.

As you can see, the second file can contain many records to one record in the firat file. Do you need me to post code that I have tried?

Any help would be appreciated, and thanks in advance.

Can you join the two files based on the invoice number?

We do join on the primary key from file one with the foreign key in file two. There are several other tables that are joined together to get all possible information they may want to see.

Here is my code:

-* 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_rinvoice_pk in (SELECT MIN(EXCH_CONTRACT_RINVOICE_PK)
-* FROM PCMS.EXCH_CONTRACT_RINVOICE
-* GROUP BY REV_INVOICE_FK)
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
INVHOLD/A20 = '0';
INVDATE/MtDYY = HDATE(INVOICEDATE,'MDYY');
TOTAMT/P11.2M = TOTALAMOUNT;
-IF INVHOLD = INVOICENUMBER GOTO CONTINUE;
GTOT/P11.2M = GTOT + TOTAMT;
INVHOLD/A20 = INVOICENUMBER;
-CONTINUE
END

TABLE FILE DDD
-*ON TABLE SET BYDISPLAY ON
HEADING CENTER
"Unpaid Revenue Invoices For ""
PRINT
-*' ' SUP-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
FOOTING
""
"<80>Total <+4>"
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
SIZE=9,
$
ENDSTYLE

The problem with this code is that it adds the invoice total for every exchange on the invoice, when the total from the invoice file is the total of all exchanges included in the invoice. The commented out AND in the Where clause is what allowed me to get all the detail records. When that was active in the code, I recieved the correct total of the invoices, but only the first exchange was listed on the report.

Hope this helps better.

I think I understand your problem, a multi verb request could give you the output you want.

Give this a try:

TABLE FILE CAR
SUM
     RETAIL_COST
BY
     COUNTRY
PRINT
     MODEL
BY
     COUNTRY
BY
     CAR
END
-RUN

There is another idea I have that may help you. I will try to write you and example of it using the CAR table.

Here is the other example I was talking about:

TABLE FILE CAR
SUM
     RETAIL_COST
BY
     COUNTRY
ON TABLE HOLD AS H0 FORMAT ALPHA
END
-RUN

TABLE FILE CAR
PRINT
     MODEL
BY
     COUNTRY
BY
     CAR
ON TABLE HOLD AS H1 FORMAT ALPHA
END
-RUN

MATCH FILE H0
PRINT
     RETAIL_COST
BY
     COUNTRY
RUN
FILE H1
PRINT
     MODEL
     CAR
BY
     COUNTRY
AFTER MATCH HOLD AS H2 OLD-AND-NEW
END
-RUN

TABLE FILE H2
PRINT *
END
-RUN

That works great if I want the total to print on every line. I want the total to print in a footing at the end of the report. Any further suggestions?

You can add this to have it print in a Footing

ON TABLE SUBFOOT
" "
"HERE'S THE TOTAL: < TOT.RETAIL_COST"
" "

Or you can just put

ON TABLE COLUMN-TOTAL. To get a total at the end.

Here is a full example for you using the MATCH FILE:

TABLE FILE CAR
SUM
     RETAIL_COST
BY
     COUNTRY
ON TABLE HOLD AS H0 FORMAT ALPHA
END
-RUN

TABLE FILE CAR
PRINT
     MODEL
BY
     COUNTRY
BY
     CAR
ON TABLE HOLD AS H1 FORMAT ALPHA
END
-RUN

MATCH FILE H0
PRINT
     RETAIL_COST
BY
     COUNTRY
RUN
FILE H1
PRINT
     MODEL
     CAR
BY
     COUNTRY
AFTER MATCH HOLD AS H2 OLD-AND-NEW
END
-RUN

TABLE FILE H2
PRINT
     RETAIL_COST NOPRINT
     MODEL
BY
     COUNTRY
BY
     CAR
ON COUNTRY PAGE-BREAK
ON COUNTRY SUBHEAD
"SUBHEAD Total Retail Cost: <ST.RETAIL_COST"
ON COUNTRY SUBFOOT
"SUBFOOT Total Retail Cost: <ST.RETAIL_COST"
ON TABLE PCHOLD FORMAT PDF
END
-RUN

The ON TABLE COULMN-TOTAL does not print out any value and that may be due to the fact that the field that I want totaled in defines as P11.2M. This is only my guess. I have tried all suggestions and have not been able to get any closer to what I really need.

Any other suggestions????

quote:

The ON TABLE COULMN-TOTAL does not print out any value and that may be due to the fact that the field that I want totaled in defines as P11.2M. This is only my guess. I have tried all suggestions and have not been able to get any closer to what I really need.

In the above you have COLUMN mis-spelled, assuming that is a typeographincal error. Do you get a blank or do you get ********.

If ****** then your column-total value is bigger than the format clause of your numeric field.

Yes, that was my typographical error. I do get a line that says Total and nothing else.

If this is still in your program...take it out.

ON TABLE NOTOTAL

The TOTAL is now working and the report is running fine. Thanks for all the suggestions.