Is there a way to use Common Table expressions in my custom SQL reports? It does not appear so, but maybe I'm missing something. I'm trying something like this, which works in SQL management studio, but not when adding the query to the report.
WITH Counts ( C )
AS ( SELECT COUNT(*) C
FROM ( ( ( Nodes
INNER JOIN Interfaces ON ( Nodes.NodeID = Interfaces.NodeID )
)
INNER JOIN CustomPollerAssignment ON ( Nodes.NodeID = CustomPollerAssignment.NodeID
AND Interfaces.InterfaceID = CustomPollerAssignment.InterfaceID
)
)
INNER JOIN CustomPollers ON ( CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID )
)
INNER JOIN CustomPollerStatus ON ( CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID )
WHERE ( ( ( CustomPollers.UniqueName = 'cpaePortCapabilitiesEnabled'
AND CustomPollerStatus.Status LIKE '0%'
)
OR ( CustomPollers.UniqueName = 'hpicfUsrAuthMacAuthAdminStatus'
AND CustomPollerStatus.Status = '2'
)
OR ( CustomPollers.UniqueName = 'jnxPaeAuthConfigMacAuthStatus'
AND CustomPollerAssignment.Status = 0
AND InterfaceType = 53
)
)
AND ( Nodes.Location = SWITCH' )
AND NOT ( Interfaces.Caption LIKE '%Router%'
OR Interfaces.Caption LIKE '%Switch%'
OR Interfaces.Caption LIKE '%Uplink%'
OR Interfaces.Caption LIKE '%Exception%'
)
AND InterfaceType IN ( 6 )
AND 1 = 1
)
UNION
SELECT COUNT(*) C
FROM ( ( ( Nodes
INNER JOIN Interfaces ON ( Nodes.NodeID = Interfaces.NodeID )
)
INNER JOIN CustomPollerAssignment ON ( Nodes.NodeID = CustomPollerAssignment.NodeID
AND Interfaces.InterfaceID = CustomPollerAssignment.InterfaceID
)
)
INNER JOIN CustomPollers ON ( CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID )
)
WHERE ( ( (CustomPollers.UniqueName = 'jnxPaeAuthConfigMacAuthStatus'
AND CustomPollerAssignment.Status = 0
AND InterfaceType = 53
)
AND ( Nodes.Location = 'SWITCH' )
AND NOT ( Interfaces.Caption LIKE '%Router%'
OR Interfaces.Caption LIKE '%Switch%'
OR Interfaces.Caption LIKE '%Uplink%'
OR Interfaces.Caption LIKE '%Exception%'
)
)
)
SELECT SUM(C) Num_Disabled_ports
FROM Counts;