
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-17-2016 01:03 AM
I am currently using the following bit of code to format System 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!
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!
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-21-2016 03:19 PM
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.
3 REPLIES 3
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-21-2016 03:19 PM
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.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-17-2016 05:57 PM
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:
or maybe
Something similar to the overall report view, but specified for a specific fields operations only.
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.
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-17-2016 05:49 PM
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".
