Do not print report with any data € 0.00

[ssatech]

Help with printing a report without field value containing €0.00.
I have a report based on the following query:

SELECT Customers.Name, CustomerTransactionAnalysisTemp.Description, CustomerTransactionAnalysisTemp.Date, CustomerTransactionAnalysisTemp.TransType, CustomerTransactionAnalysisTemp.Receipt, CustomerTransactionAnalysisTemp.Sale, CustomerTransactionAnalysisTemp.SaleCredit, CustomerTransactionAnalysisTemp.CompanyID, CustomerTransactionAnalysisTemp.TransRef, CustomerTransactionAnalysisTemp.CustomerID, Companies.Name, CustomerTransactionAnalysisTemp.OpeningBalanceD, CustomerTransactionAnalysisTemp.OpeningBalanceC, CustomerTransactionAnalysisTemp.EuroReceipt, CustomerTransactionAnalysisTemp.EuroSale, CustomerTransactionAnalysisTemp.EuroSaleCredit, CustomerTransactionAnalysisTemp.EuroOpeningBalanceD, CustomerTransactionAnalysisTemp.EuroOpeningBalanceC
FROM (CustomerTransactionAnalysisTemp INNER JOIN Customers ON CustomerTransactionAnalysisTemp.CustomerID = Customers.CustomerID) INNER JOIN Companies ON CustomerTransactionAnalysisTemp.CompanyID = Companies.CompanyID;

My report prints the following data based on the above query
and data determined from below:

EuroTXOBSum: =Sum([EuroOpeningBalanceD]-[EuroOpeningBalanceC]) Label name on report: Opening Balance

EuroSumSale:=Sum([EuroSale]) Label name on report: Sale


EuroSumSaleCredit:=Sum([EuroSaleCredit]) Label name on report: Credits

EuroSumReceipt:=Sum([EuroReceipt]) Label name on report: Receipts

Sum Balance:=(([EuroSumSale]-[EuroSumSaleCredit])-[EuroSumReceipt])+[EuroTXOBSum] Label name on report: Closing Balance

What I need to do is print a report only for those customers that have a closing balance number value and not show any customers with a (Sum Balance)Closing Balance of €0.00.I cannot seemed to do this based on the above query as the numerical values in the report are calculated from above example.

Appreciate any help/ideas how I might accomplish this..

Thanks

 

[lupins46]

I think it will be very difficult to do this in the report.
I would create a query which calculates the balances only for each customer and only includes in the output thos with a non-zero balance. This will be groupby/totals query. Once you have done this you now have a list of all customers to print and you base your report on a query which joins your current query to the totals query on customer.

 

[ssatech]

Lupins,

Thanks for your suggestion. I'll have to try that approach
and see what happens.....

 

[misscrf]

I would think there would be 1 of 2 ways to word the criteria
is not null
if that doesnt work, then field<.01


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[ssatech]

Misscrf,
I'll have to try your suggestion --- Thanks again!!