Can I get the min and max within one month?

[neronikita]

Good morning!

I'm not sure if this is the best place to post this or not, but I'm using Crystal Reports 2008 with Sql server 2008 express, so I thought I would start here.

I have a database that is tracking fuel mpg, idle time, trip time, mileage, etc. This information is pulled from our trucks using a laptop connection. Some of the newer trucks calculate more than others, meaning I have to do the manual calculations for older trucks.

What I need to do is create averages by month of the mpg and idle time. For the older trucks to get the idle time I need to subtract the lowest mileage for the month from the highest for that month. Same with the trip time. Then I need to divide the trip mileage by the trip time. I have figured out the formulas, but it's doing it for all the entries, not just the month. I also need it by truck number.

My first question is, should I be doing something in a stored procedure before doing anything in crystal, or is there a way to do all of this in crystal?

Please let me know if I haven't explained clearly or if you need a list of all the fields or have any other questions.

Thanks in advance,

Di

 

[crystalkiwibruce]

Firstly you need to group your data by the date field/monthly.

Then your summary is
Sum({table.field},{Table.DateGroupField},"monthly")

Similar syntax for Average

 

[neronikita]

Thanks for your response. The formula I was using was this:

Maximum({idlempg.triptime}) - Minimum ({idlempg.triptime})

Can I somehow use the monthly feature with that?

Thanks,

Di

 

[lbass]

Your formula is only checking the maximum/minimum per the entire report. You need to add in the group condition and the interval in both max and min parts of the formula as crystalkiwibruce showed.

-LB

 

[neronikita]

I tried that and I got "A Group Condition is not allowed here". Now when I went to do the grouping, it let me do specified order, but not monthly specifically. Is that the problem?

Maximum({idlempg.triptime},{idlempg.Date}, 'monthly') - Minimum ({idlempg.triptime},{idlempg.Date}, 'monthly')

Thanks,

Di

 

[lbass]

I've never seen that error--are you sure that is what it said? You have to have inserted a group on the date and in the group screen, there is an option to print on change of: month. If you are not seeing that, then I'm guessing your field is a string, not a date. Can you confirm by browsing the field (right click on it->browse field)?

-LB

 

[neronikita]

Good catch - in the sql database, the data type is Date but in crystal, it says string 10.

 

[lbass]

Please show how it displays and also show whether you mean MM/dd/yyyy or what the format is.

-LB

 

[neronikita]

It displays 3/4/2011. So I created a formula called dateconvert and converted it from a string to a date, then I used that in my calculations and as my group. That seems to be working.

Thanks for all your help!

Di