[rt-users] question on reporting

[Hersker, Steve]

I am in a similar situation in that I need to generate reports out of RT
(for SOX compliance). We're using a commercial helpdesk ticketing app now,
but it's missing some key features that RT has (and RT is *much* easier to
use and infinitely more flexible). Before I could begin to convert to RT, I
had to make sure I could get the reports out of it that I needed. 

My SQL skills are minimal, so I opted to quickly "prototype" the reports
using MS Access and the mySQL ODBC connector. Stephen is right in that the
db schema takes some getting used to. (But definitely doable) I found the
easiest method in Access was to create several (read: many) layered queries
to assemble the ticket. Then, I just base the reports off queries with the
appropriate criteria (e.g. All new/open/stalled tickets; All tickets closed
in the last 7 days; All tickets for an Organization & date range, etc).

Stephen also brings up a good point about the export list feature in to
Excel. But, in my situation, I need the ticket history included in the
report, not just the header info/stats. 

(Now I'm using the API to import my users and, hopefully, the ticket history
from the existing app - about 21,000 tickets).

If you think you're going to use Access, let me know. I'd be happy to share
what I've learned, tripped over and worked-around.

Good Luck
Steve




> -----Original Message-----
> From: Stephen Turner [mailto:sturner at MIT.EDU]
> Sent: Thursday, December 15, 2005 11:20 AM
> To: Jonathan Jesse; rt-users at lists.bestpractical.com
> Subject: Re: [rt-users] question on reporting
> 
> At Thursday 12/15/2005 10:49 AM, Jonathan Jesse wrote:
> 
> >What do you use for reports out of RT? For reports out of our banking
> >system, I can write queries and other reports with a product we have
> >(Cognos Impromptu) and assume thru an ODBC connection or something I
> could
> >connect to the database and generate the information I need and make the
> >reports a little prettier and easier to read then doing just printing a
> >query result from the RT web page.
> >
> >So how do you all generate reports for the boss? Or people interested?
> >
> >
> >Thanks,
> >
> >Jonathan
> 
> Hello Jonathan
> 
> You can certainly point a reporting tool at the database, but the schema
> is
> a little hard to understand (as it is with many transactional DBs) and so
> you'd need to do quite a bit of studying before you could be certain your
> reports were showing what you wanted.
> 
> You could move some RT data to a data warehouse but that would probably be
> a sizeable project and would again require you to learn the RT schema
> fairly well.
> 
> A quick alternative is to use the spreadsheet download link that appears
> on
> the bottom of the search results page - you can get data into Excel and
> perhaps use Impromptu or whatever to make reports from that.
> 
> I'd be interested to hear what you decide.
> 
> Steve
> 
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Be sure to check out the RT Wiki at http://wiki.bestpractical.com
> 
> Download a free sample chapter of RT Essentials from O'Reilly Media at
> http://rtbook.bestpractical.com
> 
> WE'RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
> San Francisco - Find out more at
> http://bestpractical.com/services/training.html