Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Service Desk - Sorting Reports by multiple requirements

Created: 18 Jul 2012 | 7 comments

 

Hey guys, just looking for a little help with reporting as I can't seem to figure this out...

 

Can you tell me if it’s possible in a report to sort by priority AND date created?

 

Example:

 

I currently have a report that shows tickets in order of priority (highest priority being at the top, lowest at the bottom)

Does anyone know if it’s possible to amend the report so that it sorts by priority first then by created date. So what I mean is if you look at all the “High” priority tickets the created dates are in no particular order so I’d like them to show in order of created date also from earliest date highest like below:

 

ID

Priority

Status

Subject

Requester

Created

IM-212655

Urgent

Open

Card Payments Failed

Glasgow Store

25/06/2012

IM-212689

Urgent

Open

Broadband Down

Dover Store

28/06/2012

IM-212783

Urgent

Open

Broadband Down

Nottingham Store

01/07/2012

IM-212984

High

Open

Unable to open anything

London Store

30/06/2012

IM-212345

High

Open

Unable to print anything

Glasgow 2 Store

01/07/2012

IM-213899

Normal

Open

New keyboard required

Natalie Williams

27/06/2012

IM-212760

Normal

Open

Phone Replacement

Mark Smith

30/06/2012

IM-212654

Minor

Open

Buddy Phone Faulty

Belfast Store

25/06/2012

IM-213444

Minor

Open

Excel reinstallation

Hayley Wilson

26/06/2012

IM-234221

Low

Open

PC move

London Store

30/06/2012

IM-222111

Low

Open

Equipment move

Daniel Jackson

01/07/2012

 

 

Hope that makes sense…I’ve had a good look in report options etc but can’t figure it out. I guess it will probably be something simple.

Thanks for any help anyone can offer

 

Comments 7 CommentsJump to latest comment

nazareth's picture

I know that you can do it by editing the report's SQL. I would copy the SQL into a new report, then edit the "order by" line at the bottom. The syntax is somewhat self explanatory. See the example below;

order by

    [Incident.Priority] asc,  [Process.ProcessStarted] desc

 
This will sort the report by the priority in ascending order, then by the date is was created in descending order. To sort by start date, then priority, change the order;
 
[Process.ProcessStarted] desc, [Incident.Priority] asc
 
"asc" and "desc" determine if it is sorted in ascending or descending order, respectively. 
 
Unfortunatly there is not a way to sort by multiple fields without creating a report from SQL. I hope this helps.
waddo2012's picture

Hi nazareth, thank you for your reply. Could you confirm the easiest way to edit the SQL of reports? 

Thanks

nazareth's picture

You can't edit the SQL in a report built by using Symantec's report builder, at least not that I know of. Once you have the report the way you want it, grab the SQL by going to the "Options" tab, and clicking on "Display SQL" at the bottom:

You will see the SQL like this:

 

Highlight and copy all of this into a new report as follows:

Create a new report as normal, except choose "Sql Query" in the "Sql" folder instead of anything else:

Paste the SQL that you copied from your report earlier into the window that opens:

Edit the last line to add the criteria that you want to sort by, in ascending or descending order, separated by a comma. Then hit OK. I think you can take it from there. I hope this helps you out :) Feel free to ask any more questions.

ReportSQL.PNG SQLWindow.PNG NewSqlReport.PNG SQLPaste.PNG
waddo2012's picture

Hi nazareth, thank you for explaining this...it worked however it's missing the Process ID section where you can just click a link to the ticket which opens it up (as already mentioned)

snm1502's picture

Hi,

By following above, if we have Process Actions (This enables a hyperlink to open respective Process in the ID column) checked and links appeared in Process IDs, we would loose the same.

Is there a way we can retain the Process Actions in SQL reports as well.

 

Thanks

snm1502

nazareth's picture

Unfortunately, as-is, no. However, you might try this procedure.

http://www.symantec.com/connect/articles/using-custom-sql-servicedesk-report-builder-and-still-use-parameters

It seems to have worked for the people on this post. I have not tried it, but I think that I will as soon as I get some free time. Let me know if it works for you. I will post back if it work for me also.

snm1502's picture

I guess, this should work... I got some similar instructions in another post as well for a different request and there as well it worked for other users...

http://www.symantec.com/connect/forums/last-posted-date-reporting-service-desk

I would be testing it in sometime next week... will post back if it works for me or not...