Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: aging report Post Reply Post New Topic
Author Message
jgh1204
Newbie
Newbie


Joined: 13 Mar 2008
Location: United States
Online Status: Offline
Posts: 20
Quote jgh1204 Replybullet Topic: aging report
    Posted: 27 Mar 2008 at 12:57pm
I am creating an aging report and the result line will look like this.
 
Name                           Invoice  Amount Current      30           60           >90
joeblow contracting      12345  248.00      0            0.00     248.00       0.00
                                     12346  250.00      0           250.00       0           0
 
Can I write one formula to return the amounts as numbers so that totaling can be done?  Or do I have to write a formula for each bucket?
 
I know I can make it work with the formula returning a string.
 
I would appreciate any help. 
 
BTW, I did buy the book on Amazon.


Edited by jgh1204 - 27 Mar 2008 at 1:03pm
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 27 Mar 2008 at 1:27pm
Hmmm... This would actually be a good question for the book's financial reporting chapter.

I think that you're going to have to create a formula for each bucket and the bucket only has a value if the date falls within that range. For each bucket, I would use the DateDiff() function (see page 257-258) to determine the number of days out and return the Amount value if it matches the date range. Let us know how that works out (or post the your formula here if it doesn't give you what you need).

One other option uses just a single formula for the main logic, but requires writing more formulas that just return a variable for each bucket: Create a single formula that has a Global variable for each bucket. Then use the DateDiff() function with a Select Case statement  (see page 293) to assign the value to the proper Global variable. Then you can create a formula for each Global variable that just returns that variable's value. This keeps all your logic in the first formula and makes it easier to read.

Either formula should work fine.

You can find out more about my books at Amazon.com or reading the Crystal Reports eBooks online.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
jgh1204
Newbie
Newbie


Joined: 13 Mar 2008
Location: United States
Online Status: Offline
Posts: 20
Quote jgh1204 Replybullet Posted: 27 Mar 2008 at 1:35pm
Thanks Brian.  That is what I figured but wanted to make sure I wasn't missing anything.
 
Here is the formula I wrote that returned the values as strings.
 
whilereadingrecords;
numbervar current;
numbervar old;
current:=0;
old:=0;
 
If datediff("d",{@duedate},CurrentDate) < 30
Then current:={AROPNFIL_SQL.amt_1}
Else old:={AROPNFIL_SQL.amt_1};
current&" "&old
 
This just creates 2 buckets, current and old. 
 
I think I will try  your global variable idea.


Edited by jgh1204 - 27 Mar 2008 at 1:37pm
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 27 Mar 2008 at 2:08pm
The formula is fine, the problem is that the very last line you are concatenating the two values together and returning a string. That's why you can't sum them. You need to create a formula for each bucket and only have it return a single numeric value. I'll copy and paste your formula and make a few changes. This should be two separate formulas.
//@FormulaCurrent:
If datediff("d",{@duedate},CurrentDate) < 30
Then
    {AROPNFIL_SQL.amt_1}
Else
    0;


//@FormulaOver30
If datediff("d",{@duedate},CurrentDate) >= 30 AND datediff("d",{@duedate},CurrentDate) < 60 Then
  {AROPNFIL_SQL.amt_1}
Else
  0;

Note: You don't need WhileReadingRecords b/c the formula uses a field in the calculation. You only need this when doing calculations that don't involve table data. (see bottom of page 300)


Edited by BrianBischof - 27 Mar 2008 at 2:09pm
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
jgh1204
Newbie
Newbie


Joined: 13 Mar 2008
Location: United States
Online Status: Offline
Posts: 20
Quote jgh1204 Replybullet Posted: 28 Mar 2008 at 8:03am
Brian, got the case statements and global variables to work great.  I appreciate the help.
 
I ran into a problem displaying the global variables in their respective bucket functions and then realized I had not put the "whilereadingrecords" in the formula.
 
 
 
 
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 28 Mar 2008 at 9:07am
Are you saying that you had to put WhilePrintingRecords in the formulas to make them work? That doesn't make sense. Since they use database fields then this shouldn't be necessary. Can you explain in more detail or am I missing something?
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
jgh1204
Newbie
Newbie


Joined: 13 Mar 2008
Location: United States
Online Status: Offline
Posts: 20
Quote jgh1204 Replybullet Posted: 28 Mar 2008 at 10:49pm
I declared all the global variables called bucket1, bucket2, etc. in the main forumula that calculated all the buckets.  In the bucket formula's, I just declared the variable and have it out put like this.
 
global numbervar bucket1;
bucket1;
 
that did not work, so I tried this.
 
global numbervar bucket1;
numbervar displaybucket1;
displaybucket1:=bucket1;
bucket1:=0//reset bucket to zero;
displaybucket1;
 
It was displaying zeros until I put whilereadingrecords on the 1st line.  I figured since the formula's displaying the variables did not reference database fields, that I needed the whilereadingrecords line. 
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.