formula for re-opened cases

[EmmaTippett]

Wonder can anyone help us, we have a field in our database called status of case, when a case opens we put it to OPEN, when it closes, we change it to CLOSED. Sometimes, they re-open, so then there's an additional OPEN status. All these status changes have a system date. How would we write some kind of formula to show cases that have been re-opened within a certain data range?

 

[IanWaterman]

Group by Case and then create a formula
@Status
If Status = 'OPEN' the 1 else 0

Sum this in group header, suppress details and group footer

In group Select expert

Sum(@Status, Casegroupfield) > 1

Report will now only show cases with more than one Open status

Ian

 

[jlopeman]

I created a table with 3 columns: caseID, status, and timeStamp.
I created 3 caseIDs. For caseID 1, I "reopened" the case (3 timestamps, with the last status being 'open')

In the report, I grouped by caseID. I created a formula called status:

if {#status} >= 1 AND {cases_.status} = 'open' then 'reopened' ELSE {cases_.status}

I then created running total field {#status} with the following properties:

summarize status
type of summary = count
evaluate on each change of status field
reset on change of caseID group

If you put the formula status in the group header, it will return 'reopened' for caseID 1.

 

[EmmaTippett]

THANKS muchly!!!