Sort by another field in report view

[EShook]

I am currently using the following bit of code to format System Uptime:

 
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
  24))) + ' d ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
  24))) + ' hrs ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
  60))) + ' mins ' As Uptime,

My question is, is there a way to have the report sort this column by the default tblAssets.Uptime values when the header is clicked? Since I had to convert the ticks away from ints it seems to simply grab highest first digit, which can be very inaccurate. Any help is appreciated!

[Susan_A · Accepted solution]

SQL uses Order By, not Sort By. If you would like to order by the Uptime field within the SQL query, you will need to do so as seen in the first sample query posted here. If you would like to order by the Uptime field within the report results by clicking on the column header, you will need to display the unconverted uptime values within the report results as a separate column/expression.

[EShook]

Yeah, I understand that is my problem. I want to know if there is a way to sort this by a default field. There is no way to just call the tblAssets.Uptime field for sorting purposes? I can and have already set it to sort by this initially but once you sort by anything else it's undone and you have to relaunch the report. I would think there has to be a way to have a field sorted by the values in an associated field, but I am fairly new to SQL so maybe I am just expecting too much. I would expect something like:

Sort [Uptime] by tblAssets.Uptime

or maybe

Order [Uptime] by tblAssets.Uptime

Something similar to the overall report view, but specified for a specific fields operations only.

[RCorbeil]

You're creating a text string there, so if you try to sort on the output you're creating, it will be an ASCII-betical sort (e.g. 1, 10, 11, 12, 2, 20, 21, ...). Unless you include the numeric uptime field to sort on, the only way you'll be able to sort your text string the way you want is if you make sure to zero-pad the converted values, e.g. "005 d 01 hrs 02 mins".