Multiple outputs with 1 report?
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?
Answers (1)
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.
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. - chucksteel 11 years ago