ServiceDesk 7.1 Aging Report

[Migration User]

I need to create the following reports

1.  Open Tickets - running counts - daily select Categories

2.  Aging Ticket Count - tickets opened 0-2-days, 2-7 days, 1 week-3-weeks, 4 weeks +

I have been trying to create these in SQL Builder and IT Analytics, but cant seem to get the correct syntax down.

Wondering if anyone else has been down this road and can share any insight.

thanks

 

Steve

 

[Justin Dybedahl]

Correct me if I'm wrong, but you want the SQL code for these reports so you can run them outside of the ServiceDesk console?

[Migration User]

@Justin -  SQL works - I have been using IT Analytics and Trying to use the SQL Report builder so I can plug the reports in for the VP to run.

The SD reporting tool has been _less than friendly_ ;-)

I'm willing to try either though.

steve

[Wynfair]

I'm also interested in reporting on Aging Ticket Count, but haven't found a solution yet. I've mainly been looking at IT Analytics.

[bcason]

The attached file is a 7.1 Report export. Rename the file to .report and import to your environment.

It approximates what you are asking for. The Option tab in the Report editor allows for Grouping.

You get COUNT(*) for free when you start grouping columns.
 

[bcason]

Rename file to 'Age+Test.report' and Import.
 

Attachment(s)

Age+Test_0.xml   46 KB 1 version

[Wynfair]

I downloaded, renamed and imported the attachment and receive "Incorrect setup fpr report" message when I try to open it. When I edit it, no Data Sources are selected.

[bcason]

I will guess that my environment is not the same version. I've attached two screen shots that show the construct of the report.
 

[Wynfair]

Excellent. I was able to contruct the report. In the original post it was requested to have this report grouped by time periods. This would make the report much easier to read. Is this possible? Example Tickets 0-7 days, > 1 week, > 2 weeks, > 3 weeks, > 1 month, > 2 months, etc.

[bcason]

The Report Builder is not capable of grouping as you have described. The attached screen shot shows that you can define 'Older Than' when the 'Age' source is selected.

[WDRAIN1]

I was able create a ServiceDesk Age Report that Includes Process Actions setting working and links to the processes into the report:

the link below explains the process https://www-secure.symantec.com/connect/articles/using-custom-sql-servicedesk-report-builder-and-still-use-parameters

 

The report is expandable and include working process actions links to the ticket.

The steps are documented in the above link.

SQL query for the for the Database view is below...

CREATE view [dbo].[IMTicketAge] as select im.SessionId , CASE WHEN ISNULL(IM.[CurrentlyAssignedQueueName], '') <> '' THEN IM.[CurrentlyAssignedQueueName] WHEN ISNULL(IM.[Owner], '') <> '' THEN u.DisplayName END AS AssignedTo ,CASE WHEN datediff(d,rp.ProcessStarted,getdate()) < 7 THEN '< 7 Days' WHEN datediff(d,rp.ProcessStarted,getdate()) >= 7 and datediff(d,rp.ProcessStarted,getdate()) <= 14 THEN '7 to 14 Days' WHEN datediff(d,rp.ProcessStarted,getdate()) >= 15 and datediff(d,rp.ProcessStarted,getdate()) <= 30 THEN '15 to 30 Days' WHEN datediff(d,rp.ProcessStarted,getdate()) >= 30 and datediff(d,rp.ProcessStarted,getdate()) <= 60 THEN '31 to 60 Days' WHEN datediff(d,rp.ProcessStarted,getdate()) >= 60 and datediff(d,rp.ProcessStarted,getdate()) <= 90 THEN '61 to 90 Days' WHEN datediff(d,rp.ProcessStarted,getdate()) >= 90 and datediff(d,rp.ProcessStarted,getdate()) <= 180 THEN '90 to 180 Days' WHEN datediff(d,rp.ProcessStarted,getdate()) >= 180 THEN '> 180 Days' else 'other' end as [TicketAge] from ImIncidentTicket im join [User] u on u.PrimaryEmail = im.Owner join ReportProcess rp on rp.SessionID = im.SessionId GO

The output of the Sql query includes the sessionid, AssignedTo and TicketAge from the query above.

The profile definition setting accessed from the ServiceDesk portal page at Admin | Data | List & Profiles | Add Profile Definition (Existing Table) is shown below...

 

The highlighted overview in the report creation for ServiceDesk Age is show below...

 

 

I hope this helps in ServiceDesk report creation limitations