Historical Record Status Reporting

[DLTaylor]

Crystal 9.0

I have a helpdesk system and want to be able to report historically on the number of open calls on a certain date.

The field in the table is calls.status and this value obviously changes with the call cycle - open or closed.

I guess that I need a formula(s) to establish the status of a call at a given date or time, but how do I do this?

Any help appreciated.

 

[kskid]

A little more techinical detail would be helpful such as

Sample input data and links
Record selection formulas
If problem with formulas, then post formula
Expected output
- layout by section
- Any groups


-LW

 

[DLTaylor]

Thanks for your reply. The output I would like is something along the lines of:

Title: Report of Open Calls on <date parameter>

By using a date parameter, this will then display all the call records at status 'open' on that chosen date. Display something like:

Calls.callid
Calls.loggedDT
Calls.customername
Calls.department
Calls.category
Calls.owner
Calls.status

Sample Data:

Calls.id Calls.loggedDT Calls.customername
MED12345 10/03/2005 09:44:35 Joe Bloggs
MED34567 10/03/2005 10:44:35 Fred Bloggs
MED56789 10/03/2005 11:44:35 John Jones

Calls.department Calls.category Calls.owner
A&E Hardware Tech Support
Day Hospital Software Tech Support
Cardiology Training Tech Support

Calls.status
Open
Closed
Closed

I don't have a problem with formulas or record selection formula because I haven't written any!! In truth, I don't know where to start with this! The issue I have got is how to report on the historically on the status of a call. The status field shows the calls current status (which may now be closed) but I want to know when it was at status open).

Any help appreciated!

Thanks
Sian

 

[jambu]

The Calls.LoggedDT looks promising, as long as the status of a call is open from when it is logged until its status becomes 'Closed'

You could test this by opening a call and seeing what is in the database, then closing the call and comparing the differences.

Assuming that Calls.LoggedDT will contain the date that the call was opened on, you will then need to add a date parameter and add something like the following to the record selection formula (under the report menu).

Calls.loggedDT = {?ParamDate}

When you run the report the parameter will prompt for the date and the record selection formula will limit the results to those records with a matching LoggedDT field.

hope that helps.

 

[DLTaylor]

Hi

Thanks for your response. I understand what you have explained, however, it is not quite that simple!!

Calls do go to the 'open' status as soon as they are logged, however, a call may be logged one day and then remain open for a week until closure. So the logged date can only tell me the logged date, and does not indicate if a call is open.

e.g. call logged on Monday 21/03, closed on Wednesday 23/03 - it is at open status on the Tuesday 22/03 - but how can I get a report to tell me this historically?!

I guess what I am trying to achieve is something that will look at the database history, and when the report is run for the chosen date, it will 'test' the status of each call record and then report out the open calls on that day.

May be I am trying to achieve the impossible!!

 

[kskid]

Assuming you want all calls since a given date, then

Calls.loggedDT in {?ParamDate} to currentdate


If through yesterday, then

Calls.loggedDT in {?ParamDate} to currentdate

Through the end of last month

Calls.loggedDT in {?ParamDate} to minimum(LastfullMonth)

Need more information on what you want to display. How do you want it grouped? Category? date? status?

Cheers,
-LW

 

[DLTaylor]

What I want is not calls logged, but calls at open status.

I just want to know the number calls which our helpdesk had open on a specified date. e.g. run the report now, to show me how many calls were open on 10th March. This does not mean the number of calls opened/logged on that day but the number open. Hypothetically, a call could have been logged 12 months previously but was still open on that day.

Displayed something like:

Calls.callid
Calls.loggedDT
Calls.customername
Calls.department
Calls.category
Calls.owner
Calls.status

 

[jambu]

I see what you mean, assuming you want a specific date and that there is another field which contains the Closing date you could try this.

Calls.loggedDT <={?ParamDate} and Calls.ClosedDT >= {?ParamDate}

This assumes that there is such a field, If there isn't an obvious closed date field or date status changed field then you are going to have to hunt for it.

As per my previous suggestion, add a call and examine the database, close the call and compare the calls fields.

 

[DLTaylor]

Thanks for that. I will try that this afternoon.

 

[kskid]

Can you have multiple rows of a given calls.callid to give you a history of transaction or do you just have one row for a given callid? Is the loggedDt is updated with each change in calls.status.

-LW

 

[DLTaylor]

Just one row for each callid in the calls table and LoggedDT holds simply the logged date/time, which is fixed (does not change) for each call.

The callshistory table does hold different transactions for the same callid. Each action on a call has a callhistoryid, which is a unique transaction ID for each action - such as call opened, update added, call closed.

There is an addedDT field for each transaction, and also 2 fields, 1 called 'change from' and 1 called 'change to' - so this shows when the call status changes from open to closed.

I guess there is something here I could work with?