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

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

Answer 1

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)

Answer 2

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)

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

Hi There

How does this sound to you

Please reply after having a look on the graph

Many thanks

Syed

Answer 7

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???

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

Answer 8

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.

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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