Using a Pivot table in SQL Query

Dashboard Server
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

1 Like
2

Have you configured the connection to the SQL instance? Can you get any query to run?

https://support.squaredup.com/v3/Reference/Procedures/ConfiguringAccessToADatabaseForUseWithTheSQLTile

Is there anything in the log? Open the folder below, refresh your dashboard and wait for it to load, open the latest log. If there’s anything in there relating to SQL data, then you’ll probably find your answer. If not, this ones probably best for support as the product isn’t doing what it should :slight_smile:

C:\inetpub\wwwroot\squaredupv3\transient\log

3

Yes the connection is configured and the query does work in Squaredup as the data from the non-pivot table does show. It is just that the columns that exist in the pivot table lookup do not show in squaredup but it does work in SQL management studio. I checked the squaredup logs and it shows that it executed the SQL and it does not show any errors.

4

Actually, I just got this to work! In a pivot table I must specify the column names in lower case. When I did this, it worked!!!

1 Like
5

It’s always the simplest solution :wink: Glad you’re up and running!