Customer Balance Report yielding null report
Greetings All,
After having been out of country and out of touch for three months, I returned to find that the clinic's June 30 year-end financials are being held up because of the inability to retrieve an Accounts Receivable report as of June 30. We always had trouble with the 'customer balance report' yielding null results, and so I modified it such that the Balance Date = current date. The report then runs. Unfortunately, it appears that they did not run the report on the morning of July 1. Aarg!
Until I can migrate to 1.8, we will be using 1.7. But I checked the 1.8 demo on the OVPMS website, created an unpaid invoice and ran the same report from the demo. It still yields null results.
The SQL query from the default 1.7 report is as follows:
SELECT
acts.activity_start_time as date,
if(credit,-financial_acts.total,financial_acts.total)as total,
entities.`name` AS customer
FROM
`acts` acts INNER JOIN `financial_acts` financial_acts ON acts.`act_id` = financial_acts.`financial_act_id`
INNER JOIN `participations` participations ON acts.`act_id` = participations.`act_id`
INNER JOIN `entities` entities ON participations.`entity_id` = entities.`entity_id`
WHERE
date(acts.activity_start_time) < $P{balanceDate}
AND (participations.arch_short_name = "participation.customer")
AND (acts.arch_short_name = "act.customerAccountOpeningBalance")
AND (total <> 0.00)
ORDER BY
customer ASC
Could someone please confirm that the report is not working, or suggest why it is not for us? The only thing that caught my eye was that acts.activity_start_time I think is being assigned to the local variable "date", whereas date is also a mysql function name. Maybe I'm not reading that correctly? But changing some names there didn't help me anyway.
Thanks,
Sam Longiaru
Kamloops, BC
Re: Customer Balance Report yielding null report
Sam - I did the 1.8 tweak on this report and added explanatory text (but did not change the SQL code). This says
If I run it with today's date I get nothing - or rather just the 'No data Found - check Parameters' message. If I run with a date on which an End Period was run - out comes all the balances.
Regards, Tim G
Re: Customer Balance Report yielding null report
Hi Tim,
Thanks for the reply. OK... I'm into new territory here. We have never done the End Period thing as we have never sent statements... or did so through another report on a client-by-client basis (1 or 2 a month). I've read through the on-line Help for the Reporting-Debtors screen and the Accounts-Concepts. If I set the end period to June 30 and run a debtor's report (or a customer balance report) do you think that would give me the Accounts Receivable value that I need? Is the End Period used only for a single report generation or does it do something non-reversible?
Right now, if I run a simple debtor's report, setting Customer Account Type to All, Statement Date to July 1 and Balances to All, I get a report, but it is missing some customers who we know owed us money on June 30. I'm not sure why some are picked up and others not.
Thanks,
Sam
Re: Customer Balance Report yielding null report
Just a follow-up to this. We noticed that the ones that are missing are clients that have a discount attached. That shouldn't really make a difference in the search should it?
Sam
Re: Customer Balance Report yielding null report
Sam - I have just done a backport to 1.7 of the 1.8 Outstanding Debtors Report and have added it to the Resource Library - see http://www.openvpms.org/customisation/outstanding-debtors-report
This should give you what you want - it works independently of Opening Balance transactions.
End Period: Even if you never regularly send statements out, you should consider running End Period at the end of every accounting period. Then, if a customer asks for a statement you can print one off. If you never run End Period, then you can still print a statement, but it will show all transaction from the specified statement date back to the previous Opening Balance - and since there are none, then you will get every transaction the customer has ever done.
Catch up: there is no reason why you cannot sit down and run all the End Periods for prior dates, ie for 31/12/14, 31/1/15, 28/2/15,.....31/8/15. Just get yourself a big pot of coffee.
Note that when you go to 1.8 you will get the Practice Summary and Key Performance Indicators reports both of which include outstanding debtors info.
Customers with Discounts: should not make any difference. As a test (in a 1.7 system) I added a discount for one customer and re-ran Reporting|Debtors|Report - no change.
Accuracy: I ran Reporting|Debtors|Report (1.7) for
and then ran the backported Outstanding Debtors report for the same date and it gave the same total outstanding.
Regards, Tim G
Re: Customer Balance Report yielding null report
Thank you very much for all that work Tim. That sounds great. I'll try the backported report as that sounds like what we need right now. I'll also take a look at the effects of running the End Period on a copy of our database and try to get a handle on what it is doing. Being able to print out a complete transaction history for a client may not be a bad thing, but I agree that having an End Period makes things a whole lot tidier.
I do have a question about how the End Period works however. Say I run an End Period at the end of each month. Does that act as a single, non-reversible marker? In other words, is there only one End Period date that once set, can't be reset earlier? Or are the opening balances recalculated on the fly for each new End Date, no matter what date is specified. For example, if after setting End Period for the end of last month, a client actually does want to see a complete history... can I go back and reset End Period to a much earlier date and re-run a statement for them that would include all earlier transactions. Or would I just have to use another report in those cases? I'm just wondering if End Date works somewhat like sending Reminders in that once done, can't really be reversed.
As for the discounted customers, it will be interesting to see if the backported report picks them up. It is the only thing we can see different in how those customers are specified. And they are our biggest customers... hence the discount.
I'll let you know how it works. And thanks again for your effort here. Very much appreciated.
Sam
Re: Customer Balance Report yielding null report
Tim,
I have run the report. Results are the same as when I simply run a debtor's report from the screen. It is not picking up those clients who have a blanket discount and who we know owed us on June 30. If these reports are only looking at transactions, I can't see where the issue might be. One client is the SPCA which has SPCA both as Last Name and Company name in Customer-Information. It has a blanket SPCA-specific discount attached. The other is an individual who has a separate, personal discount attached. Her customer information is set up just like for any other client. We don't use Account Types for anyone, so all of that is the same.
While we could go back and do the accounts for these two by hand, these are only two that we can think of. There might be more. I'm concerned that the discounts may be a red herring and that there are others that are not getting picked up for some bizarre reason.
Sam
Re: Customer Balance Report yielding null report
Humm - I think that the next step is for you to run the Customer Running balance that I am about to send you. One other thought: 'owed us at June 30' - remember that the statements and outstanding debtors reports are only looking at finalised transactions. If on June 30 you knew they owed you because you had just generated a big invoice - it would not show up if it was not finalised.
I cannot see that the has/has not a discount situation has any effect.
Regards, Tim G
Re: Customer Balance Report yielding null report
Sam - running End Period does as it says in http://www.openvpms.org/documentation/csh/1.7/concepts/accounting
period end: process all accounts. For any account with an overdue balance (see Administration|Customer Account Type), check whether accounting fees should be applied, and if so generate the required Debit Adjustment transaction. For any account with transactions since the previous Opening Balance transaction (if any), generate both a Closing Balance and an Opening Balance transaction for the current account balance.
So in fact you can run Period End for any date, ie if you have already done it for 30/6/15 and 31/7/15 you can if you want run it for 15/7/15.
Note that it generates pairs of transactions, one closing balance, one opening balance - both for the same amount but one credit, one not.
The purpose of these is simply to save processing time - want balance on a given date? - look for prior opening balance and add to this all transactions since the OB txn and the specified date.
Because the standard statement report works off the opening balance prior to the statement date, and because of the way we treat large customers, Trilby wanted a report that would generate a statement that would span a longer period. I ended up writing two "Extended Customer Statement" reports, one that off the opening balances and generates a statement "from the earlier of the 'Date From' and that of the opening balance transaction found prior to the oldest unpaid invoice, or if there are no unpaid invoices, the last opening balance before the From date. If there are no matching opening balance transactions, then all transactions for the account will be shown".
The other works off zero balances and generates a statement "from the earlier of the 'Date from' and that of the first transaction after the last time the customer's outstanding balance was zero".
We also have a Customer Running Balance report (that was put into the 1.8 package). This is intended as 'transaction dumper' and as its explanation says (in part) "Note that the Balance amounts will not be correct unless either the From Date is prior to the customer's first transaction, or it matches a date on which the customer's balance was zero."
I will email you the 1.7 version of the Customer Running Balance report. The Extended Statements are more problematic. I have held back from putting versions in the resource library because they are heavily tied to the way we lay out invoices - we order stuff by patient/date/product type/product.
So - no you cannot reverse an End Period (unless you use SQL to zap the CB/OB transaction pair) to be able to get a longer statement - but you can create an 'Extended Statement' report.
Regards, Tim G
Re: Customer Balance Report yielding null report
OK, thanks. That clarifies in my mind what End Period is doing under the hood. Very flexible.
I do have a running balance report that works well... actually the SPCA always wants each month, a running balance with a complete transaction history. So using aged balance reports to help identify some clients and my running balance report that has an "as of" date, I am able to provide the vets with what they need for the year-end right now. It's not a perfect solution as it relies somewhat on memory as to who owed us. It would be better if either the Debtor's report or the external backported report you provided would pick up the discounted clients for us. Maybe I'll wait until after the upgrade to 1.8 to see if this problem resolves itself as it seems to be specific to us (?).
So I'm good for the time being, I think.
Thanks for all your help on this. Hopefully this problem resolves with the upgrade. If not, maybe we can look again at that issue.
Sam
Re: Customer Balance Report yielding null report
AHAH - cracked it I think.
My outstanding debtors report (and I suspect the Reporting|Debtors system) works off the participations file - using the participation.customerAccountBalance records - but these only exist for customers who owe something CURRENTLY.
For a correct historical outstanding debtors report one needs to do:
for each customer, look for Opening Balance prior to statement date and add that and all transactions up to the statement date.
or more brute force:
for each customer add all transactions (except opening & closing balances) up the the statement date
Sam - I need to get some other non-OpenVPMS done, but as soon as I get that out of the way, I will check and fix the various outstanding debtors reports that I look after.
Tim A - can you confirm that Reporting|Debtors also runs off the participation.customerAccountBalance records - ie if I have a customer who currently owes nothing, but on say 30/6/15 did owe $1234, then they would not appear if I set the statement date to 30/6/15.
Regards, Tim G
Re: Customer Balance Report yielding null report
Yes - Reporting|Debtors does use the participation.customerAccountBalance.
See org.openvpms.archetype.rules.finance.account.CustomerBalanceSummaryQuery
-Tim A
Re: Customer Balance Report yielding null report
I have built a revised Outstanding Debtors report that will report the correct debtors situation at any desired date. The 1.7 version has been added to the resource library at http://www.openvpms.org/customisation/outstanding-debtors-report
If you are running 1.8 or later, you should replace the version that was in the release package with this.
Tim A - I will Jira a 1.8 version after I add support for Account Type and Practice Location selection. We will also need to decide what to do about the fact that the Reporting|Debtors does not display correct information for statement dates in the past. Do we just document the fact that this is the case, or do we fix it? My recommendation is to just document the situation now that we have a report that does provide the correct historical numbers.
Regards, Tim G