1

I have a SQL query that grabs our SCCM Software Deployment data which uses a pivot table.  The query works but any of the data that is coming from the pivot table does not show up in the SQL tile.  The column is just blank but if I run the query in SQL it works fine.  Does anyone know how to get this to work?

Query:

Select DeploymentName, Available, Deadline,

cast(cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([Successfully installed update(s)], 0) + ISNULL([Failed to install update(s)], 0) + ISNULL([Installing update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Pending system restart], 0) + ISNULL([Downloading update(s)], 0)))*100) as Numeric(10,2)) as varchar(256)) + ‘%’ AS ‘% Compliant’,

[Compliant] AS ‘Compliant’,

[Enforcement state unknown] AS ‘Enforcement state unknown’,

[Failed to install update(s)] AS ‘Failed to install update(s)’,

[Installing update(s)] AS ‘Installing update(s)’,

[Waiting for another installation to complete] AS ‘Waiting for another installation to complete’,

[Pending system restart] AS ‘Pending system restart’,

[Downloading update(s)] AS ‘Downloading update(s)’

From

(select

a.AssignmentName as DeploymentName,

a.StartTime as Available,

a.EnforcementDeadline as Deadline,

sn.StateName as LastEnforcementState,

count(*) as NumberOfComputers

from v_CIAssignment a

join v_AssignmentState_Combined assc

on a.AssignmentID=assc.AssignmentID

join v_StateNames sn

on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
where a.AssignmentName like ‘Microsoft Software Updates%’

group by a.AssignmentName, a.StartTime, a.EnforcementDeadline,

sn.StateName) as PivotData

PIVOT

(

SUM (NumberOfComputers)

FOR LastEnforcementState IN

( [Compliant],

[Enforcement state unknown],

[Successfully installed update(s)],

[Failed to install update(s)],

[Installing update(s)],

[Waiting for another installation to complete],

[Pending system restart],

[Downloading update(s)])

) AS pvt
order by DeploymentName

Robert Auten selected answer