How to create a custom report?

[sourabh]

Hi,

May I know how to create a custom report in OTRS 3.0.2.
I have tried stats but need customized report.
Shall I use SQL query in SQL box?

I need a report that contains total close tickets, total open tickets, tickets open for 1 day, 3 day, 5 day, 7 day and so on.

If I use the SQL box, please provide the query and procedure how to run the reports.


Regards,
Sourabh

[jojo]

you can use SQl from other Tools like jasper reports etc.

You'll find the database scheme here: http://ftp.otrs.org/pub/otrs/misc/

[sourabh]

I have the details of the OTRS database.

Could you please give me the Query for the requirement mentioned in previous mail and guide me how to generate reports through OTRS?

[jojo]

The report generaot (statistics) is easy to understand. For the SQL just try it and play around...

[sourabh]

I have tried stats but it doesn't pull up a custom report.
For custom report, I have tried different queries but did not work.
Could you explain me SQL query with an example and how to import in excel as well?

[crythias]

What kind of "custom report" do you want? What exactly do you want to customize?


Stat# / Title / Object / Description
10001 Overview about all tickets in the system TicketAccumulation Current state of all tickets in the system without[..]
10002 List of open tickets, sorted by time left until escalation deadline expires Ticketlist List of open tickets, sorted by time left until es[..]
10003 Changes of status in a monthly overview Monthly overview, which reports status changes per[..]
10004 List of tickets created last month Ticketlist List of all tickets created last month. Order by a[..]
10005 List of tickets closed, sorted by solution time Ticketlist List of tickets closed last month, sorted by solut[..]
10006 List of open tickets, sorted by time left until solution deadline expires Ticketlist List of open tickets, sorted by time left until so[..]
10007 List of tickets closed, sorted by response time. Ticketlist List of tickets closed last month, sorted by respo[..]
10008 List of the most time-consuming tickets Ticketlist List of tickets closed last month which required t[..]
10009 List of open tickets, sorted by time left until response deadline expires Ticketlist List of open tickets, sorted by time left until re[..]
10010 New Tickets TicketAccumulation Total number of new tickets per day and queue whic[..]
10011 List of tickets closed last month Ticketlist List of all tickets closed last month. Order by ag[..]

Each of these can be customized. You just change the range you want. If you want counts, choose the summation.

[jojo]

For custom report, I have tried different queries but did not work.

Please show your SQL Queries and your expected output.

[sourabh]

I need a report that contains total open cases, closed cases, cases open for more than 1 day, 3 day, 5 day and 7 day in a single report. Is it possible with STATS?
If YES, please explain.I think we can pull this report only through SQL.

Please provide a query for this?

[crythias]

No, it's not possible in Stats. You have more than 5 different things you want to report in one report.

This is a prime thing to hire OTRS to develop for you.

[sourabh]

Is it possible through SQL Query.
If yes, please send me the query.

[jojo]

it is not possible to send you the query. You should consider to buy professional consulting and support

[cbravo]

Can I use the 2.4 for 3.04 or has it changed much?

[jojo]

could you please explain your question?

[cbravo]

could you please explain your question?

on the link you provided, the latest picture of the relations between the database is for otrs 2.4, there is no 3.04. Can I use 2.4 or has the schema changed much?

[jojo]

3.0 ist mostly a 2.4 from the database schema