1

I have written a SQL report against the ticket_work table (is there a way to query ticket_work via the report GUI instead of SQL, by the way?). We want a report to list the time entries in a given date range. Right now I can edit the SQL and change the date range manually. What is the method/syntax to have the user input the date range? And can this be done on regular reports created via the GUI? Thank you!

2 Spice ups
2

When you create a new report, you can use SQL queries in that

Date ranges can include

today, tomorrow, last week, next week, last month, next month etc.

3

is an SQL report needed for what you want? Can you get the same output using the reports built in?

4

Well, I couldn’t find a way using the built-in reports (and maybe I didn’t look hard enough) to list individual ticket work time entries as opposed to the total time worked by a user per ticket. As soon as I pick Tickets as the “domain” of the report, it seems that all that is available is the total worked per user per ticket. But that does not show the dates when they worked, and how much time per date.

What is the syntax for specifying today, tomorrow, last week, next week, etc.?

We are Version 7.4.00075 by the way.

Thank you!

5

https://community.spiceworks.com/help/Reports_Overview

all here

Also search reports, one may exist

https://community.spiceworks.com/appcenter/browse/reports

6

Having read through the Reports Overview, I don’t see a way for a user to enter run-time report parameters. So I imagine that this is not possible?

Thank you.

7

Also, the sections “Using Specific & Relative Dates And Times” and “Using SQL Reports” seem to be mutually exclusive. Can you confirm?

Thank you.

8

I’m not really sure what you mean by this

9

BTW we are now Version 7.5.00063.

I am pretty good at SQL if you could provide a sample of the syntax for specifying today, tomorrow, last week, next week, last month, next month etc.

Thank you.

10

What I mean is, it seems that with “regular” Spiceworks reports, specific and relative dates and times can be used because of “the Chronic language which works with Ruby”. But with SQL the chapter simply says "Write your SQL code". There is no mention of how I can specify, for example, “3rd thursday this September” with proper SQL syntax. So if your first reply is true, I would like some examples I can then apply to my specific SQL report.

Thank you Rod-IT,

Don

11

I am not a DBA or that good with SQL - I have no need for it on most of the reports I run either - if the (second) link I provided already doesn’t have your specific needs, it may have something similar that you can look at the code on and go from there.

As a guess, it’s likely the same as those on the relative dates on the first link

12

OK, thanks. The first ticket_work report I checked that I thought would work was also SQL, so I think what I am looking for is not available, but I will check out a few App Center reports tomorrow. Thanks.

13

Perhaps a silly question, but why and how will the user run reports?

14

What do you mean by time entries?

Can you give an example of the type of output you want?

How many tickets open in x days, how many closed, how many opened and closed in 30 days etc.?

15

The users in this case are in a small IT department, so no problem to edit the report before running it. To answer your question, from the Reports page, with the link at the top of Spiceworks.

16

If I work on the same ticket 3 times in a week, I would like to see 3 lines on the report, showing the ticket number and summary, the date I entered my time, and how many hours of time I worked that day on that ticket.

I may have also worked on that ticket last week, but in this case, I don’t want to include last week’s time entries, only this week’s (or whatever the date range I choose). Make sense?

This is what I want for all tickets worked on by the four of us in our small department in a given date range. I don’t want the total time I worked on a ticket.

17

I’m confused by the reason for this, but how it reads you want a report that;

Shows only comments added in the last X days for each ticket worked on in this period, for each user assigned - is this a simpler description of what you want?

Oh and you want a time worked in this range, not total time overall?

18

The reason is as simple as what did I spend my 40 hour work week doing?

ticket_work entries are not comments. I can add comments to tickets without specifying that I spent a half hour on the ticket. There is a ticket_work table that shows exactly what I need. I have the SQL report working, I was just looking for some nice-to-have’s.

I do not want the total time I worked on ticket XXXX within a given timeframe. I want the specific time entries: what date/time did I enter my time, how many hours and/or minutes did I say that I spent on that ticket when I put in the time entry.

Ticket Date/Time Technician Time Worked

4344 2016-02-15 4:44pm Don Haskins 30m

4344 2016-02-16 3:57pm Don Haskins 1h

4982 2016-02-14 3:59pm Don Haskins 2h30m

4982 2016-02-16 5:05pm Don Haskins 45m

I hope that looks good once I post!

That sort of thing. Thank you, Don.

19

Hi Don,

I know this is an older request you entered, but I’m looking for the same for our business.

All tickets updated at = Date Range i.e. 04/01/2017 - 05/01/2017

Ticket# - Category - Location - Time Spent By User - Worked By - Summary - Last Comment

Note - would like Total time by user but it includes all time outside of the date range requested

Issue:

What I’m getting is time spent by user for earlier than the date range I required for much older tickets than 1 month.

I cannot find a standard report capable because we want management to be able to do this on their own.

I also couldn’t find an SQL that could give me all this. I’m not an SQL expert by any means :frowning: