Solved

SSRS Chart Time along the Y axis

Posted on 2014-12-18
12
521 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
  • 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now