locked
Convert Seconds to dd:hh:mm:ss on the y-axis RRS feed

  • Question

  • Hi,

    I need to create a report where the y-axis is displaying the min/max value and the interval in dd:hh:mm:ss format rather than seconds.

    Assuming my seconds range from  so instead of seeing this 53 to 2790, instead of seeing this

    3000

    2500

    1500

    1000

    500

    0

    I want to see

    00:00:50:00

    00:00:41:40

    00:00:25:00

    00:00:16:40

    00:00:08:20

    00:00:00:00

    Can this be achieve in anyway? I've been googling for hours but in vain?

    Many Thanks.

    Thierry

    Monday, June 25, 2012 2:04 AM

Answers

  • Hi Thierry,

    I was trying something similar and in my experience a datetime isn't treated as an interval by ssrs charts (scale breaks don't work either). I don't think you'll be able to go beyond 24hrs using a datetime label because there's no 0th day. And any dataset that exceeds 24x60x60 will limit at 24hrs.. My dataset would never exceed 24 hrs so this isn't a problem for me..

    To get something like this:

    The y-axis is H:mm, but can be formatted into HH:mm:ss

    Step 1. you need to have the data from your dataset be shown as a datetime, so set your chart to use something like: =DateAdd("s",Fields!field.Value,"1-1-1900") as the sum value or do this in the source tsql.

    Step 2. click on the y axis and goto vertical axis properties.

    i have always include 0 ticked

    my minimum is an expression: ="1-1-1900"

    Max is =DateAdd("s",Max(Fields!field.Value, "dataset"),"1-1-1900")

    Number format is set to HH:mm:ss


    Friday, June 29, 2012 1:24 AM
  • Hi There

    Thanks for your posting.

    DateAdd("s",Max(Fields!field.Value, "dataset"),"1-1-1900")

    In this above expression dataset is the name of your dataset and please make sure this is a case sensitive so you have to put exact case as it is defined in your dataset name for example(DataSet1 etc)

    I hope this will help

    Many thanks

    Syed Qazafi Anjum


    Sunday, July 1, 2012 10:13 PM

