SSRS Plot an hourly graph

Question

I am trying to generate an area graph with quantity grouped by hours. My dataset query is as below:

SELECT
   FORMAT(DATEADD(hh,DATEPART(hour, TRANDATETIME),'00:00'),'HH:mm') AS SalesByHour,
   SUM(TQty) as SalesQty
FROM
   [RC_BACK].[dbo].[TRAN]
WHERE TRANCODE = 'SA'
GROUP BY FORMAT(DATEADD(hh,DATEPART(hour, TRANDATETIME),'00:00'),'HH:mm')

and I got the results with something like below:

SalesbyHour	SalesQty
14:00	200
17:00	5000
21:00	8000
23:00	20000

I want to generate a graph that includes the hours that have no quantity and would have 24 hours with 1 hour interval in the horizontal axis.

This is what I've tried so far "Horizontal axis properties.." -> "Scalar" -> "Minimum: =TimeValue("00:00") Maximum: =TimeValue("23:59") Interval:1 Interval Type: Hours" -> "Number" -> "Category: Time Type: 13:30" -> "Major Tick Marks" -> "Interval Type: Hours" and I managed to get the horizontal axis right but the values are not plotting on the graph in preview as below:

The values should have the same data type as the x-axis label but it is not plotted and I would like to have the hours with no quantity plotted with 0 on the graph as well. Or is it possible to fill in the remaining hours with 0 in SalesQty through query? I have look through a lot of questions and answers but still no luck yet.

Answer 1

This should work based on what you have now, All I've done is create a table of the hours and always returned the hour from that table even if there is no matching value in your original table. This could easily be improved but as I don't know the structure of your tables, this method was the easiest.

DECLARE @hours TABLE (hr as varchar(5))
INSERT INTO @hours VALUES 
    ('00:00'), ('01:00'), ('02:00'), ('03:00'), ('04:00'), ('05:00')
  , ('06:00'), ('07:00'), ('08:00'), ('09:00'), ('10:00'), ('11:00')
  , ('12:00'), ('13:00'), ('14:00'), ('15:00'), ('16:00'), ('17:00')
  , ('18:00'), ('19:00'), ('20:00'), ('21:00'), ('22:00'), ('23:00')

SELECT h.hr as SalesByHour, ISNULL(d.SalesQty, 0) as SalesQty
    FROM @hours h 
    LEFT JOIN 
        (
         SELECT
                FORMAT(DATEADD(hh,DATEPART(hour, TRANDATETIME),'00:00'),'HH:mm') AS SalesByHour,
                SUM(TQty) as SalesQty
            FROM [RC_BACK].[dbo].[TRAN]
            WHERE TRANCODE = 'SA'
            GROUP BY FORMAT(DATEADD(hh,DATEPART(hour, TRANDATETIME),'00:00'),'HH:mm')
        ) d ON h.hr = d.SalesByHour 

However, I would probably avoid doing all the formatting of time in your query and just pass an actual time, that way the time properties on the axis intervals would work as intended. As you are almost there it might not be worth the effort, but worth bearing in mind in the future.