Solved

I have to convert the given value into the format " Days:HH:MM:SS  " in SSRS report

Posted on 2011-02-21
1,621 Views
I have to convert the value into    DDD:HH:MM:SS   format.

For example
if I get the value 241 ,   I have to convert it into 10:01:00:00

When > 24 hours, to use number of days

DDD -days
HH -Hours
MM -Minutes
SS -Seconds

How to do it in ssrs expression?!
0
Question by:Manikannan
• 7
• 6

LVL 9

Expert Comment

what is the value 241, is it minutes, days or hours or seconds? based upon that we should write the function
0

Author Comment

Suresh,
Its minutes
0

LVL 9

Expert Comment

declare @mint int   /*  minutes */
set @mint = 3456

Select @mint,
Cast(@mint / 1440 as Varchar) + ':' +
Cast((@mint%1440) / 60 as Varchar) + ':' +
Cast(@mint % 60 as Varchar)
0

Author Comment

Suresh,

I asked it in the SSRS expression , not in the sql stmt.
Is it possible to add in ssrs?!
for ex
0

LVL 9

Expert Comment

yes, same logic you use it in ssrs expression by concatenating it in a expression of a text box
0

Author Comment

How to do that suresh?!

Now in the textbox, i'm displaying the following,

=Last(Fields!I_TIME_ON_GPS.Value)

Can u tell me how to change the "I_TIME_ON_GPS.Value"  value into "DDD:HH:MM:SS"    format
0

LVL 9

Expert Comment

try this in expression

CChar(Fields!I_TIME_ON_GPS.Value/1440) + ":" + CChar((DivRem(Fields!I_TIME_ON_GPS.Value,1440))/60) + ":" + CChar(DivRem(Fields!I_TIME_ON_GPS.Value,60)) + ":00"

the ":00" in last is for seconds, as the number is in minutes, you cannot divide them into seconds
0

Author Comment

No Suresh,

It shows the error as "wrong number of arguments" in the following part

CChar((DivRem(Fields!I_TIME_ON_GPS.Value,1440))/60)

0

LVL 9

Expert Comment

Replace DivRem(Fields!I_TIME_ON_GPS.Value,1440)   to Fields!I_TIME_ON_GPS.Value Mod 1440

and

DivRem(Fields!I_TIME_ON_GPS.Value,60) to Fields!I_TIME_ON_GPS.Value Mod 60
0

Author Comment

Its showing error only.

Is it working in ur side?!
0

LVL 9

Accepted Solution

sureshbabukrish earned 500 total points
it should work , try to use each part at a time and then keep on adding one by one if the earlier one works
0

Assisted Solution

Manikannan earned 0 total points
I use like the following,

Fields!I_TIME_ON_GPS.Value \ 1440  & ":" & Fields!I_TIME_ON_GPS.Value \ 60 & ":" & Fields!I_TIME_ON_GPS.Value mod 60

Its working,  Thanks suresh for your idea.
0

Author Closing Comment

It helped me partially
0

Featured Post

Suggested Solutions

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.