Greetings

Thanks for the explanation m8
Been banging my head on this for sometime.

Is it possible to get a template of a MySQL query for calculating the averages.
And probably a hint about how to amalgamate it with one of the SQL report template in KACE 1100 - "Work Report last 31 days by person"


select W.STOP as DATE, CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET, U.FULL_NAME, substring(W.NOTE,1,1200) as NOTE,
format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED
from (HD_WORK W, HD_TICKET T, USER U)
where W.HD_TICKET_ID = T.ID
  and isnull(W.VOIDED_BY)
  and W.USER_ID = U.ID
  and W.STOP > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by U.FULL_NAME, TICKET, W.STOP


Much appreciated

I understand what you mean by "formulas" now, and yes you can run calculations using *almost* any MySQL formula. I've done several and you can add columns, average them, etc. with no issue.

As I said, you'll have to use custom SQL queries for the majority of the reports you are looking for because the "wizard" cannot join tables (ticket work, comments, and other fields are in three separate tables). It's no different than using something like Crystal Reports, because you'll have to write queries either way. You CAN use third-party reporting tools if you desire (using read-only ODBC), but there is no need because all data that would be available in a third-party reporting tool is already available in custom SQL reports.

You'll need a DBA or you'll need to learn MySQL query syntax for custom SQL reporting.

I know the sql backend will run queries for information that is the basis for any reporting.

What I want to do is calculations such as:

Total daily/weekly logged work hours
Average time for ticket resolution
Average customer satisfaction survey score
Etc.

By formulas, I assume you mean database queries. Yes, you can create custom SQL reports to your heart's content. There is no need for a third-party reporting tool. The wizard cannot perform table joins, so you may have to write some custom SQL queries.