LiveReports is a Target Helpdesk Add-in for those who want to harness the power of creating customised reports tailored to specific requirements.
Whether you are interested in the last 5 requests submitted to the helpdesk, the most frequent client to your helpdesk this month or the top 10 longest outstanding requests in your helpdesk, LiveReports gives you the power to build your dashboard, for live data feedback.
If you do not already have a LiveReports licence, simply contact us for an evaluation licence key for the LiveReports designer. Any LiveReports you create during your evaluation can be kept simply by saving them when trialling the LiveReports designer.
Follow this guide to get started with LiveReports.
1. Login into Target Helpdesk
2. Open LiveReports (click on the toolbar icon 
or go to View | LiveReports from menu)
3. Click on the blue link “Create New LiveReport” in the bottom-left hand corner of the screen.
4. Click on “Advanced” in the top-right. This will show the raw SQL window at the bottom of the screen so you can enter the SQL query directly, should you wish.
5. You can build a report query by manually writing a sql query. Alternatively, you can build a query using the visual designer by dragging report views onto the designer canvas. You will see the designer parses the SQL and graphically displays the query.
6. You can click on the “Data” tab to preview results.
7. Click the blue “Save As” link. Enter a name and click “Save”.
8. Close the save confirmation box and then click the window [X] in the top-right to close the LiveReport designer.
9. You now have your report in the list of available LiveReports. Simply select it from the list and click “Add Grid” or “Add Chart”.
10. When viewing the data in the grid, you can click on “Toggle options” to Print/Email/Schedule/Export the data.
Here are some sample SQL queries to get you started that can be pasted directly into the Advanced “SQL Code” area.
Time log current month
SELECT view_ticket_list.ticket_id, view_ticket_list.title,
view_tm_audit.charged_by_name, view_tm_audit.time_spent,
view_tm_audit.time_spent_units_description, view_tm_audit.chargeable_rate,
view_tm_audit.datestamp, view_tm_audit.chargeable_rate_units_description,
view_tm_audit.rate_name, view_tm_audit.rate
FROM view_tm_audit INNER JOIN
view_ticket_list ON view_ticket_list.system_id = view_tm_audit.system_id AND
view_ticket_list.ticket_id = view_tm_audit.ticket_id
WHERE month(view_tm_audit.datestamp) = month(getdate()) AND
year(view_tm_audit.datestamp) = year(getdate())
Time value for this month
SELECT view_ticket_list.ticket_id, view_ticket_list.title,
view_ticket_list.requestor_name, view_ticket_list.time_spent_minutes,
view_ticket_list.time_spent_hours, view_ticket_list.time_spent_days,
view_ticket_list.time_value, view_ticket_list.materials_value,
view_ticket_list.total_value
FROM view_ticket_list
where month(view_ticket_list.datestamp) = month(getdate())
Tech/Org/Category analysis
SELECT view_tm_audit.ticket_id AS [Ticket Id], view_tm_audit.charged_by_name AS
Chargee, view_tm_audit.time_spent AS [Time Spent],
view_tm_audit.time_spent_units_description AS [Time Spent Units],
view_ticket_list.requestor_organisation AS Organisation,
view_ticket_list.category_subcategory_description AS Category
FROM view_tm_audit INNER JOIN
view_ticket_list ON view_ticket_list.system_id = view_tm_audit.system_id AND
view_ticket_list.ticket_id = view_tm_audit.ticket_id
WHERE month(view_tm_audit.datestamp) = month(getdate()) AND
year(view_tm_audit.datestamp) = year(getdate())
Service Level Agreements expiring next month
SELECT view_sla.sla_id, view_sla.reference, view_sla.expiry_datestamp,
view_sla.contract_cost, view_sla.contract_invoice_reference,
view_sla.start_datestamp, view_sla.on_hold
FROM view_sla
WHERE view_sla.on_hold = ‘false’ AND
view_sla.deleted = ‘false’ AND month(view_sla.expiry_datestamp) =
month(dateadd(m, 1, getdate()))
ORDER BY view_sla.expiry_datestamp
Service Level Agreement value
SELECT view_sla.sla_id, view_sla.reference, view_sla.expiry_datestamp,
view_sla.contract_cost, view_sla.contract_invoice_reference,
view_sla.start_datestamp, view_sla.on_hold
FROM view_sla
WHERE view_sla.on_hold = ‘false’ AND view_sla.deleted = ‘false’
ORDER BY view_sla.contract_cost DESC
Ticket/Time chart
SELECT view_tm_audit.ticket_id, view_ticket_list.title,
view_ticket_list.time_spent_hours, view_tm_audit.datestamp,
view_tm_audit.charged_by_name
FROM view_tm_audit, view_ticket_list
WHERE view_ticket_list.ticket_id = view_tm_audit.ticket_id AND
view_ticket_list.time_spent_hours IS NOT NULL
ORDER BY view_ticket_list.title, view_tm_audit.datestamp DESC
Top 10 highest volumne companies raising requests this month
SELECT TOP 10 view_ticket_list.requestor_organisation AS
[Requestor Organisation], Count(*) AS [Total Tickets]
FROM view_ticket_list
WHERE ’1 ‘ + datename(m, getdate()) + ‘ ‘ + ltrim(str(year(getdate()))) <=
view_ticket_list.datestamp AND view_ticket_list.datestamp <= getdate() AND
view_ticket_list.requestor_organisation <> ”
GROUP BY view_ticket_list.requestor_organisation
ORDER BY [Total Tickets] DESC
New/Updated/Closed tickets by organisation by status
SELECT ‘New’ AS Classification, Count(*) AS Total,
view_ticket_list.requestor_organisation AS Organisation,
view_ticket_list.status AS Status
FROM view_ticket_list
WHERE view_ticket_list.datestamp >= getdate() – 7
GROUP BY view_ticket_list.requestor_organisation, view_ticket_list.status
UNION
SELECT ‘Updated’ AS Classification, Count(*) AS Total,
view_ticket_list.requestor_organisation, view_ticket_list.status
FROM view_ticket_list
WHERE view_ticket_list.last_updated >= getdate() – 7 AND EXISTS(SELECT 1
FROM notes n
WHERE n.ticket_kb_id = view_ticket_list.ticket_id AND n.note_type = ‘ticket’
AND n.note_subtype IN (‘follow-up’, ‘email-out’, ‘response_email’, ‘notes’))
GROUP BY view_ticket_list.requestor_organisation, view_ticket_list.status
UNION
SELECT ‘Closed’ AS Classification, Count(*) AS Total,
view_ticket_list.requestor_organisation, view_ticket_list.status
FROM view_ticket_list
WHERE view_ticket_list.closed_datestamp >= getdate() – 7
GROUP BY view_ticket_list.requestor_organisation, view_ticket_list.status
Service Level Agreement summary with ticket count breakdown for contract period, MTD, YTD, and Quarterly analysis
SELECT view_sla.sla_id AS [SLA #], view_sla.reference AS Reference, CASE
WHEN view_sla.on_hold = 1 THEN ‘On Hold’ ELSE ”
END AS [Contract On Hold?], view_sla.start_datestamp AS Started,
view_sla.expiry_datestamp AS Expires, (SELECT Count(*) FROM ticket
WHERE ticket.deleted = 0 AND ticket.sla_id = view_sla.sla_id) AS
[Total Jobs For This Contract ID], (SELECT Count(*) FROM ticket
WHERE ticket.deleted = 0 AND ticket.sla_id = view_sla.sla_id AND
view_sla.start_datestamp <= ticket.datestamp AND ticket.datestamp <=
view_sla.expiry_datestamp) AS [Total Jobs For Contract Period],
(SELECT Count(*) FROM ticket
WHERE ticket.deleted = 0 AND ticket.sla_id = view_sla.sla_id AND
dateadd(m, -1, getdate()) <= ticket.datestamp AND ticket.datestamp <=
getdate()) AS [1 Month Rolling Jobs For Contract], (SELECT Count(*)
FROM ticket
WHERE ticket.deleted = 0 AND ticket.sla_id = view_sla.sla_id AND
’1 ‘ + datename(m, getdate()) + ‘ ‘ + ltrim(str(year(getdate()))) <=
ticket.datestamp) AS [MTD Jobs], (SELECT Count(*) FROM ticket
WHERE ticket.deleted = 0 AND ticket.sla_id = view_sla.sla_id AND
’1 Jan ‘ + ltrim(str(year(getdate()))) <= ticket.datestamp) AS [YTD Jobs],
(SELECT Count(*) FROM ticket
WHERE ticket.deleted = 0 AND ticket.sla_id = view_sla.sla_id AND
’1 Jan ‘ + ltrim(str(year(getdate()))) <= ticket.datestamp AND
ticket.datestamp <= dateadd(s, -1, ’1 Apr ‘ + ltrim(str(year(getdate())))))
AS [Q1 Jobs], (SELECT Count(*) FROM ticket
WHERE ticket.deleted = 0 AND ticket.sla_id = view_sla.sla_id AND
’1 Apr ‘ + ltrim(str(year(getdate()))) <= ticket.datestamp AND
ticket.datestamp <= dateadd(s, -1, ’1 July ‘ + ltrim(str(year(getdate())))))
AS [Q2 Jobs], (SELECT Count(*) FROM ticket
WHERE ticket.deleted = 0 AND ticket.sla_id = view_sla.sla_id AND
’1 July ‘ + ltrim(str(year(getdate()))) <= ticket.datestamp AND
ticket.datestamp <= dateadd(s, -1, ’1 Oct ‘ + ltrim(str(year(getdate())))))
AS [Q3 Jobs], (SELECT Count(*) FROM ticket
WHERE ticket.deleted = 0 AND ticket.sla_id = view_sla.sla_id AND
’1 Oct ‘ + ltrim(str(year(getdate()))) <= ticket.datestamp AND
ticket.datestamp <= dateadd(s, -1, ’1 Jan ‘ + ltrim(str(year(getdate()) +
1)))) AS [Q4 Jobs], view_sla.target AS [Target %], view_sla.contract_cost AS
Cost, view_sla.contract_invoice_reference AS [Inv Ref],
view_sla.contract_invoice_datestamp AS [Inv Date],
view_sla.contract_sent_datestamp AS [Contract Sent],
view_sla.contract_received_datestamp AS [Contract Recvd Date],
view_sla.contract_processed_datestamp AS [Contract Processed Date],
view_sla.last_updated_by_name AS [Last update by],
view_sla.contract_invoiced_by_name AS [Invoiced by],
view_sla.contract_sent_by_name AS [Sent by],
view_sla.contract_received_by_name AS [Recvd by],
view_sla.contract_processed_by_name AS [Processed by]
FROM view_sla
WHERE view_sla.deleted = 0
ORDER BY view_sla.sla_id, view_sla.reference
Time sheet for month
SELECT view_ticket_list.ticket_id, view_ticket_list.title,
view_ticket_list.requestor_name, view_ticket_list.time_spent_minutes,
view_ticket_list.time_spent_hours, view_ticket_list.time_spent_days,
view_ticket_list.time_value, view_ticket_list.materials_value,
view_ticket_list.total_value
FROM view_ticket_list
WHERE month(view_ticket_list.datestamp) = month(getdate())
Find out more on Target Helpdesk’s powerful reporting
