SSRS Chart Time along the Y axis

Hello Experts Exchange
I have a table of data that has a time field that I want to have on a chart, but when I try to assign the time to a chart the column charts are all the same height.

Please see my screen shot for more details.  You will see the second from the right column is Time field that I am after in the chart.

How do I get the time to plot correctly in my chart?

Regards

SQLSearcher
Time-Chart.jpg
SQLSearcherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nico BontenbalCommented:
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
0
SQLSearcherAuthor Commented:
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

Open 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
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You need to change your time from datetime to number of hours, if you have values over 24. You can use DATEDIFF for this.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SQLSearcherAuthor Commented:
Hello Phillip
Can you give me more information please?

Regards

SQLSearcher
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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.
0
SQLSearcherAuthor Commented:
Hi Phillip
How does that help me to be able to put the data in a chart?

Regards

SQLSearcher
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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.
0
SQLSearcherAuthor Commented:
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
0
Nico BontenbalCommented:
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.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
dividing this by 3600 would probably get minutes, not hours.
0
Nico BontenbalCommented:
@Phillip. Are you sure? I assume the value is seconds. When I divide it by 60 I get the minutes, when I divide it by 3600 I get the hours.

But let's start at the beginning. There is a table [Acc_Data]. This table has a column [Total Down Time]. Since this column is used like this: Sum(DateDiff(second, '0:00:00',[Total Down Time])). I assume the [Total Down Time] column is a time (or datetime) field that contains the duration of the Down Time. So a (date)time of 7:23 would mean the Down Time was 7 hours and 23 minutes. The expression Sum(DateDiff(second, '0:00:00',[Total Down Time])) returns the total number of seconds of the downtime. The expression RIGHT('0' + CAST([Total Down Time] / 3600 ..... CAST([Total Down Time] % 60 AS VARCHAR),2) is used to convert these seconds to hh:mm:ss. But the result of that expression is a string and this can't be used as a value in a chart. So we need another field in the result ([Total Down Time] / 3600.0 as [Total Down Time hour] for example). To use in the chart.

What I would do is this scenario is use the query:
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

Open in new window

As the source of my report. If your users are like mine the next thing they want is to be able to 'zoom in' on a Tool to see the individual down times. You can do the summing and the grouping in SSRS as well and that way you'll have the individual records available as well.
But that's just a suggestion. It also possible to do the grouping in the query.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQLSearcherAuthor Commented:
Hi Nicobo
Thank you for your help.

Regards

SQLSearcher
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.