Suppress and sum the time different 1

[lalakeyorai]

Hi all,

Can somebody help me.... I'm using CR 2008. i have calculated the time different between 'Active' and 'Clear' <Status> for each <Event> by using timediff function and the time different is in string format. Here is my data,


<Time> <Event> <Status> <timediff>
12 Oct 2009 5:08:03 AM Unresponsive Clear
-71:-42:-52
12 Oct 2009 5:57:06 AM Unresponsive Active
00:00:00
12 Oct 2009 7:41:31 AM Unresponsive Clear
01:44:25
12 Oct 2009 7:45:32 AM Down Active
00:00:00
12 Oct 2009 1:10:29 AM Down Clear
05:24:57
12 Oct 2009 1:14:30 AM Unresponsive Active
00:00:00
12 Oct 2009 3:10:56 AM Unresponsive Clear
01:56:26


my problem is:

1. How can i suppress the 00:00:00 so the it will not display on my record (to hide the time only)? How to suppress it?

2. If the first data of the group started with 'Active' followed by 'Clear', then it will be a problem on displaying the time different. But if the data started with 'Clear', it show negative number. The 'Clear' data appeared first was due to the date & time selection in my report. the 'Active' data maybe on the previous date or time which are not include during date & time selection.is it possible for me to make the report appear 'Active' first then followed by 'Clear' status. if 'Clear' come first, i want it suppress the whole line of data.

3. since the time different format is in string format, how can i sum the time different? here's my syntax for time different:

diff:=datediff ('s',down,previous ({@time}))//@time was generated from dateadd function.

hrs:=...
min:=...
sec:=...

totext(hrs,"00")+":"+totext(min,"00")+":"+totext(sec,"00")


Thanks in advance.

 

[CoSpringsGuy]

right click the field and select format field. select common tab. click the X-2 next to supress and use

//
{@timediff} = time(0,0,0)

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

I don't understand why your datetimes are not sorted--this would seem critical if you are comparing records based on a sequence, so maybe you have some undisplayed field that is being used for the sort.

1 and 2) Use a suppression formula like this for the timediff formula:

{table.status} = "Active" or
(
{table.status} = "Clear" and
(
onfirstrecord or
{table.groupfield} <> previous({table.groupfield})
)
)

3- Use a variable to collect the datediff results BEFORE conversion to a string:

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar secs := secs + (
if {table.status} = "Clear" and
not onfirstrecord and
{table.groupfield} = previous({table.groupfield}) then
datediff ('s',{@time},previous({@time})); //not sure what "down" references

//{@display} to be placed in the group footer:
whileprintingrecords;
numbervar secs;
(Then build the conversion into this formula).

//{@reset} to be placed in the group header:
whileprintingrecords;
numbervar secs;
if not inrepeatedgroupheader then
secs := 0;

-LB

 

[lalakeyorai]

CoSpringsGuy,
Thank you so much for your reply. I did try the syntax but i get error saying that 'A string is required here'. this is because my {@timediff} is string format.

LB,
Thank you so much for your reply. Your suggestion work successfully..!!

I'm so sorry, that was my typing error. the data is actually 1:10:29 PM, 1:14:30 PM and 3:10:56 PM. 'down' in the syntax is to indicate time different calculation for Event=down.

down:=if {Event}='down' and {Status}='Clear' then {@Time};

FYI, there were another syntax for Event=unresponsive. I have another enquire. There were also sometimes the data appear not in 'Active' then 'Clear'. It may appear like these example:

12 Oct 2009 5:08:03 AM Unresponsive Active
00:49:03
12 Oct 2009 5:57:06 AM Down Active
00:00:00
12 Oct 2009 7:41:31 AM Unresponsive Clear
00:04:01
12 Oct 2009 7:45:32 AM Down Clear
[/color blue]

where by right i should get timediff=02:33:28 for Event=Down while timediff=01:48:26 for Event=Unresponsive. how can i fix this? Sorry if this is not a good question, i am really slow when it comes to syntax writing.

Sorry if i'm taking your time too long. Thanks in advance

 

[lbass]

I can't tell what you are comparing here or why--how can YOU tell what should be compared? Is there another field that clues you in?

-LB

 

[lalakeyorai]

I am sorry for not providing clear explanation about my problem. I am actually need to find the time different between time 'Clear' and time 'Active' according to the Event, (either 'unreponsive' or 'down') where each Event will have both Status (active and clear). That is why i have 'down' in my syntax.

With your help, i am now able to find the different time for each Event and total all the different times then display the total on the group footer. Thank you very much.

Now, if the data in the group started with 'Active' followed by 'Clear' then 'Active' again (like my first post), then it will be a problem on displaying the time different. But i have noticed that some data in group can appear like these:

<Time> <Event> <Status> <timediff>
1. 9 Nov 2009 10:17:03 AM Unresponsive Active
00:00:00
2. 9 Nov 2009 10:43:26 AM Down Active
00:00:00
3. 9 Nov 2009 11:45:33 AM Unresponsive Clear
01:02:07
4. 9 Nov 2009 2:09:14 PM Down Clear
02:23:41
5. 9 Nov 2009 2:39:30 PM Unresponsive Active
00:00:00
6. 9 Nov 2009 3:10:56 PM Unresponsive Clear
00:31:26

timediff in red color are wrong because the time different value was not under the same Event. is it possible if i want to display the correct timediff(blue color) like below? *where for Event=unresponsive, timediff=[time(Status='clear') - time(Status='active')] while Event=down, timediff=[time(Status='clear') - time(Status='active')]

1. 9 Nov 2009 10:17:03 AM Unresponsive Active
00:00:00
2. 9 Nov 2009 10:43:26 AM Down Active
00:00:00
3. 9 Nov 2009 11:45:33 AM Unresponsive Clear
01:28:30
4. 9 Nov 2009 2:09:14 AM Down Clear
03:25:48

 

[lbass]

Do you have the option of grouping by event and then ordering by status?

-LB

 

[lalakeyorai]

Sorry, I am afraid i cannot do that because those data are already in group called 'device' (which i have not enclose in here) and data is sorted by time.

 

[lbass]

You can insert groups within groups, so could you group on device and then on event?

-LB

 

[lalakeyorai]

It work...!!! i've never thought of that. that is exactly what i need even the time become unsorted ascendingly but at least the time different calculation is correct. i'll try to figure out how make the time sort ascendingly and mantain the correct time different calculation (hope i find a way)...

thank you very much for your help, LB.... thank you so much...