Formula Query

[DLTaylor]

I have a database which is used to log IT Helpdesk calls. I want to create a report to show how many calls were at the status 'open' on a particular date.

I can easily write a report to show calls at the status 'open' at the present time, however how can I run this retrospectively? e.g. how many calls were open on the helpdesk on 1st June 2004.

Any help/suggestions appreciated!

(Crystal v9.0)

Thanks

Sian

 

[Katy44]

The layouts of your tables would help.

 

[DLTaylor]

2 tables for this report - Calls table which contains the individual call records, and CallsHistory table which holds the actions on each call, e.g. open, hold, closed, etc.

I need a formula which will identify which records were at status Open on the selected date.

 

[Katy44]

Well you could query the CallHistory table:

select ...
from CallHistory
where date = <selected date>
and status = 'open'

If this doesn't help, then what are the fields in youe CallHistory table?

 

[DLTaylor]

Thanks for that. Will give that a go.

 

[synapsevampire]

If it's all in the CallsHistory table, then add that table to the report, create a parameter of type date, and in the Report->Edit Selection Formula->Record place:

{table.status} = "Open"
// or whatever the Open indicator flag is.
and
{table.date} = {?MyDateParameter}

Now the report will prompt for the date and only return open rows.

It may be that you have a date field which indicates open, in which case you need only build a parameter and in the record selection use:

(
{table.opendate} <= {?MyDateParameter}
and
isnull({table.closeddate})
)
or
(
{table.opendate} <= {?MyDateParameter}
and
{table.closeddate} > {?MyDateParameter}
)

If you post example data and expected output you'll get better results than posting text descriptions.

-k

 

[DLTaylor]

Thanks so much for your reply. I have tried these and other variations, but I don't have a date field which indicates open or a {table.date} field.

I need to be able to use {table.datelogged} and/or{table.dateclosed} but I don't have a date field which indicates the date the call was at status open.

 

[kskid]

To get "Open" status

Creating a parameter date range

isnull({table.dateclosed}) and
{table.datelogged} >= minimum({?MyDateParameter}) to maximum({?MyDateParameter})

 

[kskid]

Ooops error in code

isnull({table.dateclosed}) and
{table.datelogged} [b]in[/b] minimum({?MyDateParameter}) to maximum({?MyDateParameter})

 

[DLTaylor]

Thanks for this but I get an error saying that the date parameter field cannot be summarised?

 

[kskid]

Assuming that you have one record per call, the code goes into your record selection formula but the summary is based on what you want to count and how you are grouping the records. In short, what's your expected output?

You can insert count summary on {table.logid} if there is one record per log. If your database has multiple records for the same logid, then use distinct count summary count.