| Author |
Message |
AMMAR
Newbie
Joined: 29 Oct 2008
Online Status: Offline
Posts: 11
|
   Topic: show last month closing as this monthopening entry
Posted: 04 May 2009 at 5:58am |
hello
i am still new to crystal, please could anyone help with the following.
i need to write a diesel consumption report, my report is broken up into groups and each group displays data on a specific vehicle registration. it looks as follows
reg - mileage - previous mileage - date
NCL020GP 10000 0 11/02/2009
NCL020GP 10500 10000 15/02/2009
NCL020GP 11000 10500 18/02/2009
this report is run between 2 dates using the previous() function to get the previous mileage reading. it all runs fine except that the first previous entry display a 0, instead of bringing in the entery from the previous month.
is there any formula i can use to bring in the previous months final entry as an opening entry for the current month?
my formula currently looks as follows
If Previous ({DIESELOIL.FLEET_NUM}) = GroupName ({DIESELOIL.FLEET_NUM}) Then
Previous ({DIESELOIL.ODOMETER})
Else
0;
i need to put something instead of the "0" to display last months closing figure
your help is greatly appreciated
Ammar
|
 IP Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 04 May 2009 at 6:36am |
you will need to get the data from the database. The first record, won't know it's previous if you don't tell it what it is.
|
|
IP Logged |
|
AMMAR
Newbie
Joined: 29 Oct 2008
Online Status: Offline
Posts: 11
|
Posted: 04 May 2009 at 6:44am |
thank you for your reply, that is exactly what i need help with, i am aware that i will have to derive it from the database but have no idea how to do so. if possible can you ellaborate on this.
thank you
|
|
IP Logged |
|
JohnT
Groupie
Joined: 20 Jan 2008
Online Status: Offline
Posts: 92
|
Posted: 04 May 2009 at 7:34am |
Does your selection criteria contain dates parameters ? I would guess it does because you said the report is run between two dates. You don't have the previous record because it is before your begin date. If you know how far back it is, you can change the dates so it gets included. If you don't, you are going to have to pick up a lot of data and exclude what you don't want.
As an example:
You are running your report for Jun 1 to July 1. For one vehicle, the previous record is Apr 15. For another vehicle, the previous record is May 10. In this case, you need to go back to Apr 15 to get everything you need. The tricky part is knowing how far to go back.
What should happen if there isn't a previous record ? In the example you gave, it seems possible that it is a new vehicle and the start point really is zero.
Edited by JohnT - 04 May 2009 at 7:55am
|
|
IP Logged |
|
AMMAR
Newbie
Joined: 29 Oct 2008
Online Status: Offline
Posts: 11
|
Posted: 04 May 2009 at 7:39am |
than you JohnT,
our vehicles fillup almost once every 2 days, so going back 1 week would cover it, but now they might fill twice durin those past seven days so i need to be able to select the entry for the highest mileage in the previous month. and if possible would you be able to put me in the right direction as to what function or formula can be used in order to retrieve that records 1 week before the from date
thank you
|
|
IP Logged |
|
JohnT
Groupie
Joined: 20 Jan 2008
Online Status: Offline
Posts: 92
|
Posted: 04 May 2009 at 12:02pm |
You probably have 2 date parameters. Something like begin date and end date. Your selection probably has something like:
vehicle date >= begin date and vehicle date <= end date. You might try changing your selection to say vehicle date >= dateadd("d",-7,begin date) and vehicle date < = end date.
That should get your records from 1 week prior.
From here, I think I would try to put in a suppression formula on the detail line to print where the vehicle date >= begin date and vehicle date <= end date. That should give you the lines you want.
Give that a try and let me know what you get.
Edited by JohnT - 04 May 2009 at 12:03pm
|
|
IP Logged |
|
AMMAR
Newbie
Joined: 29 Oct 2008
Online Status: Offline
Posts: 11
|
Posted: 04 May 2009 at 11:35pm |
i cannot thank you enough for this.. it works perfectly, really appreciate your time.
thank you
Ammar
|
|
IP Logged |
|
AMMAR
Newbie
Joined: 29 Oct 2008
Online Status: Offline
Posts: 11
|
Posted: 06 May 2009 at 2:43am |
Helo JohnT
i have encountered a new problem as a result of supressing the fields with an earlier date then start date. i need to sum up some of the fields and my problem now is that the supressed records are also being summed up along with the current records.. is there a way to just sum up the records that appear between the 2 dates?
thanx again for your help..
Ammar
|
|
IP Logged |
|
AMMAR
Newbie
Joined: 29 Oct 2008
Online Status: Offline
Posts: 11
|
Posted: 06 May 2009 at 2:43am |
Helo JohnT
i have encountered a new problem as a result of supressing the fields with an earlier date then start date. i need to sum up some of the fields and my problem now is that the supressed records are also being summed up along with the current records.. is there a way to just sum up the records that appear between the 2 dates?
thanx again for your help..
Ammar
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 06 May 2009 at 6:43am |
Yes, but it can become complex.
It starts off really simple. Set shared variable to 0 when the group you want to sum starts. In each detail record, if the record is 'valid', increment the shared variable by the correct amount. In the group footer, display the shared variable.
Simple. Where it gets complex, is that you can not do any aggregate functions on the shared variable, so if you want to sum all of the sums (a second grouping that is larger) you need to increment a second shared variable...and so on for all the larger groups that you want to track this value through, and remembering to reset the counters as appropriate.
Searching for shared variables should return plenty of hits as I have responded to any number of posts...I use them a lot in my reports, usually for this very reason.
Hope this helps
|
|
IP Logged |
|
|
|
