SSRS Chart Time along the Y axis

Are you sure the time is a date/time field. In your screenshot the values are left aligned. It might be the field is a text field. In that case the chart will default to count([time]) as the expression for the value. That would explain why you have a 1 for every item.
To help you solve this we need more information about where the data is coming from. If it is SQL server we need the structure of the tables and the queries. Some more information about the design of the chart would also be nice.

When the value field is a time field it is possible to use is on the y-axis. See the attached report for a sample. You need to change the Data Source of the report to a SQL Server database to use the report. No specific data is needed on the SQL Server.
ChartWithTime.rdl

Hello Nicobo
I have changed my script this morning, so it will show repair longer than 24 hours.

Here is my script;

with x as (
Select [Tool Code], [Asset M/C N°], Sum(DateDiff(second, '0:00:00',[Total Down Time])) as [Total Down Time],Count(*) as OCC
From [dbo].[Acc_Data]
Where [Bay N°] = @Bay
and [Reported Date] between @StartDate and @EndDate
Group by [Tool Code],[Asset M/C N°]
) select [Tool Code], [Asset M/C N°], RIGHT('0' + CAST([Total Down Time] / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(([Total Down Time] / 60) % 60 AS VARCHAR),2)  + ':' +
RIGHT('0' + CAST([Total Down Time] % 60 AS VARCHAR),2) as [Total Down Time (sec)], OCC
from x
Order by RIGHT('0' + CAST([Total Down Time] / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(([Total Down Time] / 60) % 60 AS VARCHAR),2)  + ':' +
RIGHT('0' + CAST([Total Down Time] % 60 AS VARCHAR),2) desc

Select allOpen in new window

Please see two new screen shots;

I have tried to configure as per your example report but cause I have time greater than 24 hours now it does not work.

How do I configure chart so it will show this data.

Regards

SQLSearcher
Time-Chart-2.jpg
Time-Chart-3.jpg

You need to change your time from datetime to number of hours, if you have values over 24. You can use DATEDIFF for this.

Hello Phillip
Can you give me more information please?

Regards

SQLSearcher

Well, at the minute you are using code such as RIGHT('0' + CAST([Total Down Time] / 3600 AS VARCHAR),2).

Therefore, presumably [Total Down Time] is the number of minutes.

So just divide that by 60 and there's the number of hours.

Hi Phillip
How does that help me to be able to put the data in a chart?

Regards

SQLSearcher

I thought you said you wanted it in the graph?

Now you should be able to use the SUM() or AVG() of that figure as the Values.

Hi Phillip
I want the data in a column chart.

I have used a expression to SUM() and then AVG() but still the chart has no data inside it.

Regards

SQLSearcher

As Phillip says the field [Total Down Time (sec)] is a text field. So even though it contains a 'time' SSRS does not recognize this. As far as SSRS is concerned it could be any text string. And all it can do with a text string is count the number of occurrences. That's why it says Count(Total_Down_Time) in the chart design. It looks like the down time is stored as seconds in the database. You can divide this by 3600 to get the number of hours. You'll get 4,5 instead of 4:30. But when you only show the whole hours on the y axis that should not be a problem. You can keep your current  [Total Down Time (sec)] field of course to show in the table. Just add another field to the query that returns the down time as as number so you can use that in your chart.

You can even base your chart on a completely different query. One that returns the individual down times. When you then use a stacked chart you can even see the size of the individual down times. So one bar in the chart can be made up of for example 3 blocks. This can show you that there was 1 long down time and two small ones for example.

dividing this by 3600 would probably get minutes, not hours.

Hi Nicobo
Thank you for your help.

Regards

SQLSearcher