Need Help
Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=4055
Printed Date: 19 May 2024 at 5:37am
Topic: Need Help
Posted By: Tejaswini
Subject: Need Help
Date Posted: 21 Aug 2008 at 5:44am
|
I am using a database view for designing crystal reports. In my report I want to find the sum of a particular field say "x" for every hour in the selected duration(start date till end date). The summary function in crystal reports will provide the results for selected option say "Sum" but it will give the sum of "x" which we get every 5 mins (collection interval). In our report we want to add the values for every hour and not 5 mins i.e. if we consider the start date as "7/14/2008 12:00:00 AM" then we want to add the value of parameter "x" at "7/14/2000 1:00:00 AM", "7/14/2000 2:00:00 AM", "7/14/2000 3:00:00 AM", and so on till the end date. But the database has values for every 5 mins interval i.e. "7/14/2000 1:00:00 AM", "7/14/2000 1:05:00 AM", "7/14/2000 1:10:00 AM", and so on. We want to skip the values in between and add only the hourly value. How this can be achieved. Any help or guidance is appreciated. ------------- Tejaswini |
Replies:
Posted By: rajacm
Date Posted: 21 Aug 2008 at 11:02pm
|
hi, In RecordSelectionFormula use the DateAdd function to set the interval of one hour. This should solve ur problem.
-Raja
|
Posted By: Tejaswini
Date Posted: 22 Aug 2008 at 6:38am
|
Hello Raja,
Thanks for your reply.
Tried this option but it didn't work
 Regards,
Tejaswini ------------- Tejaswini |
Posted By: rajacm
Date Posted: 25 Aug 2008 at 12:58am
|
Another way is, in RecordSelectionFormula add a condition
like
<time part of filed> in ('00:00:00 AM', '1:00:00 AM', '2:00:00 AM' ... '11:00:00 PM' )
this is little bit tedious. try once.
-raja
|
Posted By: Tejaswini
Date Posted: 26 Aug 2008 at 5:25am
|
Hi Raja,
Not working :(
I am trying running total fields option In this I have given conditions as follows: Field to summarize: <parameter 'x' whose addition needs to be done> Type of summary: sum Evaluate Reset I guess this should give me the expected results but somewhere it is still failing. Need advice on this. Thanks. ------------- Tejaswini |
Posted By: rajacm
Date Posted: 27 Aug 2008 at 3:24am
|
Hi Tejaswini, U tried well but, i am sorry it may not work out it seems.U are left with the option to use recordselectionFormula as given below.
Minute ({CATS.DATE_ENTERED}) = 00
then use the Summary field to summary of another field which u requied.
remember that if u use this u need to remove the grouping in CR.
it worked for me...hope it hleps u.
-Raja.
|
Posted By: Tejaswini
Date Posted: 27 Aug 2008 at 4:05am
|
Hello Raja,
The problem here is my database has values for every 5 mins but it is not always a round figure i.e. 1:00:00 AM or so. Database has values depending on when the user has added that added i.e. it can be 1:01:00 AM or 1:03:00 AM.
It is not the case that the minutes field will be "0" always. It can vary.
Due to this constraint i cannot use the record selection formula that u have mentioned.
My DB has timestamp values as mentioned below:
8/21/2008 12:53:25 PM
8/21/2008 12:58:25 PM
8/21/2008 01:04:25 PM
.
.
.
so on
Somehow I need to get the hourly value and sum it up
 Thanks,
Tejaswini. ------------- Tejaswini |
Posted By: rajacm
Date Posted: 27 Aug 2008 at 4:23am
|
hi http://www.crystalreportsbook.com/forum/member_profile.asp?PF=5204 - Tejaswini ,
In that case also we may not get the exact hourly totals....
can u take hour's first record. for totaling...
-raja
|
Posted By: Tejaswini
Date Posted: 27 Aug 2008 at 5:45am
|
In this case I need to take the hour's last value i.e. 12:58:25 AM then 1:58:25 AM, 2:58:25 AM.... so on till the last date/time and then sum them up.
To achieve the above mentioned summation logic I tried using "Running totals Field" option but I am not getting the expected values. For this I have done grouping on timestamp for each hour (which picks up the last value of the hour). somehwre my running totals is failing :(
As u said "can we take the hour's first record for totalling", by taking the first value of hour how can we get the expected result?
Thanks. ------------- Tejaswini |
Posted By: rajacm
Date Posted: 28 Aug 2008 at 5:49am
|
hi Tejaswini,
In that i think we need to create a function in database which can get the last record of the first hour of the start date and compares the current record <datetime field > with the only time part . and if it matches it returns true else false
Use this funtion to create new column in ur view.
then based on the bool column u can sum the records..
!!One more option!!
-Raja
|
Posted By: Tejaswini
Date Posted: 03 Sep 2008 at 11:41pm
|
Hi Raja, Using formula field I could resolve my problem :) Thanks a lot for your help. Just FYI, initially when I tried formula filed I was just using that formula field in GroupFooter where I needed that value. But when I used it in GroupFooter as well as Details section (suppressed Details section), I am getting the expected values.
Thanks again for your help..... ------------- Tejaswini |