Call Report -Help required

RT Users
1

Hi,

I have been using rt3 with mysql and till now, it has ben a success story.

Has any one got a html / cgi file to generate a report of the calls logged.

Thanks in advance

Manjunath hegde

Confidential: This electronic message and all contents contain information
from Syntel, Inc. which may be privileged, confidential or otherwise
protected from disclosure. The information is intended to be for the
addressee only. If you are not the addressee, any disclosure, copy,
distribution or use of the contents of this message is prohibited. If you
have received this electronic message in error, please notify the sender
immediately and destroy the original message and all copies.

2

Hi,

I have been using rt3 with mysql and till now, it has ben a success story.

Has any one got a html / cgi file to generate a report of the calls logged.

http://wiki.bestpractical.com/index.cgi?RT3StatisticsPackage

Andy Harrison

3

Hi,

I could build some MySQL querry which would give me a call report with all
the details i need.
Has any one got a script to generate a .xls file for such reports - please
help me
The querry is given below (incase if it helps some one )
SELECT
t.id as ‘Call No’,
DATE_FORMAT(t.Created, “%m/%d/%Y”) as ‘Date’,
CASE WHEN tcf.CustomField = 4 THEN tcf.Content ELSE NULL END as ‘Machine
ID’,
DATE_FORMAT(t.Created, “%H:%i”) as ‘Time Reported’,
CASE WHEN tcf.CustomField = 1 THEN tcf.Content ELSE NULL END as ‘Project’,
t.Subject as ‘Fault Description’,
u.Name as ‘Caller Name’,
t.Priority as ‘Priority’,
u.Name as ‘Engineer’,
“” as ‘Vendor’,
DATE_FORMAT(t.Started, “%H:%i”) as ‘Time Attended’,
DATE_FORMAT(t.Resolved, “%H:%i”) as ‘Time Completed’,
DATE_FORMAT(t.Resolved, “%m/%d/%Y”) as ‘Date Completed’,
t.Status as ‘Status’
FROM
Tickets t,
TicketCustomFieldValues tcf,
Users u
WHERE
t.id = tcf.Ticket
AND (t.Creator = u.id OR t.Owner = u.id)
AND DATE_FORMAT(t.Created, “%m/%d/%Y”) BETWEEN “11/20/2004” AND “11/22/2004”

I would like to use this in a .cgi file and create a .xls file - can some
one help me please ??

Thanks in advance

Manjunath Hegde-----Original Message-----
From: Hegde Manjunath [mailto:Manjunath_Hegde@syntelinc.com]
Sent: Tuesday, November 23, 2004 10:38 AM
To: rt-users@lists.bestpractical.com
Subject: [rt-users] Call Report -Help required

Hi,

I have been using rt3 with mysql and till now, it has ben a success story.

Has any one got a html / cgi file to generate a report of the calls logged.

Thanks in advance

Manjunath hegde

Confidential: This electronic message and all contents contain information
from Syntel, Inc. which may be privileged, confidential or otherwise
protected from disclosure. The information is intended to be for the
addressee only. If you are not the addressee, any disclosure, copy,
distribution or use of the contents of this message is prohibited. If you
have received this electronic message in error, please notify the sender
immediately and destroy the original message and all copies.
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Be sure to check out the RT wiki at http://wiki.bestpractical.com

Confidential: This electronic message and all contents contain information
from Syntel, Inc. which may be privileged, confidential or otherwise
protected from disclosure. The information is intended to be for the
addressee only. If you are not the addressee, any disclosure, copy,
distribution or use of the contents of this message is prohibited. If you
have received this electronic message in error, please notify the sender
immediately and destroy the original message and all copies.

4

There are modules for perl that generate CSV files to be downloaded
into your spreadsheet. But, perhaps a low tech approach of stuffing
into the existing query the conventional methods for creating CSV
files will do just fine ?

SELECT t.id as…“11/22/2004” INTO OUTFILE’/tmp/outfile’ FIELDS
TERMINATED BY ‘\t’ OPTIONALLY ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’
FROM
Tickets t, …

