Technical Questions: show last month closing as this monthopening entry

Print Page | Close Window

show last month closing as this monthopening entry

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6246
Printed Date: 04 May 2024 at 8:03pm

Topic: show last month closing as this monthopening entry

Posted By: AMMAR
Subject: show last month closing as this monthopening entry
Date 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

Replies:

Posted By: lockwelle
Date 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.

Posted By: AMMAR
Date 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

Posted By: JohnT
Date 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.

Posted By: AMMAR
Date 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

Posted By: JohnT
Date 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.

Posted By: AMMAR
Date Posted: 04 May 2009 at 11:35pm

i cannot thank you enough for this.. it works perfectly, really appreciate your time.

thank you

Ammar

Posted By: AMMAR
Date 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

Posted By: AMMAR
Date Posted: 06 May 2009 at 2:43am

Helo JohnT

thanx again for your help..

Ammar

Posted By: lockwelle
Date 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

Posted By: AMMAR
Date Posted: 06 May 2009 at 6:50am

thank you for this, i got this far i am able to reset all the supressed reords to 0 which sorts out my sum issue but the problem i get now is that the result from this colum is used in a division to give me an average and i get a "cannot divide by 0 " error.

Posted By: lockwelle
Date Posted: 06 May 2009 at 7:27am

before the average, check if the variable =0, if it is print an appropriate message or value (has to be the same as the average, so either the average becomes a string or the appropriate value is 0 (probably))

If the average is not in a formula, make a formula for it like:

if sum({table.field}, {group}) = 0 then

else

someNumber / sum({table.field},{group})

Print Page | Close Window