• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

Creating a Report to get aged customer account balances

I am trying to create a customer balance report which would show me aged customer balances using the following aging periods :-
30 Days
60 days
90 Days+

My tables have the follg structure

Invoice Table ( holds all transactions entered for a customer - Invoice, Credits & Payments)

      Accode ( customer Code)
      InvTotal ( Credits and Payments are shown as negative)
      InvType ( 1 if it is an Invoice, 3 if it is a credit note, 6 if it is a payment)
      Noalloc ( This shows the actual amount allocated in total for a particular payment or credit note
                sometimes a payment or credit is not completely allocated for ex when a customer has overpaid)

Alloc Table  ( this holds the allocation details of payments or credit notes against an Invoice)

        InvNo ( same as InvNo in Invoice Table)
        AllocNo ( the Invoice no against which the payments is allocated)
        Amount ( the amount of this allocation)

So lets say a customer has 2 invoices InvNo 100  - $500
and Inv No 101 - $1000 and they have paid $1250 which has InvNo -P1 ( as it is a payment)
The entries in the tables would be as follows:-

Invoice Table
       Acccode            InvNo      InvDate            InvTotal      InvType            Noalloc
      cust1            100      Date1            $1000            1            
      cust1            101      Date2            $500            1      
      cust1            P1      Date3                      - $1250            6               $1250

Alloc Table
      InvNo      AllocNo            Amount
      P1      100            -$1000
      P1      101            -$250

I joined the two tables using a left outer join from Invoice.InvNo to Alloc.AllocNo to capture entries in
Invoice table which have no allocations. These would be Invoices which have not been paid at all.

I tried to group the report first on Accode and then on Inv no to get the amount owing on each Invoice.
The problem I run into is that I cannot get calculated summary fields to work when the Invoices have no entries in the Alloc table.

I would appreciated if anybody can setup the report for me which would give me the oustanding balances by aging as well as list all unallocated transactions which would basically add upto the customers outstanding balance.

  • 4
  • 2
2 Solutions
You could replace the Noalloc field in your report with a formula to replace null fields with a zero:

if not isnull({YourTable.Noalloc}) then

The summarize on the NoAllocAmt field.

Or you could modify your SQL to replace nulls with zeros:

ISNULL(YourTable.NoAlloc, 0) AS NoAllocAmt,
dlgAuthor Commented:
I followed your suggestion of testing for IsNull and it worked but the problem I was having with this issue was with the
Amount field in the Alloc table, when there were entries in the Invoice table which did not have a matching entry in the Alloc table. I was summarising the Amount field in the group foooter where the records were grouped on InvNo. When the Invoice no had no entry in the Alloc table it would not create a summary field and so I could not get a calculated field in the group footer to work. Now by chekcing for ISnull I am giving it a value of 0 if isNUll and am bale to get balances correctly.

However I am unable to goto the next step which is...
In my group footer (grouped on Inv No) I have the following fields
InvNo InvTotal    sum of Inv.Noalloc    sum of Alloc.amount   balance
where balance is a calculated field ( InvTotal + sum ofInv.noalloc + sum of Alloc.Amount)
This gives me a balance owing on each transaction. So if an Invoice is fully paid Balance is 0, if partly paid it shows the amount not paid, if a payment is fully allocated the balnec is 0, if a payment is partly allocated the balnce shows teh unallocated amount.

Now I would like to get a sum of the balance field for all transaction( invoices & payments) for a customer.
But am unable to create a summary field in another group footer (group footer 1) for the balance field( which is a calculated field in group footer 2)

Perhaps there is an easier way to do all this.

it looks like the first thing that you need to do (because of your table structure) is to change your data source to the a SQL union query, which will then allow you to "stack" the interrelated data and thus allow you to then group it, etc... Assuming that you are using a more recent version of Crystal, either create a new copy of the report or change your data source to use the "cmd" rather than the tables, and then create a query that looks something like:

SELECT "Inv" as Source, InvoiceTable.Accode, InvoiceTable.InvNo as Ref, InvoiceTable.InvDate, InvoiceTable.InvType, InvoiceTable.Noalloc, InvoiceTable.InvTotal
FROM InvoiceTable
WHERE (((InvoiceTable.InvType)<>6));
union all
SELECT "Pmt" as Source,AllocTable.InvNo as Accode, AllocNo as Ref, InvoiceTable.Invdate as InvDate,6 as InvType, 0 as Noalloc,Amount as InvTotal
FROM AllocTable  inner join InvoiceTable on AllocTable.InvNo = invoiceTable.InvNo

what this does is to then persent the resulting data in a manner that looks like:
Source      Accode      Ref      InvDate InvType Noalloc InvTotal
Inv      Cust1      100      5/11/2006      1      0 1000
Inv      cust1      101      5/12/2006      1      0  500
Pmt       P1      100      5/15/2006      6      0  -1000
Pmt       P1      101      5/15/2006      6      0   -250

Once you have the data looking like this, you can now create a group using the "InvNo" and then also insert a subtotal for the "InvTotal" field.

After you get the intial groupings/subtotals working such that the correct payments are applied against the corresponding invoices, then you can begin to work on the agings...

Crystal does have some built-in Aging functions that looks like they will be just fine for you... (See for example "Aged0to30Days" under the Date Range type of functions, and the help files are very good...)
so that you can now create invoices for the respective columns for each "InvTotal" that look something like:

formula 1/column 1 =
If {cmd.InvDate} in Aged0To30Days Then
Else  0
formula 2/comlum 2 =
If {cmd.InvDate} in Aged31To60Days Then
Else  0

and so on...

The only somewhat "tricky' thing for your aging would be for partially paid invoices since the Invoice will Age based upon the Invoice date, and the related payment will age based upon the payment posting date...  Since this is the way many aging reports work anyway, hopefully this will not present an issue for you... (if you need to first net the payment, and then only age the open balance of the invoice, the SQL query and all can become much more complex.

so, give this a try and let us know if this is what you're looking for.
Best wishes, Pat K
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

dlgAuthor Commented:
Hi Pat
have changed my datasource to be an sql query as you suggested. My sql query is below:

select "INV" as Source,  `acccode`,`invoiceno` as ref , `date` , `itype`,`fnoalloc`,  `invtot` as total
from invoice  
union all
 SELECT "PMT" as Source,`invoice`.`acccode`, `alloc_2`.`alloc_no` as ref, `invoice`.`date`, `invoice`.`itype`, `invoice`.`fnoalloc`, `ALLOC_2`.`famount` as total
 FROM   `invoice` `invoice` INNER JOIN `alloc_2` `alloc_2` ON `invoice`.`invoiceno`=`alloc_2`.`invoiceno`
 WHERE  `invoice`.`itype`<>'1'

This works fine. I have created 2 groups : Group 1 for acccode and Group 2 for Invoiceno.
I get the following output for one customer :

source     acccode    ref    date     itype    fnalloc    total


inv         inter        inv0001  1/1/06  1      0.00       298.28
pmt         inter       inv0001  1/2/06  6     298.28   -298.28


inv         inter        inv0002  15/1/06  1      0.00      245.22
pmt         inter       inv0002  1/3/06   6     245.22   -245.22


inv         inter        inv0003  21/1/06  1      0.00       17.08
pmt         inter       inv0003  1/3/06   6     17.08      -17.08


inv         inter       p0001  1/2/06      6     298.28   -676.60


inv         inter       p0001  1/3/06      6     262.30    -262.30

Total owing                                                       -938.90

In the above all Invoices have their payments grouped together and the balances show correctly.
The only problem happens when a payment is overpaid or partially allocated, as in the line showing details of payment p0001. This payment was against inv001, leaving a balance of $378.30 in credit for the customer.
If i did a total of all the invtot fields I will get a balance of $-938.90 when it should be -$378.30.
Total of all the invtota fields give a correct balance when there are no overpayments or unallocated payments as i have to exclude all entries from the 1st part of the sql union where itype <>1 ( as all invoices are itype =1 anything else is a payment or a credit whihc is being picked up in the 2nd part of the sql union)


Am I correct in thinking that it's the "noAlloc" field within the invoice table that contains any "unallocated" payment? (which it looks like in your example is the <262.30> from p001)

If so, my questionto be able to help you next modify the query, etc would be: within the Invoice table, if the transaction type is "6" (for a payment,") am I correct in thinking that we want to only pull in the amount in the "noAlloc" field?  and the reference/date would be based upon the payment number?

if this is the situation, try modifying the first portion of the query to set a "condition" as to which amount to use for the trans type, etc, something more like:

select "INV" as Source,  `acccode`,`invoiceno` as ref , `date` , `itype`,`fnoalloc`,  
case InvType

`invtot` as total
from invoice  
sorry - didn't get to finish - so let's try this again:

select "INV" as Source,  `acccode`,`invoiceno` as ref , `date` , `itype`,`fnoalloc`,  
case InvType
     when 6 then -1 * NoAlloc
     else `invtot`
end as total
from invoice  

what you can see that this is doing is substituting which field is being used, and possibly converting it to a negative if necessary?
if this woks, you may want a similar 'case statement' for the source (which is not required, but is recommended to tell you where the data came from in case you need to "track back" to the raw tables/data) to be something like:
Case InvType
  when 6 then "NoAlloc"
  else "INV"
end as Source

sounds like you're really close!! best wishes :) Pat

doesn't look like this one should be split... based on author's comment early on, it looks like janmarini's sugggestion did not work for the situation.. thanks Pat K

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now