timeserial Visual Studio Report Designer

[morpheusdreams]

Firstly I am not sure if this is the right forum, apologies if it is not.

From tSQL dataset we have a value which comes out as a total number of seconds.

We need to display this as HH:mm. we thought we had it cracked when we found:

=Format(Timeserial(0,0,Fields!TotalTime),"HH:mm")

However this resets to 0 when the time goes over 24 hours. We have googled this to hell and back and have not come up with any answers, please help.

 

[gmmastros]

Do you want to 'fix' this problem on the front end or in the database?

If you fix this in the DB, then the following code may be useful.

Declare @Seconds Int
Set @Seconds = 10000000

Select Case When @Seconds > 86400 
            Then Convert(varchar(10), @Seconds / 86400) + 'd ' 
            Else ''
            End + Convert(VarChar(10), DateAdd(s, @Seconds % 86400, 0), 108)

If you want to fix this in the front end, then the same 'thought process' would apply.

First, divide the number of seconds by 86400. The 'whole number' part represents the number of days. Then mod your value by 86400 to get the remainder and format that to hours and minutes.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[morpheusdreams]

so there is no way of doing this without adding a user defined function in?

 

[AlexCuse]

This gets tricky, because SSRS does not support the Mod function (AFAIK). Here is an expression that should work:

=floor(fields!seconds.value / 3600).ToString() & ":" 
	& floor((fields!seconds.Value - (floor(fields!seconds.value / 3600) * 3600))/60).ToString()

If you use a calculation like this in a lot of reports, I would recommend you put it into a .dll (which would allow you to write the function in VB.net or C#, and make it much simpler) and deploy it to your report server.

Hope this helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[AlexCuse]

Also, for SSRS questions this is the place to go: forum1462

If your problem was in actually generating your dataset, then this would be the right place.

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page