Solved

SSRS Chart Time along the Y axis

Posted on 2014-12-18
12
695 Views
Last Modified: 2014-12-22
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
0
Comment
Question by:SQLSearcher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 40508630
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
 

Author Comment

by:SQLSearcher
ID: 40508736
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40508809
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SQLSearcher
ID: 40508834
Hello Phillip
Can you give me more information please?

Regards

SQLSearcher
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40508835
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
 

Author Comment

by:SQLSearcher
ID: 40508862
Hi Phillip
How does that help me to be able to put the data in a chart?

Regards

SQLSearcher
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40508908
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
 

Author Comment

by:SQLSearcher
ID: 40508998
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
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 40509076
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40509096
dividing this by 3600 would probably get minutes, not hours.
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 500 total points
ID: 40509591
@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
 

Author Closing Comment

by:SQLSearcher
ID: 40512871
Hi Nicobo
Thank you for your help.

Regards

SQLSearcher
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

732 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