Solved

Billing Statement in Crystal Reports with SQL DB - handling a partial payment

Posted on 2016-08-01
12
90 Views
Last Modified: 2016-08-04
I am creating a billing statement from a SQL DB using Crystal Reports, and need to come up with a way to handle multiple payments for one invoice. The attached statement demonstrates my problem. I'm assuming I need to create a view in the SQL DB, but need help. Here is my thought process:
1. Apply the parameter to select the correct job number from [jobs.job_no] (this is working fine in my Crystal select with a parameter)
2. Get the full invoice amount from [ar_invoice.invoice_amount]
3. Check [ar_cash_receipts.cash_receipt] to see if there is a payment
4. If there is a payment in full, then apply the payment value and the resulting balance due is 0
5. If there is a balance due after a partial payment is applied, then create a result with the value of the remaining balance due.
6. Now the invoice_amount should become the remaining balance from the result above, and any future payments would continue to be subtracted until the final balance = 0

Currently the report I've created does not know that a partial payment has been applied, and if a partial payment is made, the invoice repeats at it's full amount when another payment is received.

If there is a better way to do this please advise as well.  Thanks for your help!
0
Comment
Question by:Gary Demos
  • 7
  • 4
12 Comments
 
LVL 34

Expert Comment

by:James0628
ID: 41738350
You mentioned an "attached statement", but I don't see anything attached to your post.

 Are you just trying to display the amount due (original amount - payments) on the report, or are you actually trying to change the db (eg. subtract payments from the original amount, altering the original amount in the db) ?  From item #6, it sounds like it might be the latter.  It might be possible to update the db through CR (eg. by using a stored procedure as the datasource), but it probably isn't a good idea.  And if you did do that, what would happen if you ran the report again with the same parameters?  The payments would be subtracted again, unless you had some way to prevent that from happening.

 And if you don't want to update the db, and just want to show the current total (original amount - payments), that seems simple enough.  Where are you having a problem?

 James
0
 

Author Comment

by:Gary Demos
ID: 41738884
James,

I do not want to update the DB, I just want to show the current total on the report. I'll try attaching the report again.  Thanks
Partial_Payment.pdf
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 41739610
As I understand your report and data, this shouldn't be a problem.

 Based on your sample report, it seems that you have one record for the invoice, and then any payments are in separate records.  And you're presumably grouping/sorting on the original invoice number or something, to get an invoice and its payments together.

 I assume that you're using a formula similar to {Invoice Amount field} - {Paid Amount field} to get the current balance.  Create a running total for Paid Amt and set it to reset when that invoice group/sort field changes, to get a running total for the amount paid on each invoice.  Then replace the {Paid Amt field} in the current balance formula with the running total:

{Invoice Amount field} - {#running total}


 You can also use the running total to adjust the Invoice Amt column to reflect the payments so far (prior to the current record).  You just have to adjust the result so that the Paid Amt from the current record is not included.  Create a formula similar to the following and use it for the Invoice Amt column:

{Invoice Amount field} - {#running total} + {Paid Amt}

 James
0
 

Author Comment

by:Gary Demos
ID: 41741168
James - your suggestions worked beautifully. Now the only issue I am having is that the only invoices that show up on the report are ones that have had payment activity. If I remove all data fields that have to do with payments and only report on amounts that have been invoiced, then all of the invoices show up on the report. Could you show me how to avoid this problem. For example, here is my formula for the Running Total:
{ar_invoice.invoice_amount} - {#RTotalInvoice} + {ar_cash_invoice.cash_amount}

When I enter this formula in the report, it works correctly on invoices that have been paid, but it causes invoices that have no payment activity to disappear from the report. What should I do to this formula to get the unpaid invoice to show up?

Thanks!
0
 
LVL 34

Expert Comment

by:James0628
ID: 41741294
First, just to clarify something -- The invoices with no payments were showing on the report before, when you included a field from the payment table (the paid amount), correct?  The invoices were there, and then disappeared when you added the running total on that field?

 Assuming that the answer is "yes" ...

 I'm guessing that you have a Left Outer Join to the payment table?  Under certain circumstances, when you use a field in an Outer joined table, CR changes the Join to Inner, which would prevent the report from including anything that was not in the joined table.  That sounds like what you're running into.  Assuming that I'm right about you using a Left Outer Join, you could check by changing the report again so that the invoices without payments don't show, and then go to Database > "Show SQL Query" and see if the Join is Left Outer or Inner.

 Assuming that I'm right, the question is, why is this happening?  I don't think simply adding a running total on a field in the payment table should do that (and it didn't in a test that I just did here).  Did you change anything else on the report, especially in the record selection?

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41741654
Are you doing any filtering on the payment table?

mlmcc
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Gary Demos
ID: 41742267
Sorry for the confusion. The missing "unpaid" invoices is a problem when I add any field from the payment table - not just the running total formula. I looked at the SQL statement generated by CR and the joins are all inner joins. Should I manually change them to left outer joins?

I am not doing any filtering on the payments receipts table.
0
 

Author Comment

by:Gary Demos
ID: 41742393
I tried changing the joins to left outer joins and that did not change anything. I've attached a copy of the SQL statement for review.
CR-SQL.txt
0
 

Author Comment

by:Gary Demos
ID: 41742454
I just tried changing the joins to RIGHT OUTER JOIN and now the missing invoices appear, but the invoice amount is blank on the newly appearing invoices. Here is the running total formula:

{ar_invoice.invoice_amount} - {#RTotalInvoice} + {ar_cash_invoice.cash_amount}

I assume there is no amount showing in the report because {ar_cash_invoice.cash_amount} has no value on these invoices.
0
 

Author Comment

by:Gary Demos
ID: 41742475
I think I have it now - I changed the report options to convert DB null values to default and now all of the invoices appear with the prices. Thanks for your help James!
0
 

Author Closing Comment

by:Gary Demos
ID: 41742480
Appreciate the help and informative answers
0
 
LVL 34

Expert Comment

by:James0628
ID: 41743079
You're welcome.  Glad I could help.

 FWIW, I thought about nulls and using that "Convert ..." option, but nulls didn't seem to be a factor in my test (I still got all of the records), so I didn't bring it up.  I'm glad you figured it out.

 James
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now