We are using below query to check SQL Server scheduled job status on daily basis and we have created SSRS report using below query.
Now we have to do colour coding for the column LastRunSttaus using SSRS expressions.
1. when job Failed : Entire row should be coloured with RED colour.
2. when job Succeeded : Entire row should be coloured with GREEN colour.
3. when job Running : Entire row should be coloured with YELLOw colour.
we are using SQL Server 2008 and 2012 technology in our environment.
Please advise how to write SSRS expressions for above scenarios.
SELECT @@SERVERNAME As ServerName, [sJOB].[name] AS [JobName] , CASE WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL ELSE CAST( CAST([sJOBH].[run_date] AS CHAR(8)) + ' ' + STUFF( STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS DATETIME) END AS [LastRunDateTime] , CASE [sJOBH].[run_status] WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'Running' -- In Progress END AS [LastRunStatus] , STUFF( STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS [LastRunDuration (HH:MM:SS)] , [sJOBH].[message] AS [LastRunStatusMessage] , CASE [sJOBSCH].[NextRunDate] WHEN 0 THEN NULL ELSE CAST( CAST([sJOBSCH].[NextRunDate] AS CHAR(8)) + ' ' + STUFF( STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS DATETIME) END AS [NextRunDateTime]FROM [msdb].[dbo].[sysjobs] AS [sJOB] LEFT JOIN ( SELECT [job_id] , MIN([next_run_date]) AS [NextRunDate] , MIN([next_run_time]) AS [NextRunTime] FROM [msdb].[dbo].[sysjobschedules] GROUP BY [job_id] ) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id] LEFT JOIN ( SELECT [job_id] , [run_date] , [run_time] , [run_status] , [run_duration] , [message] , ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber FROM [msdb].[dbo].[sysjobhistory] WHERE [step_id] = 0 ) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id] AND [sJOBH].[RowNumber] = 1ORDER BY [JobName]