Creating a Report to get aged customer account balances

Posted on 2006-05-11
Last Modified: 2008-02-01
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.

Question by:dlg
    LVL 7

    Assisted Solution

    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,

    Author Comment

    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.

    LVL 8

    Expert Comment

    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

    Author Comment

    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)


    LVL 8

    Expert Comment

    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  
    LVL 8

    Accepted Solution

    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

    LVL 8

    Expert Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now