1

Hey guys. We are getting audited and the auditor is requesting some reports and also make some changes to how the reports are done.

I almost have everything that I need in a report, but there are a few things that I am missing and I cannot do in the regular report. I know nothing about SQL, so my ability to create a custom report is slim to none.

Here is what I need the report to do

I need both a weekly and monthly report that contains the following

Ticket #

Created By (Email)

Create Date

Close Date

Assigned to

Time Spent

Days Open

Summary

Total number of tickets for that week/month

I need to make this poll back for a week or month because I need to automate this and make it automatically send to my boss so that he can then forward it on as needed. As it is right now I can just about do all of that through the regular report builder…Problem is, there is no way to set for example the last 7 days or last 30 days, only a time range.

4 Spice ups
2

Hey Math,

Please keep in mind that this “Total number of tickets for that week/month” for only one reason. It is not associate it with ticket table, it as unique calculate number.

So you list is going to look like this

Ticket # … Close Date Assigned to Time Spent Days Open Summary # of Ticket weekly /monthly

1 July 25 David 10 minutes <1 TEST ???

2 July 25 David 5 Minutes <1 TEST ???

What are you expecting to show up on the # of Ticket weekly /monthly for every ticket?.

Everything else is possible and it even possible to create the ticket weekly/monthly as unique report.

1 Spice up
3

I have a report that will pull a total of how many tickets a technician has closed in the past 30 days. I was hoping it to calculate total number of tickets that week or month but I see the issue…It would be a column, and the only way for it to work would be to have it on every line which I don’t think would look right to have it at the end. Maybe I can incorporate a different report for something like that.

Thanks for the assistance! :slight_smile:

1 Spice up
4

You may be able to get away with doing 2 reports then. For some examples on how to edit a report for what you want to do take a look at this report

http://community.spiceworks.com/reports/2084

I documented most lines in it so that it would be easy to edit.

1 Spice up
5

Thanks! That script was helpful! I am trying to add my own stuff to it…Basically I have added this and changed the select numbers from 10 on up to reflect the changes.

union
  select 10 as "Order", 'Tickets Closed this week for ' || users.first_name || " " || users.last_name as "Category",count(tickets.id) as "Status"
  from tickets join users on assigned_to=users.id
  where (tickets.closed_at >=date('now','-14 days'))and (tickets.closed_at <=date('now','-7 days'))

However, when I run the report, since there are 5 technicians, it will put 5 # 10’s in the list. How can I fix this? Can the “Order” field be hidden so that it doesn’t get listed on the report when I run it, or something along the lines of that?

6

I am not sure if you can hid the numbers or not. I mostly don’t even notice them any more. I had to go look at the report again just to see if they showed up.