To conclude this particular series on using SQL Report Builder from within Configuration Manager 2012, I will show how to create an action inside the dashboard’s summary report, linking it to the Operating System Details report we created in the previous half of this series.
Previous videos for the dashboard part of the series can be found here:
This session will concentrate on inserting three-color-range guages into the dashboard and configuring them to represent specific defined values. Additionally, a new dataset will be created to define the variables being used.
The SQL query used in this video is shown below:
DECLARE @TOTAL INT, @CLIENT INT, @ACTIVE INT
SELECT
@TOTAL = COUNT(*)
FROM
v_R_System
SELECT
@CLIENT = COUNT(*)
FROM
v_R_System
WHERE
v_R_System.Client0 = 1
SELECT
@ACTIVE = COUNT(*)
FROM
v_R_System
WHERE
v_R_System.Active0 = 1
SELECT @TOTAL AS Total, @CLIENT AS Client, @ACTIVE AS Active
This will be the second half of the series I’m creating on using SQL Report Builder with Configuration Manager 2012. In this part of the series I will show how to build a dashboard using various features.
This first video will demonstrate how to use a COUNT statement in order to summarize query results.
The SQL query used in this video is shown below
SELECT
Operating_System_Name_and0, COUNT(*) AS Qty
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