Solved

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

Posted on 2011-02-21
13
1,671 Views
Last Modified: 2012-05-11
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
Comment
Question by:Manikannan
  • 7
  • 6
13 Comments
 
LVL 9

Expert Comment

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

Author Comment

by:Manikannan
ID: 34950657
Suresh,
 Its minutes
0
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 34951102
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Manikannan
ID: 34951139
Suresh,

         I asked it in the SSRS expression , not in the sql stmt.
Is it possible to add in ssrs?!
  for ex
  DATEADD("n",Parameters!NoOfMinute.Value)
0
 
LVL 9

Expert Comment

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

Author Comment

by:Manikannan
ID: 34951265
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

by:sureshbabukrish
ID: 34951376
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

by:Manikannan
ID: 34951485
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

by:sureshbabukrish
ID: 34951578
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

by:Manikannan
ID: 34951673
Its showing error only.

Is it working in ur side?!
0
 
LVL 9

Accepted Solution

by:
sureshbabukrish earned 500 total points
ID: 34952361
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

by:Manikannan
Manikannan earned 0 total points
ID: 34958157
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

by:Manikannan
ID: 34990907
It helped me partially
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Data source in SSRS 2 51
SSRS Reports - get rid of blank columns 3 65
SQL 2012 R2 Express report problem 2 87
SSRS: My Data Source no longer has any dependent items ??? 2 33
Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Introduction: This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes. It demonstrates how to build a report using SQL Ser…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question