Sorting report by custom field

[amybillmayer]

Hi all - need a little help. I've been asked to create a report that shows the number of days requests lived on the backlog prior to project conversion. This is my column code, which works perfectly:

displayname=Days on backlog
textmode=true
valueexpression=ROUND(DATEDIFF({entryDate}, {convertedOpTaskEntryDate}))
valueformat=HTML

THEN, I was asked to sort the report by this column, so I added:
sortOrder=1
sortType=asc

This doesn't work, it just breaks the report. Any suggestions on how to sort by this column?

[JoaquinAv · Accepted solution]

Hi Amy, 

Unfortunately, you cannot sort an expression column in a report. The reason for this is, you are creating the data as you pull the report and therefore are not pointing to data that already exist. To achieve what you want, you will want to create the expression as a calculated field in a custom form that is attached to your requests. This calculated field will already have the data in place and you can pull that field into your report. From the report, you can sort the custom field data. This may be a big lift depending on how many requests you have. If you already have a custom field attached to your requests, it'll be easier, as you can just add the new calculated field to it and recalculate expressions. 

I hope this helps. 

_Joaquin

[amybillmayer]

Thank you, Joaquin! I will give this a try.

[Madalyn_Destafney]

Pretty sure you don’t even need text mode or a calculated field. Try adding a column in your issue report called ‘age’ and that should show number of days before conversion. You should also be able to sort by that column in the advanced options of standard mode. Let me know otherwise!

[JoaquinAv]

Hi Madalyn, 

If I understand "age" (text mode: howOld), this shows the total life span of the Request, entryDate to $$TODAY, not entryDate to convertedOpTaskEntryDate.

I could be total off here though, which I will fully admit could be the case.

[Madalyn_Destafney]

Ah I misread. What you can do here is use issue actual start date to show when it was converted. So I've had an issue entry date column and right next to it issue actual start date so you can see when it was entered vs when it was converted. The 'start' is action taken in the issue, so this works if that is the first thing done with the issue when received.