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

Crystal Reports

Hi,

I have a table filled with accounting bills, I'd like to create a crystal report that groups these bills by their date difference from today, kind of like an aging accounts receivable.

Such as :

0-30 days:
Bill 1
Bill 2
Bill 3

30-60 days:
Bill 4
Bill 5
Bill 6

60-90 days:
Bill 7
Bill 8
Bill 9

I'm wondering if this is at all possible?

Thank you
0
FCapo
Asked:
FCapo
1 Solution
 
Atdhe NuhiuCommented:
The way I would do it is to create a view in SQL like

CREATE VIEW MyView
AS

SELECT    
Field1,
... ,
Fieldn,

case
  when datediff(day,datefield,getdate()) < 30 then 1
  when datediff(day,datefield,getdate()) < 60 then 2
  when datediff(day,datefield,getdate()) < 90 then 3
  else 4
end as dategroup
FROM         MyTable

Then base your report on this view and group on 'dategroup' and name each one appropriately.

HTH
0
 
peter57rCommented:
If you are not allowed to mess with the databse then in CR you can use the same idea.

Create a formula field ...@AgeGroup

Numbervar diff:=Currentdate-{table.datefield};
If diff <=30 then
"0-30 Days"
else
If diff <=60 then
"31-60 days"
else
If diff <=90 then
"61-90 days"
else
"91days and over"

Then group on this formula field.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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