All replies

  • This expression should solve your problem:

    =Int(Sum(Fields!DurationSec.Value) / 60) & “:” & Right(“0″ & (Sum(Fields!DurationSec.Value) – (Int(Sum(Fields!DurationSec.Value) / 60) * 60)), 2)

    Monday, June 25, 2012 6:11 AM
  • Hi,

    You can create a custom Code for this and then use that function in column definition. Below is the custom code

    Function ConvertToDateString(seconds As Integer) As String
        Dim d As Integer
        Dim h As Integer
        Dim m As Integer
        Dim s As Integer
        Dim returenStr As string
        If (seconds >= 86400) Then
            d = seconds \ 86400
            seconds = seconds Mod 86400
        End If
        If (seconds >= 3600) Then
            h = seconds \ 3600
            seconds = seconds Mod 3600
        End If
        If (seconds >= 60) Then
            m = seconds \ 60
            seconds = seconds Mod 60
        End If
        s = seconds
                
        returenStr = d & ":" & h & ":" & m & ":" & s
        return returenStr
    End Function

    To add custom code, open report peroperties and go to code tabe and just copy above code there.

    Now, In report designer, you need to add below code

    =Code.ConvertToDateString(Fields!time.Value)
    Please note that in ablove code, you need to replace Fields!time.Value, with filed name in your dataset.

    - Chintak (My Blog)

    Monday, June 25, 2012 6:49 AM
  • Hi,

    Thanks for getting back to me.

    Hi Irusul,

    I've tried your suggestion i.e.

    =Int(Sum(Fields!DurationSec.Value) / 60) & “:” & Right(“0″ & (Sum(Fields!DurationSec.Value) – (Int(Sum(Fields!DurationSec.Value) / 60) * 60)), 2)

    But there was a problem with it and while I thik I fixed it, it still failed to give me the proper values in the y-axis. This what I ended up with

    =Int(Sum(Fields!Seconds.Value) / 60) &

    ":" & Right("0" & (Sum(Fields!Seconds.Value)- ((Sum(Fields!Seconds.Value) / 60) * 60)), 2) and the values were displayed as follows:

    56: 3000

    56: 2500

    56: 2000

    56: 1500

    56: 1000

    56: 500

    56: 00

    Hi Chintak,

    I've also tried your solution and was also hopeful when you mentioned the function which I wasn't arware you could do (newbie with reporting :-)), but unfortunately, it didn't work either for the y-axis. Note that I did manage to get it to work for the column, but I don't need it for the Column as I'm already doing the formatting of the seconds to Time via a function in SQL Server and this works great.

    I added the function as you suggested and I'm calling the following by setting the "Number" format with a "Custom" i.e.

    =Code.ConvertToDateString(Fields!Seconds.Value)

    and the interval is displayed as follows:

    30:0:46:30

    25:0:46:30

    20:0:46:30

    15:0:46:30

    10:0:46:30

    5:0:46:30

    0:0:46:30

    Strangely, 46:30 is the appropriate value for the Max value i.e 2790 seconds, but it is somehow re-using the largest value over and over again and then the "day" seems to be completely off... When I was playing around with this myself, I got something similar without the day but where all my intervals were displayed as 46:30...

    Any other suggestions?

    Thanks.

    Thierry

    Tuesday, June 26, 2012 11:01 PM
  • Hi There

    Thanks for your posting. Please have a look on this thread that might help you

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/74aaa6b3-1768-42dc-964f-ac687554bbd6/

    Many thanks

    Syed

    Tuesday, June 26, 2012 11:09 PM
  • Hi Syed,

    I've had a look at it but this is no good once again. This requires specific interval to be set.

    Also in regards to using a function to convert, I've figured out, well part of the problem. When the y-axis displays

    30:0:46:30

    25:0:46:30

    20:0:46:30

    15:0:46:30

    10:0:46:30

    5:0:46:30

    0:0:46:30

    I've just realized that the non-formatted data is displayed as well the formatted data... This is insane!! Based on the original provided data,

    30:0:46:30 - represents 3000 46:30

    25:0:46:30 - represents 2500 46:60

    etc...

    Now this is a crazy behaviour... By specificy a function in the Number|Custom format it does the following:

    1) Uses both the seconds value (i.e. 3000/2500/2000/etc...) and

    2) Uses only the max value for all intervals i.e. 46:30, on every interval.

    This is the function I'm calling for the y-axis

    =Code.ConvertToDateString(Fields!Seconds.Value)

    But something striked me here... Why am I passing the "Seconds" to format to interval? Surely I should be trying to conver the interval rather than the actual seconds?

    This is driving me nuts!! Here I thought It would be nice and easy to set a min/max seconds, format it to a time and let the interval be detected automically by using the min/max seconds and the format these intervals to time as well... Boy, was I WRONG!!

    I hope one of you will still be able to provide me with a solution.

    Thanks again for all the feedback and help.

    T.

    Wednesday, June 27, 2012 1:50 AM
  • Hi There

    How does this sound to you

    Please reply after having a look on the graph

    Many thanks

    Syed

    Wednesday, June 27, 2012 3:26 AM
  • Hi,

    I see that my last reply never got posted!! Sorry about that!

    First thank you Syed for taking the time to help... Second, unfortunately this is what I had already implemented but I'm not happy with this solution as while it is ok when 5 or 6 columns are displayed, there will be reports that could potentially have 20 or 30 columns required which would make the report unreable.

    I'm still hoping I will find a solution, but still no luck.

    When applying the "custom" function to the y-axis and using the table as per snapshot, you can see that I'm displaying the time above each columns and it is displayed correctly but the y-axi is still not behaving correctly. There are 2 problems:

    1. It appears to be displaying the interval (i.e. 0, 500, 1000, 1500, 2000, 2500, 3000) as part of the formatted string??

    2. It is re-using the last value (i.e. 00:46:30) for all interval???

    For example,

    the interval 3000, displays as 03:00:46:30

    the interval 2500, displays as 02:50:46:30

    See snapshot for all the intervals and the repetition of the last value of my table (i.e. 46:30) for all intervals???

    Report and table

    Sorry for the corrupt image but I first uploaded a non-corrupted image but I felt it was just too small... I don't know what the story is in regards to uploading image in msdn forum, but there is a obviously a problem as I've tried 3 or 4 apps to generate my jpg and it just won't let me upload a file which is only 600x240... Very strange!

    Anyway, I hope the above may help you in trying to resolve my problem.

    Thanks.

    Thierry


    Thursday, June 28, 2012 11:41 PM
  • Hi There

    Thanks for your posting again. The above post by me is just a work around for you as You might know that most chart types require numeric values along the value axis, which is typically the y-axis, in order to draw correctly. Currently, the value axis cannot display string data type. This is actually  by design.

    If you have any questions please do ask.

    Many thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Friday, June 29, 2012 1:19 AM
  • Hi Thierry,

    I was trying something similar and in my experience a datetime isn't treated as an interval by ssrs charts (scale breaks don't work either). I don't think you'll be able to go beyond 24hrs using a datetime label because there's no 0th day. And any dataset that exceeds 24x60x60 will limit at 24hrs.. My dataset would never exceed 24 hrs so this isn't a problem for me..

    To get something like this:

    The y-axis is H:mm, but can be formatted into HH:mm:ss

    Step 1. you need to have the data from your dataset be shown as a datetime, so set your chart to use something like: =DateAdd("s",Fields!field.Value,"1-1-1900") as the sum value or do this in the source tsql.

    Step 2. click on the y axis and goto vertical axis properties.

    i have always include 0 ticked

    my minimum is an expression: ="1-1-1900"

    Max is =DateAdd("s",Max(Fields!field.Value, "dataset"),"1-1-1900")

    Number format is set to HH:mm:ss


    Friday, June 29, 2012 1:24 AM
  • Hi Jakubk,

    I've tried what you suggested but I'm getting an error when trying to preview the report. I've followed step 1 & 2 but no luck. The error I'm getting is:

    The definition of the report <reportname> is invalid. The ValueAxis_Primary.Maximum expression of Chart1 has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant equal to either the name of a containing group, the name of a data region or the name of a dataset.

    I've played around with the settings but I'm not quite sure how to fix the error... Any problems?

    Thanks.

    Thierry

    Sunday, July 1, 2012 9:57 PM
  • Hi There

    Thanks for your posting.

    DateAdd("s",Max(Fields!field.Value, "dataset"),"1-1-1900")

    In this above expression dataset is the name of your dataset and please make sure this is a case sensitive so you have to put exact case as it is defined in your dataset name for example(DataSet1 etc)

    I hope this will help

    Many thanks

    Syed Qazafi Anjum


    Sunday, July 1, 2012 10:13 PM
  • Hi,

    Thank both of you for the replies... It is exactly what I want... Such a shame it won't support days... I'll just have to accept that for reports such as weekly, monthly or annually won't include a chart. Such a shame.

    Thank you once again.

    Regards,

    Thierry

    Sunday, July 1, 2012 11:30 PM