So I'm newer to the reporting side of this thing and want to start generating some REAL reports (as the canned ones offer.... VERY LITTLE information).  I do understand the basics and understand the SQL and everything I need to put together to get an output of a report, for example I will write a SQL report to generate the last 30 tickets opened by submitter Label.  Simple.

 

HOWEVER There are going to be multiple levels of this report and I don't want 3-5 emails going out to a department head (or the IT head) for the (essentially) same information.  For example:  I want a report to contain the tickets opened in the last 30 days by submitter label, and in the same report (Farther down the page) I want a list of the all the ticket submitted in the last 30 days by category

(which should have the same tickets as the first report since it's the same table and same amount of tickets, the last 30 days, but sorted differently and containing different data columns)

if that is too difficult (or impossible with the report generating software) then something that may be simpler (but I also want):  1 report with 3 things:  Tickets opened in last 30 days, tickets closed in last 30 days, and tickets that are STILL open at the end of the month.  Is there a way to combine these into 1 report (cleanly) ?  With my limited SQL experience my first reaction to the issue is to do a SQL statement for 1 report then simply write a join statement that would have the second report in it?  Would something like that work?

 

Am I making this too difficult and should just stick to pushing out 50+ report emails a week?

1 Comment   [ + ] Show Comment

Comments

  • For the first example those are two different reports, yes they are different representations of the same data, but the reporting engine doesn't give you a way to include two separate reports on the same page.

    For the second one it might be possible to present three columns showing tickets opened, closed and still open for a queue in one report. Would you want that broken down by category?

    In terms of combining things you need to think about the underlying SQL query. A report is one SQL query so if you can craft a query that shows the data you want you're good to go.
Please log in to comment

Answers

0

This query will get you a count of tickets opened and closed in the past 31 days:

 select HD_CATEGORY.NAME, 
sum(if(CREATED > DATE_SUB(NOW(), INTERVAL 31 DAY), 1, 0)) AS COUNT_OPENED,
sum(if(TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY), 1, 0)) AS COUNT_CLOSED

FROM HD_TICKET
JOIN HD_CATEGORY on HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
WHERE HD_TICKET.HD_QUEUE_ID = 2
GROUP BY HD_CATEGORY.NAME

Be sure to change the HD_TICKET.HD_QUEUE_ID = 2 to reflect the queue ID that you want the report for.

Determining tickets that were opened in the past month and are still opened is a little harder but I'll see if I can figure that out next week.

Answered 11/15/2013 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share