Open the file /tmp/outfile with your favorite text editor and
cut-n-paste the into an empty excel spreadsheet. You could do a http
get on /tmp/outfile if you are keen on using a web server. Don’t know
which mime type to use though.

Cut ‘n’ paste works for me though.On Wed, 24 Nov 2004 09:56:05 -0500, Hegde Manjunath manjunath_hegde@syntelinc.com wrote:

Hi,

I could build some MySQL querry which would give me a call report with all
the details i need.
Has any one got a script to generate a .xls file for such reports - please
help me
The querry is given below (incase if it helps some one )

SELECT
t.id as ‘Call No’,
DATE_FORMAT(t.Created, “%m/%d/%Y”) as ‘Date’,
CASE WHEN tcf.CustomField = 4 THEN tcf.Content ELSE NULL END as ‘Machine
ID’,
DATE_FORMAT(t.Created, “%H:%i”) as ‘Time Reported’,
CASE WHEN tcf.CustomField = 1 THEN tcf.Content ELSE NULL END as ‘Project’,
t.Subject as ‘Fault Description’,
u.Name as ‘Caller Name’,
t.Priority as ‘Priority’,
u.Name as ‘Engineer’,
“” as ‘Vendor’,
DATE_FORMAT(t.Started, “%H:%i”) as ‘Time Attended’,
DATE_FORMAT(t.Resolved, “%H:%i”) as ‘Time Completed’,
DATE_FORMAT(t.Resolved, “%m/%d/%Y”) as ‘Date Completed’,
t.Status as ‘Status’
FROM
Tickets t,
TicketCustomFieldValues tcf,
Users u
WHERE
t.id = tcf.Ticket
AND (t.Creator = u.id OR t.Owner = u.id)
AND DATE_FORMAT(t.Created, “%m/%d/%Y”) BETWEEN “11/20/2004” AND “11/22/2004”

I would like to use this in a .cgi file and create a .xls file - can some
one help me please ??

Thanks in advance

Manjunath Hegde

-----Original Message-----
From: Hegde Manjunath [mailto:Manjunath_Hegde@syntelinc.com]
Sent: Tuesday, November 23, 2004 10:38 AM
To: rt-users@lists.bestpractical.com
Subject: [rt-users] Call Report -Help required

Hi,

I have been using rt3 with mysql and till now, it has ben a success story.

Has any one got a html / cgi file to generate a report of the calls logged.

Thanks in advance

Manjunath hegde

Confidential: This electronic message and all contents contain information
from Syntel, Inc. which may be privileged, confidential or otherwise
protected from disclosure. The information is intended to be for the
addressee only. If you are not the addressee, any disclosure, copy,
distribution or use of the contents of this message is prohibited. If you
have received this electronic message in error, please notify the sender
immediately and destroy the original message and all copies.

The rt-users Archives

Be sure to check out the RT wiki at http://wiki.bestpractical.com

Confidential: This electronic message and all contents contain information
from Syntel, Inc. which may be privileged, confidential or otherwise
protected from disclosure. The information is intended to be for the
addressee only. If you are not the addressee, any disclosure, copy,
distribution or use of the contents of this message is prohibited. If you
have received this electronic message in error, please notify the sender
immediately and destroy the original message and all copies.

The rt-users Archives

Be sure to check out the RT wiki at http://wiki.bestpractical.com

5

Dear Hedge,

Of course you say ‘help required’. If you didn’t need help, you wouldn’t be
asking the list.

I’ve done this before, and it’s not difficult. What you want to do is format
the output into a comma-separated list of values, and then put the following in
the page header:

Content-type: application/msexcel\n
Content-disposition: inline; filename="test.csv"\n
Pragma: no-cache\n
Expires: 0\n\n

where test.csv is your filename. I hope this helps. I’ll also note that this
really isn’t a topic for this list, as it really has nothing to do with RT
except for the fact that it’s the RT database that you want to generate this
from.

-Sheeri Kritzer
Systems Administrator
University Systems Group
Tufts University
617-627-3925
sheeri.kritzer@tufts.edu

Quoting Hegde Manjunath Manjunath_Hegde@syntelinc.com: