Building a day-to-day open ticket chart

[chrishaw]

Hi all -

Using SCR 7.

I need to build a chart that shows how many problem tickets were in an open state on any given day going back 30 days from the current date.

I have fields that tell me when the ticket was opened and when it was closed, so figuring out what is open and closed at a given time isn't a big deal, that's just opentime < specificdate@7am and closetime > specificdate@7am. Note that this does mean that a ticket that was open for 5 days would be in all 5 of those counts.

What I'm having trouble with is how to do this for the 30 day rolling interval and how to build that into a chart.

Thanks

 

[chrishaw]

Okay, alternatively, I can create 30 different formula fields that would cover each day by doing current datetime, -1, -2, -3, etc. However, I can't figure out how to basically make a custom chart just showing those fields. Doesn't seem to be a way to do it.

 

[chelseatech]

The real problem is that you have a DB table that contains the ticket STATUS and you want to report on ticket EVENTS.

A UNION join can convert one to the other....
SELECT &quot;Open&quot; as EvtType, OpenDate as EventDate, ....
FROM Tickets
UNION ALL
SELECT &quot;Close&quot; as EvtType, CloseDate as EventDate, ....
FROM Tickets.

This works in Access, and any any SQL complient DB. The ... refers to any other fields with data you want from the Ticket Table.

Then you can select this as your source of data, and use a running total field to keep track of tickets as they open and close.

And now some sad news. CR7 will not display Running Total Fields in a chart - that feature arrived with CR8. So you might need to consider an upgrade.

Or find a SQL solution using subselects and they you are okay with CR7.
Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[chrishaw]

Ugh.

I know my garbage ODBC driver doesn't support UNION.

However, I'll have to look into the running total piece. I may be able to use that in some fashion.