Announcements
 Crystal Reports Forum : General Information : Announcements
Message Icon Topic: How To Use Maximum Formula Post Reply Post New Topic
Page  of 2 Next >>
Author Message
trupu k
Newbie
Newbie
Avatar

Joined: 26 Oct 2007
Location: India
Online Status: Offline
Posts: 10
Quote trupu k Replybullet Topic: How To Use Maximum Formula
    Posted: 07 Dec 2007 at 3:19am
i m trying to use maximum(fld,condfld,cond) in my project but not able to use that. it is giving  me error as ""There must be group that matches this field""
 
my database is
 
date, time ,current_rain
all these fields are string.
i m saving every 15 minutes data in table. i.e. i will see 96 records for day.
means for 1 day 24 hour for 1hr 4 records each of after 15 minutes.
 
Now i want to display only one record for one day & i will take maximum record for Current rain for the day.
 
i.e.
 
date           current_rain
1/1/2007    maximum(Current_rain,date,"daily")
 
i types this function in one formula but it is giving error as  " ""There must be group that matches this field"""
 
Which condition will i use for getting only one record for date Value from table.
 
Please Contact me if you r having doubt!!!!
 
tru
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 07 Dec 2007 at 5:53am
OK, first of all, please tell me you did not actually name your fields "date" and "time"!  Those are restricted keywords in pretty much every language.  Using them will confuse SQL, Crystal, and pretty much anything else.

Second, why are you storing your date and time values as strings?  That's going to create additional overhead, and additional room for error (as there's nothing preventing someone from putting an "A" in the middle of the date, or messing up the format).

Now, given that, you have a couple options.  With your current setup, you actually don't need the "daily" condition.  Each unique value of the "date" field will be a separate day.  So, you can simply group by "date."  Your formula should look like:
Maximum ({Current_Rain},{date})

(The curly brackets are necessary to tell Crystal that you're talking about fields.)

Second, if you want to more sophisticated work with the dates, you'll need to convert them to datetime values.  My suggestion is to concatenate your "date" and "time" values, and then do a conversion.  The formula would look like:

CDateTime({date} & " " & {time})

(Naturally, in the finished product, you probably want to put some validation in there with IsNull, IsDate, and IsTime.)

Once you have that, you can do grouping on the formula by hour, week, month, etc.


IP IP Logged
trupu k
Newbie
Newbie
Avatar

Joined: 26 Oct 2007
Location: India
Online Status: Offline
Posts: 10
Quote trupu k Replybullet Posted: 11 Dec 2007 at 3:44am
Thanks for reply
sorry i write wrong stmt.
the 'date' field is datetime only. & field name is date1,and time1 is another field
 
database is as (sql server2005)
 
date1  datetime
time1   datetime
curr_rain  string
 
out of which i want maximum of curr_rain for day And i want to show this in monthly report. i.e. report will include only 31 records 1 record for one day & that will be max(day) for curr_rain
 
 
Please tell me if my database contain 96 records for one day how can i  show only one record i.e. max(curr_rain) in report
 
which selection criteria wud i give to retrive only one date1 of one day.
means
 
date1                       date1  time1
1/1/07 out of 1/1/07 00:00
                      1/1/07 00:15
                      1/1/07 00:30
                      1/1/07 00:45
                      1/1/07 01:00
                      1/1/07 01:15
                            till
                      1/1/07 23:45
 
 
if i select date1 from database and mount that on report in detail section it will show 96 times same date
 
 
& Please help for this also summation
i m having 4 record for 1 hour, each of after 15minute
i want to show hourly report as
date          time    curr_rain
1/1/07     00:00    curr_rain at 00:00
1/1/07     01:00    sum(curr_rain at 00:00,00:15,00:30,00:45)
 
same for till end date
 
 
tru
IP IP Logged
trupu k
Newbie
Newbie
Avatar

Joined: 26 Oct 2007
Location: India
Online Status: Offline
Posts: 10
Quote trupu k Replybullet Posted: 13 Dec 2007 at 3:56am
Plz Atleast Reply
tru
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2460
Quote BrianBischof Replybullet Posted: 13 Dec 2007 at 7:56am
Is the problem that since the date and time are in two different fields, then when you join the two tables together then the data in the second table gets repeated 96 times because the date is listed 96 times in the first table. If so, you really need to have a second field to join the tables by to prevent the duplication. However, I assume that the reason you are asking this question is because you can only join on the date field. So an option would be to have the report group on the date field and only print the group header on the report. That way, the 96 detail records for that date will be skipped because only one record can be printed in the group header at a time.

Re your second question. You can have a running total field the sums the data in the Details section and and increases the total with each record printed.
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
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 14 Dec 2007 at 4:56am
Sorry for taking so long to respond.  I've been on vacation.

OK, if date1 and time1 are both datetime fields, why do you have both of them?

I really think that all you want to do is group on the time1 field, and set your group options to group by day.  In the group header, put date1 and create a summary field for Max of curr_rain.  Then, suppress your details section.  Unless I'm really misunderstanding what you want, it should be that simple.


IP IP Logged
trupu k
Newbie
Newbie
Avatar

Joined: 26 Oct 2007
Location: India
Online Status: Offline
Posts: 10
Quote trupu k Replybullet Posted: 29 Dec 2007 at 3:16am

thanks to reply.
i got upto this.
but now my bos want this report for many station included in one report.

if my db contains fields as
project_id char(5)
Station_id char(5)
date1      datetime
time1      datetime
today_rain int
total rain int

now i want to show rep as

            station1    Station2  station3
date time today total today total today totl


like this i want to data of each station in vertical format. there can be max 20 stations.


i thought to group by stationID
but this shows data as

station1
station2
station3

means in row format after completing data of one station, below that data of 2nd station


i used command1 as
select * from table where station_id=station1
& command2 as
select * from table where station_id=station2

& added this to crystal report but my data is huge i want to show 3000 record of one station at a time.
like this 3000 * 20(each 20 station data at a time using 20 commands)

this take so much time so i rejected this idea

now i used stored procedure.
in stored procedure i passed one parameter station_id so that i can use same stored proc for 20 station.

but when i add one stored proc to report it is ok
but when i add same proc sacond time  then it ask for creating alias for previous one,i told yes.

but when stored proc ask for parameter it take same parameter to both alias & original but i want 2 separate parameter as Station1 & station2 for both .

i want to use same stored proc for 20 station using diff parameter.
Plz help me for this,
plz told me if u have another solution
thanks


 

tru
IP IP Logged
trupu k
Newbie
Newbie
Avatar

Joined: 26 Oct 2007
Location: India
Online Status: Offline
Posts: 10
Quote trupu k Replybullet Posted: 02 Jan 2008 at 2:53am
plz reply
 
How to use same Stored proc as alias in same report with different value of parameter?
 
mean
one value of parameter to 1st stored proc
2nd valus of parameter to  2nd alias stored proc
tru
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 02 Jan 2008 at 7:04am
Try looking into a cross-tab.  That will give you what you want.  In fact, this scenario is exactly what cross-tabs are designed to handle.


IP IP Logged
trupu k
Newbie
Newbie
Avatar

Joined: 26 Oct 2007
Location: India
Online Status: Offline
Posts: 10
Quote trupu k Replybullet Posted: 03 Jan 2008 at 8:47pm
thanks for reply.
but can we do that using stored procedure?
tru
IP IP Logged
Page  of 2 Next >>
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.029 seconds.