How To Use Maximum Formula
Printed From: Crystal Reports Book
Category: General Information
Forum Name: Announcements
Forum Discription: Please check this section for the latest announcements from Crystal Reports Forum
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1847
Printed Date: 03 May 2024 at 3:58am
Topic: How To Use Maximum Formula
Posted By: trupu k
Subject: How To Use Maximum Formula
Date 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
|
Replies:
Posted By: Lugh
Date 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.
|
Posted By: trupu k
Date 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
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
|
Posted By: trupu k
Date Posted: 13 Dec 2007 at 3:56am
Plz Atleast Reply
-------------
tru
|
Posted By: BrianBischof
Date 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>
|
Posted By: Lugh
Date 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.
|
Posted By: trupu k
Date 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
|
Posted By: trupu k
Date 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
|
Posted By: Lugh
Date 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.
|
Posted By: trupu k
Date Posted: 03 Jan 2008 at 8:47pm
|
thanks for reply.
but can we do that using stored procedure?
-------------
tru
|
Posted By: Lugh
Date Posted: 04 Jan 2008 at 4:19am
Sorry, I was responding to your earlier post before.
I honestly don't know whether or not you could join two different stored procedures with different parameters together. I don't see why it would necessarily fail, off hand. But, I expect there would be some weird behavior until you got all the bugs ironed out.
As a better solution, assuming you don't need the two stored procedures to interact very much, is to use subreports. Put each stored procedure in a separate subreport. Link the parameters to parameter fields in the main report. That should do the trick. If you do need to do something like compare totals, you can use shared variables.
|
Posted By: trupu k
Date Posted: 06 Jan 2008 at 2:19pm
i have used cross tab but problem is its display format. i cant add line between two column. in custom format i saw that these lines are there but reports does not show this line.
big prob is
i want "NA" to be displayed if data is null How can i do this, becoz of this prob i used datatype of Curr_rain as string in database but in cross-tab it not giving correct value of any function used(ex. maximum) so i converted it to number but if data is null i want to display "NA" in report.
Plz Help me
Plz reply
-------------
tru
|
Posted By: trupu k
Date Posted: 06 Jan 2008 at 2:27pm
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
i have used cross tab but problem is its display format. i cant add line between two column. in custom format i saw that these lines are there but reports does not show this line.
big prob is
i want "NA" to be displayed if data is null How can i do this, becoz of this prob i used datatype of Curr_rain as string in database but in cross-tab it not giving correct value of any function used(ex. maximum) so i converted it to number but if data is null i want to display "NA" in report.
-------------
tru
|
Posted By: trupu k
Date Posted: 06 Jan 2008 at 2:29pm
but i dont want to show subreports. how can i avoid that
-------------
tru
|
Posted By: Lugh
Date Posted: 07 Jan 2008 at 5:16am
|
Originally posted by trupu k
i have used cross tab but problem is its display format. i cant add line between two column. in custom format i saw that these lines are there but reports does not show this line.
big prob is
i want "NA" to be displayed if data is null How can i do this, becoz of this prob i used datatype of Curr_rain as string in database but in cross-tab it not giving correct value of any function used(ex. maximum) so i converted it to number but if data is null i want to display "NA" in report.
I'm not sure what you mean "add line." Do you mean a blank column? A thicker gridline? There are a lot of ways to customize your cross-tab.
Returning "NA" is going to be very difficult. Crystal is really going to balk at mixing numbers and strings. A much simpler solution, that may work for you, is to have null values default to 0 (the standard action in Crystal), and set the formatting option to display 0 values as "-". It's not quite the same, but it may give you the visual distinction for null values that you are looking for.
|
Posted By: Lugh
Date Posted: 07 Jan 2008 at 5:34am
I think that you are really headed down the wrong path with your stored procedure solution. This will end in tears and frustration. Even if it does eventually work.
Two important questions:
Is your list of stations static enough that you would be willing to change the query if there is a change in the stations?
Do you always want to see all the data for all the stations?
If the answer to both questions is "yes," then there is a way to do a kind of false cross-tab in SQL. However, all of the column headings are hard-coded. So, you have to manually go in and change it if the list of stations changes.
The key to this solution is the CASE function. Your SQL would start something like:
SELECT date1, time1,
(CASE Station_id WHEN "Station1" THEN today_rain ELSE 0 END) AS Station1_Today,
(CASE Station_id WHEN "Station2" THEN today_rain ELSE 0 END) AS Station2_Today,
(CASE Station_id WHEN "Station3" THEN today_rain ELSE 0 END) AS Station3_Today
You can either sum your columns in the SQL statement (generally the preferred method, as otherwise you see a LOT of zeroes), or in Crystal, grouping by your date and time values.
|
Posted By: trupu k
Date Posted: 17 Jan 2008 at 3:32am
|
thank u
i mean i want to insert line after first column.
like
station1 station2
date curr_rain total_rain curr_rain total_rain
suppose above cross tab.
i want line after column 'Curr_rain' in each section of station 1 and 2
-------------
tru
|
|