Capturing Date/Time fields for total!

[IKONOS]

Hello all... again.

I thought what I had was a simple job trying to create this report, but has turned out to be one problem after another. Here is what I am trying to accomplish:

To create a simple report showing the status of emails coming in through our website. In doing this, I am trying to account for these emails by using the {DateTime_field} associated with the email status itself. Each email that comes in populates a separate table in the DB depending on what the status is. There are four tables:

{create_dt_tm} - which shows the date and time of when the email is submitted.
{start_dt_tm} – which shows the date-time the email is accepted.
{response_dt_tm} – which shows the date-time that a response to the email is sent.
{end_dt_tm} – which shows the date-time of when the email case is closed.

What I am attempting to show is a simple report that details how many NEW, OPEN or CLOSED cases are outstanding at report time. To do this, I thought a formula with one ‘DT_TM’ field canceling out another one would work. Here is a layout of the mechanics behind this scheme:

{create_dt_tm} {start_dt_tm} {end_dt_tm}
NEW yes NULL NULL
OPEN yes yes NULL
CLOSED yes yes yes

If an email is NEW, the only database field populated is the {create_dt_tm}, the other two have no value at all. OPEN, populates both the {create_dt_tm} and {start_dt_tm} fields but with different time stamps naturally and leaves the {end_dt_tm} field empty. CLOSED populates each of the DB fields.

What I would like to do with this report is to capture the number of emails based on their status.
(e.g. - NEW-10, OPEN-15, CLOSED-156)

Any help would be greatly, GREATLY appreciated!
I am stumped!

Mark

 

[MrBillSC]

Mark,

I would try creating 3 formula fields as follows:

CreateCount:
If IsNull({start_dt_tm}) then 1 else 0

StartCount:
If IsNull ({end-dt_tm}) and not IsNull({start-dt-tm}) then
1 else 0

EndCount:
If not IsNull({end_dt_tm}) then 1 else 0

Then insert subtotals where appropriate.

best of luck,

bill

 

[rosemaryl]

Dear Mark,

Without more info, this is what I would do.

Create a formula called Status.

//begin formula @Status

if isnull({start_dt_tm}) and isnull({end_dt_tm}) then
" New"

else if isnull({end_dt_tm}) and not isnull({start_dt_tm}) then " Open" else "Closed"

//end formula @status


Group on @Satus and insert the id field for the emails.

Insert a summary for the group that counts the email id.

Move the summary to the Group Header

Hide the details and group footer and you should end up with:

New: 125
Open: 25
Closed: 50


Hope this helps,

ro

Rosemary Lieberman

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.