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