Report help

[andrew-k]

Hi all I’m hoping someone can help with creating a custom report.

*We have a client who requires us to submit time sheets for work undertaken.
*A ticket may be closed on the same day it was opened, but also may stay open for several days. In some cases will be open across 2 months.
*The report must show the time spent per tech, per ticket, and also detail which days the work was done.
*The report will only ever span a single month so the status of a ticket is not relevant to the report.
*The report should only produce entries for the time spent in the specified reporting month.

So ultimately I need a report to show something like:

Ticket # | Summary | Update Date | Updated By/Assignee | Time Spent

Which would hopefully produce a report sorted and grouped by date showing something like:

1 | Help | 16/04/2013 | Andrew | 15m
2 | Printer | 16/04/2013 | John | 1h
1 | Help | 17/04/2013 | John | 5m
3 | Network | 17/04/2013 | Andrew | 1h

Is it possible?

If it is possible, is there a way to have the time on any given day stop counting after 7.5 hours and perhaps show something like CAP REACHED.

TIA

AndrewK

[molan]

I have already written and shared a report that will do most if not all of what you want.

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

feel free to make any minor tweaks you would like after you download it

[andrew-k]

That is almost perfect.

How can I change the date so that it can produce a monthly report? My SQL is very bad but something like DATE(April) ???

[molan]

Try This, edit the following 2 lines

AND ticket_work.created_at > DATE (‘now’, ‘-7 day’)
AND ticket_work.created_at < date(‘now’)

Change them to this

AND ticket_work.created_at > ‘2013-04-01’
AND ticket_work.created_at < ‘2013-04-30’

Make sure you use the leading zero when a day or month is single digit, if you don’t the report will wig out and do weird things.

[andrew-k]

Brilliant.

That’s exactly what I needed.

[molan]

Glad I could help.

don’t forget to spice up my report!

[andrew-k]

One more question. Due to a ‘site’ allocation error in SW (currently working with SW Support to resolve) I need to filter the results to exclude our local mail domain from the result set. ie:

I need to somehow use ‘tickets.created_by’ to match up to ‘users.email’ and then filter out everything that has ‘example.dom’ in the mail address.

Thanks

[molan]

you could add a line like this to match a domian

and tickets.created_by like ‘%@domain.com’

or a line like this to exclude a domain

and tickets.created_by like not ‘%@domain.com’

this line would go in the where section before the groupby