Counting Rows Returned Trouble 1

[Travlnbard]

NEWBIE ALERT! I am using Crystal 8.5, connecting to an Oracle database using a tnsnames.ora file to connect.

I am trying to create a report that will show what calls are open for each user. A single call can be opened and closed several times. So I need to determine the date of the last status change on a call and then determine if the status is "O" or "C". The problem is that a call can be opened and closed on the same date (the time the call status changed is not captured).

What I have right now is a group for the user and a group below that for the call ID. In the details I have a running total field that determines the maximum of the date field. I use that field to suppress any record that wasn't opened or closed on that date.

Now what I want to do is determine how many rows are returned. If it is an odd number then I know that the call is still open, if it is an even number I know that the call is closed. (Because a call has to be opened first and a call cannot be opened twice or closed twice in a row).

The problem comes when I try to use the #MaxDate in a running total. #MaxDate is not on the list of choices to do a running total on. I have also tried to add a field based on the #MaxDate that is set to 1 if the date is equal to the #MaxDate otherwise it is set to 0. Then tried to do a running total on that field but had the same problem.

Any help is greatly appreciated.

Cathy

 

[synapsevampire]

Can't use tnsnames.ora to connect, it's simply a configuration file used by Oracle.

If the time isn't captured, then you'll have to assume that the natural order of the rows demonstrates the age.

Apparently the only outstanding issue is to count the rows where the date is the same as the max:

group header formula:
whileprintingrecords;
numbervar Counter:=0

Details formula:
whileprintingrecords;
numbervar Counter;
if {table.date} = maximum({table.date},{table.groupfield}) then
Counter := Counter+1

group footer formula:
whileprintingrecords;
numbervar Counter;
if remainder(counter,2) = 0 then
"C"
else
"O"


Now you can display the date and the above formula in the group footer, and suppress the group header and details if need be.

-k

 

[lbass]

If you want to use an inserted running total, you can set the following criteria in the evaluation area->formula->x+2 area of the running total:

{table.date} = maximum({table.date},{table.user})

Reset on change of group {table.user}.

Then you can use the running total in a formula:

if remainder({#rt},2) = 0 then "Closed" else "Open"

...which is essentially the same as SV suggested, only using an inserted running total.

-LB

 

[Travlnbard]

Thank you! That worked perfectly! I am going to try what lbass recommended too (just to see the difference and for my own knowledge). You both are great for getting back to me so quickly.


Cathy (a.k.a. Travlnbard)