I’m continuing the series on SSRS Reporting within Configuration Manager 2012 and building on the previously create report outlined in
CM 2012 – REPORTING – CREATE A TABLE REPORT USING SSRS
CM 2012 – REPORTING – CREATING MEANINGFUL RESULTS WITH SQL CASE STATEMENTS
CM 2012 – REPORTING – NARROWING RESULTS WITH PARAMETERS
We will add an indicator “light” which has the three conditions of green, yellow, and red, to serve as visual queues so that a person reviewing the report can quickly identify troublesome systems. This will complete the work on this specific report and it will be used as a drill-down destination for a future exercise.
The SQL query used in this video is shown below:
SELECT
v_R_System.Name0,
V_R_System.Operating_System_Name_and0,
CASE
WHEN v_R_System.Client0=1 THEN ‘YES’
ELSE
‘NO’
END AS Client0,
CASE
WHEN v_R_System.Active0=1 THEN ‘YES’
ELSE
‘NO’
END AS Active0,
CASE
WHEN v_R_System.Client0+v_R_System.Active0 IS NULL THEN 0
ELSE Client0 + Active0
END AS Status
FROM
v_R_System
WHERE
V_R_System.Operating_System_Name_and0 = @OSName
ORDER BY
V_R_System.Operating_System_Name_and0,
v_R_System.Name0