March 29, 2011 at 9:22 am
Hi There,
I am having a small issue when trying to get the max value from a set of data.
Within the table within Reporting Services I have this expression to extract the max value:
=max(floor(Fields!Response_Time.Value / 3600)) & ":" & floor(((Fields!Response_Time.Value Mod 3600) / 60)) & ":" & floor(((Fields!Response_Time.Value Mod 3600) Mod 60))
for some reason it isn't picking up the max value. For example for one analyst the max time should be 223:52:52 but it is stating 223:00:00
Many thanks for any pointers, searching on incorrect max value isn't working.
March 29, 2011 at 9:41 am
Don't use FLOOR in your MAX expression.
To quote BOL:
The FLOOR function returns the largest integer less than or equal to the specified numeric expression.
March 30, 2011 at 5:28 am
Hi Daniel,
Thanks for replying. I did wonder if this might be the problem so I took the floor out but a)this hasn't changed the value and b) my problem with multiple decimal places has come back.
I am wondering if I should perhaps try and find the value using a select statement instead?
March 30, 2011 at 7:17 am
I looked at your query again. This really may be more of a formatting issue. Spend a few minutes Googling SSRS TimeSpan formatting, SSRS Time Formats, etc.. You may find what you need there without all the formulas.
If you do find a formatting solution, please post your solution back here.
March 30, 2011 at 7:32 am
karen.blake (3/30/2011)
Hi Daniel,Thanks for replying. I did wonder if this might be the problem so I took the floor out but a)this hasn't changed the value and b) my problem with multiple decimal places has come back.
I am wondering if I should perhaps try and find the value using a select statement instead?
Back again Karen? 😛
Try it like this: -
=floor(max(Fields!Response_Time.Value) / 3600)) & ":" & floor(((max(Fields!Response_Time.Value) Mod 3600) / 60)) & ":" & floor(((max(Fields!Response_Time.Value) Mod 3600) Mod 60))
-EDIT-
Daniel Bowlin (3/30/2011)
I looked at your query again. This really may be more of a formatting issue. Spend a few minutes Googling SSRS TimeSpan formatting, SSRS Time Formats, etc.. You may find what you need there without all the formulas.If you do find a formatting solution, please post your solution back here.
SSRS has problems when the time-span is greater than 24 hours, so you have to build up your own format.
March 31, 2011 at 8:38 am
Ahhh skcadavre yes it is me, back again. I swear I just cannot get enough of this place and SQL 😛
And as per usual thank you so much it worked first time...how can I ever repay you?
April 1, 2011 at 4:32 am
karen.blake (3/31/2011)
Ahhh skcadavre yes it is me, back again. I swear I just cannot get enough of this place and SQL 😛And as per usual thank you so much it worked first time...how can I ever repay you?
Just glad it works 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply