Report finding changes in statuses

[rhino143]

I've been working on a report that takes data in the following format:

Account Number DateTime Worktype Queue Status USERID

The database tracks everything that's done to an item as it gets routed through the company until it's finally resolved. So we may have:

10001 11/11/05 12:00:00PM NEWACCT NEW RESOLVED EEEEE
10001 11/10/05 04:00:00AM NEWACCT NEW PROCESSED DDDDDD
10001 11/10/05 03:45:00AM
10001 11/10/05 03:33:00AM NEWACCT NEW RESEARCH CCCCC
10001 11/09/05 17:23:00PM NEWACCT NEW RESEARCH CCCCC
10001 11/09/05 17:20:00PM NEWACCT NEW NEW AAAAA

We're trying to track any item that went from NEWACCT NEW RESEARCH to NEWACCT NEW PROCESSED and show it on the report. By putting the same table on the report twice (alias) and using the first one as the beginning status and the second as the ending status we're able to show the information, but it shows too much. In the case above it would show two items that went from RESEARCH to PROCESSED. The line without a worktype, queue or status is another problem as it's there when notes are put on the item. It also prevents us from using the previous or next command.

Any suggestions on how to get crystal to only show the change in status once? I'm using crystal 8.5 and using a selection similar to the following:

Begin.Key_Date = End.Key_Date and
Begin.Key_Time = End.Key_Time and
Begin.Key_Millisec = End.Key_Millisec and
Begin.Worktype = "NEWACCT" and
Begin.Queue = "NEW" and
Begin.Status = "RESEARCH" and
End.Status = "PROCESSED" and
Begin.Key_Date > '2005-10-25' and
Begin.Key_Date < '2005-10-27' and
End.DateTime >= Begin.DateTime

Thank you in advance!

 

[lbass]

Would just detecting the presence of both "Research" and "Processed" in each group do the trick?

-LB

 

[rhino143]

That would work if I just wanted to count the account numbers that went from research to processed. But I need it loose enough to count multiple times if it goes back out of processed back to research and then back to processed by the same person or different person.

 

[MrBillSC]

I don't think you need the alias table. Just read the table once and in your record select formula select only if
{table.status} in ["RESEARCH","RESOLVED"].
Group on Account Number and add decending sort on DateTime field.
Create a formula and place it in the detail section:
WhilePrintingRecords;
NumberVar CountResolved;

If Not OnFirstRecord and
Previous({table.status}) = "RESEARCH" and
{table.status} = "RESOLVED" then
CountResolved:= CountResolved + 1

Create a formula and place it in the Account Number group header;
WhilePrintingRecords;
NumberVar CountResolved:= 0

Create a formula and place it in the Account Number group footer;
WhilePrintingRecords;
NumberVar CountResolved;
NumberVar CountResolvedDisplay:= CountResolved

Suppress whatever sections you don't want to see.

MrBill

 

[rhino143]

MrBillSC

Thanks a lot for your suggestion. I've worked on various reports stemming from your idea. One thing I've been having trouble with is narrowing down the ending status to a date range. The report should show items that went to an end state (for example COMPLETED) during a certain date range, but that same date range shouldn't affect the beginning state.

This has brought me back to my alias idea where I use the first table to find the end states, then pass the account information to the same table to find all events and use your method to parse those events.

About the only thing that I can't figure out when using the alias table and your method is how to suppress the group header (USERID) when it has end states but no qualifying beginning states. It shows up as a USERID with no data and 0 records.

Chris

 

[MrBillSC]

If I understand correctly, you could select the beginning states in the main file and the end states in the alias file. That way if there are no begin states selected, the alias end states will not be selected. You would still select the end-states based on your date range. This is less efficient as it will probably read more records or can you suppress the group header if the first record is not one of the beginning states?

MrBill