Record value depending of the next record

[Bruni007]

Hi,
I am trying to do something with Crystal Reports 9, but I am not sure if it is possible to do it...

how_long is the field I want to add
...depending of how many consecutive True value there is in the active field, I want to know for how many days it is True

when it is False...I put no value in the how_long record

time active how_long
2006-05-23 False
2006-05-24 True ~4days
2006-05-25 True ~3days
2006-05-26 True ~2days
2006-05-27 True ~1day
2006-05-28 False
2006-05-29 False
2006-05-31 False

I thought I could use the function Next(), but I need to go further than the next record

Any way to do this?
Would it be with a subreport? I don't know
Thanks for your help

Michael

 

[jdemmi]

Unless I am missing something you should be able to use a running total.

Count the records, use a formula under EVALUATE to provide the criteria (where active = true)

-- Jason
"It's Just Ones and Zeros

 

[Bruni007]

What I want to know is for how many days it has been active, the days numbers are going down instead of going up...I don't think that I can use a running total

 

[jdemmi]

I think you can but I haven't seen your entire data/report structure.

Can you send more of the table structure?

One more question, which may be answered after I see more of your table structure, why is the count 4 days for the 5/24 date?

-- Jason
"It's Just Ones and Zeros

 

[Bruni007]

What I wrote as an example is a simplified version of my report...It may have been clearer if I would have written this:

time active how_long
2006-05-23 False
2006-05-24 True 4timesTrue
2006-05-25 True 3timesTrue
2006-05-26 True 2timesTrue
2006-05-27 True 1timeTrue
2006-05-28 False
2006-05-29 False
2006-05-31 False

on the 24th, I want my report to generate a value of 4 because there is 4 consecutive True value in the active field from this record

on the 25th, I want my report to generate a value of 3 because there is 3 consecutive True value in the active field from this record

 

[synapsevampire]

Crystal doesn't look ahead more than one row, so this gets ugly in Crystal.

One means might be to use a subreport and in it sort descending, and create a Running Total which does a countwhich resets when how_long = false. This gives you the needed day count.

Then you'd pass the date as a shared variable to the subreport and pass back a shared variable of the counts.

Not very elegant, I'd create the how_long number on the datasource instead. Of course that requires that you code on the database side, and that you post your database and connectivity, which should be included with every post.

-k

 

[lbass]

Let's say you are doing this per customer group. I think you can do this by creating a running total {#active}:

Select {table.customerID}, count, evaluate using a formula:

{table.active} = true

Reset on change of field: {table.active}

Then create two formulas:

//{@iftrue}:
if {table.active} = true then 1

//{@reversecount}:
if {table.active} = true then
sum({@iftrue},{table.customerID})+1 - {#active}

The reversecount formula will show the descending values. Format {@reversecount} ->format field->numbers->customize->suppress if zero.

-LB