24 Hour X Axes

Luke MacDonald 21 Reputation points
2022-10-12T19:09:49.297+00:00

There is a tool called SQLJobVis that I we really like and I want to make a SSRS report that shows similar information. I do not think anyone supports the SQLJobVis tool anymore. Does anyone know of were I might find out how to make the bottom axes, a time of day range from 12:00AM - 11:59 PM.

This is my starting query, but may need to get end time of job. That's not in the tables I'm joining though.
SELECT sj.name,
dbo.agent_datetime(sh.run_date,sh.run_time) run_datetime,
-- STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
CASE WHEN sh.run_duration > 235959 THEN
CAST((CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT)
/ 24
) AS VARCHAR) + '.'
+ RIGHT('00'
+ CAST(CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT)
% 24 AS VARCHAR), 2) + ':'
+ STUFF(CAST(RIGHT(CAST(sh.run_duration AS VARCHAR), 4) AS VARCHAR(6)), 3, 0, ':')
ELSE STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
END AS 'LastRunDuration (d.HH:MM:SS) '
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sh
ON sj.job_id = sh.job_id AND sh.step_id = 0
WHERE sh.run_date = 20221012

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,826 questions
{count} votes

1 answer

Sort by: Most helpful
  1. AniyaTang-MSFT 12,331 Reputation points Microsoft Vendor
    2022-10-13T03:07:59.53+00:00

    Hi @Luke MacDonald
    Do you mean you want to set the x-axis to hourly intervals? Then you can do this:

    1. Right-click on the horizontal axis and select Horizontal Axis Properties;
    2. In the horizontal axis properties, select "Number", select "Time" in the category, and then select the time format you want;
      249924-1.png
    3. In the "Axis Options" pane, set the axis type to "Scalar", the interval to 1, the interval type to "Hours", and the minimum set expression to "=TimeValue("00:00:00") ", the maximum value setting expression is "=TimeValue("23:59:00")".
      249902-2.png
      Preview:
      249864-3.png
      For more details, you can refer to this link: format-axis-labels-as-dates-or-currencies-report-builder-and-ssrs
      Best regards,
      Aniya
    0 comments No comments