[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

Aging Report

How do I create an aging report in V8?
0
rtstannard
Asked:
rtstannard
  • 3
1 Solution
 
rene_moellerCommented:
Could you be a bit more specific?
0
 
tomookCommented:
The general technique is to add a calculated field to a query which determines the "bucket" for each record, then run GROUP BY on that field. To make this simple, you can use two queries, one to calculate the bucket and a second to group.

For example, given the tables and fields:
Customers
   CustID
   CustName
Invoices
   InvID
   CustID
   AmountDue
   Age

Make Query1:
SELECT InvID, CustID, AmountDue, Age,
IIF(Age <= 30, "Current", IIF((Age > 30) And (Age <= 60) , "30-60", "Over 60") As AgingBucket
FROM Invoices;

And Query2:
SELECT CustID, Sum(AmountDue) As BucketTotal, AgingBucket
FROM Query1
GROUP BY CustID, AgingBucket;

0
 
tomookCommented:
I should note that you can structure things a little differently, which helps you print standard invoices or statements.

Query1:
SELECT InvID, CustID, AmountDue,
  IIf(Age<=30, AmountDue, 0.0) As BucketCurrent,
  IIf((Age>30) And (Age<=60), AmountDue, 0.0) As Bucket30,
  IIf((Age>60) And (Age<=90), AmountDue, 0.0) As Bucket60,
  IIf(Age>90, AmountDue, 0.0) As BucketOver90
FROM Invoices;

Query2:
SELECT CustID,
  Sum(BucketCurrent) As BucketCurrentTotal,
  Sum(Bucket30) As Bucket30Total,
  Sum(Bucket60) As Bucket60Total,
  Sum(BucketOver90) As BucketOver90Total
FROM Query1
GROUP BY CustID;

Query2 will show you one record per customer, which makes certain reports easier to write.
0
 
rtstannardAuthor Commented:
tomook:  Thanks for a full, understandable, and syntactically correct answer.  Good work!


0
 
tomookCommented:
I am glad there were no syntax errors as I just typed it in cold. Thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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