Detailed Ticket Reporting

[Ring-A-Nerd]

Script URL: http://www.ringanerd.co.za/Helpdesk
Version of script: 2.4.2
Hosting company: Hetzner
What terms did you try when SEARCHING for a solution: Help Desk Software Ticket Reporting

Write your message below:

Hi there! Firstly, I just want to thank you in advance for reading my message, and for any attempt you may make in trying to assist me.

Basically what I'm trying to do is have a reporting feature that will show the total hours worked for a client. If I knew how to run a SQL command that would be even easier, as I'm sure all the data I need is in the databases already...

What I want to get in a report is;

Time Worked
Name of Client (could be email or the name given)
Name of Company (not all clients have this, so it can be optional)

So the time needs to be totaled for each client, but allow me to specify the data range, and I'd preferably want this to be on a month-to-month basis. If its not a hard thing to do, it'd be nice to have a report for each person/company listing each date and the hours worked on a day to day basis for the entire month.

Also, to make reporting easier name of client should also take into consideration the email address, as sometimes we put the name of the company in the "name" field but leave the email address intact, or vice verse.

HESK is so damn perfect, every feature of it, but the reporting is so minimal. I really would appreciate it if someone could assist me with this... It doesn't have to be perfect, it can be simple raw data that I can make a plan with to beautify. I'm not lazy, so just explain to me what I need to do and I'll try my best to understand, I just really wanna get this working.

If any additional information is required, PLEASE, do let me know and I'll gladly respond with whatever it is you require to help me.

[bigj6360]

I am looking for this exact same thing.

Maybe in addition to total hours, maybe even average response time?

thanks,

[dr_patso]

I can help!

use mysql queries, they are easy to look up on google... you can run a mysql query in phpmyadmin and export it to CSV.. even then you can sort and filter and generate tables/pie charts with your familiar excel...

Also you can use this code and create a link to it to export the CSV.


Theres a little thread on this sloppy way of custom reporting on your hesk that I started.. I had to create a couple tables to get the naming correct for the status and priority, but you maybe do not need that.

viewtopic.php?f=13&t=4239

Code: Select all

<?php

/*

 * PHP code to export MySQL data to CSV

 * http://911-need-code-help.blogspot.com/2009/07/export-mysql-data-to-csv-using-php.html

 *

 * Sends the result of a MySQL query as a CSV file for download

 */



/*

 * establish database connection

 */



$conn = mysql_connect('localhost', 'databaseusername', 'databasepassword') or die(mysql_error());

mysql_select_db('databasename', $conn) or die(mysql_error($conn));



/*

 * execute sql query

 */



$query = sprintf('SELECT `hesk_tickets`.`id` AS "Ticket Number", `hesk_tickets`.`dt` AS "Date Created", `hesk_users`.`name` AS Owner, `hesk_categories`.`name` AS Category, `hesk_tickets`.`name` AS "Contact", `hesk_tickets`.`subject` AS Subject, `hesk_status`.`name` AS "Status", `hesk_priority`.`name` AS Priority, `hesk_tickets`.`time_worked` As "Time Worked"

FROM hesk_tickets, hesk_categories, hesk_users, hesk_status, hesk_priority

WHERE (

hesk_categories.id = hesk_tickets.category

AND hesk_users.id = hesk_tickets.owner

AND hesk_status.id = hesk_tickets.status

AND hesk_priority.id = hesk_tickets.priority

)');

$result = mysql_query($query, $conn) or die(mysql_error($conn));



/*

 * send response headers to the browser

 * following headers instruct the browser to treat the data as a csv file called export.csv

 */



header('Content-Type: text/csv');

header('Content-Disposition: attachment;filename=hesk_tickets.csv');



/*

 * output header row (if atleast one row exists)

 */



$row = mysql_fetch_assoc($result);

if ($row) {

    echocsv(array_keys($row));

}



/*

 * output data rows (if atleast one row exists)

 */



while ($row) {

    echocsv($row);

    $row = mysql_fetch_assoc($result);

}



/*

 * echo the input array as csv data maintaining consistency with most CSV implementations

 * - uses double-quotes as enclosure when necessary

 * - uses double double-quotes to escape double-quotes 

 * - uses CRLF as a line separator

 */



function echocsv($fields)

{

    $separator = '';

    foreach ($fields as $field) {

        if (preg_match('/\\r|\\n|,|"/', $field)) {

            $field = '"' . str_replace('"', '""', $field) . '"';

        }

        echo $separator . $field;

        $separator = ',';

    }

    echo "\r\n";

}

?>

[Klemen]

I would also recommend exporting and doing reports in Excel.

The thing is that HESK database has been designed to be fast and easy on server resources. The downside is it isn't optimized for gathering detailed reports.

Yes, it can be done with complex joins and sub-queries, but on large databases this becomes very slow and resources intensive.

[davidfdz]

Hello, just registered so I can post this question, but lemme just say: i just finished implementing HESK for our small business. We have about 300-500 customers, and on average get about 7 new tickets a day. I'm the only Tech Support member on our staff and must divide my duties between tech support and development/production of web-based kiosks and directories. After years of having only e-mails and post-it notes to track issues, HESK is a true breath of fresh air, and an amazing gift to the tech community as a whole! You deserve a plaque or something.

That being said,

Would the size of the hesk ticket database after about 3 years or be too big for more detailed ticket reporting like you said? Server resources aren't really an issue since we are running this on a dedicated server.

I understand that this might not be on your list of things to do in terms of generating detailed reports like this, but our company would possibly be willing to compensate you for implementing this feature and some other more minor things (Rich Text Editing, for example).

How far off your radar is doing something like this, and how much time would it take to get something like this implemented?

[Klemen]

Hi and thanks for the kudos.

Version 2.5.0 will support export of tickets into Excel, so I would recommend doing reporting there.

More complex reports are indeed off the radar at the moment and not planned for the near future.

Unfortunately I am also not available for hire, so unless you have an in-house programmer your best bet would probably be to hire someone, for example here: http://www.freelancer.com

Sorry!