So I was creating a report with Report Writer (not the Web Report Writer. I wanted the report to be available in places other than in Web Report Writer) and needed to sort the custom poller status results largest to smallest and to filter out results that were smaller than 10,000. So looking at the preview of results on the server, it sorted my values correctly, but it didn't filter out ones that were < 10,000. It filtered out ones that were <= 1, which I thought odd. When I looked at the report online, it hadn't even sorted my statuses correctly, but was sorting them alphanumerically, so something like 99293, 973, 9, 821345, 81234, 888, ... 233999, 2000, 2. That wasn't what was showing up on the server....So I checked to make sure my field formatting for status was set to a number. It was. So then what I did was look at the generated SQL and noticed that it had Status as a string, not an int. I copied the generated SQL and plopped it into a new SQL report, took off the quotes, and it worked just fine. Seems to me like the querying robots dropped the ball? I think that Status for custom pollers is Unicode and not integers by default since there could be lots of different values in the custom poller status results.
Just wanted to share and to see if anyone had run into this and if there is some other solution than editing the SQL. Thanks!
-----------------------------------------------------------------------------------------------------
Here's the query that was auto generated just because (part that screwed up my report in bold):
SELECT NodeID, NodeName, Poller_Name, CAST(Status AS varchar) AS Status, DateTime, PollerID FROM (
SELECT TOP 10000 Nodes.NodeID AS NodeID, Nodes.Caption AS NodeName, CustomNodePollers_CustomPollers.UniqueName AS Poller_Name, CAST(CustomNodePollerStatus_CustomPollerStatus.Status AS real) AS Status, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()) , CustomNodePollerStatus_CustomPollerStatus.DateTime) AS DateTime, CustomNodePollers_CustomPollers.CustomPollerID AS PollerID
FROM ((Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON (Nodes.NodeID = CustomNodePollerAssignment_CustomPollerAssignment.NodeID)) INNER JOIN CustomPollers CustomNodePollers_CustomPollers ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerID = CustomNodePollers_CustomPollers.CustomPollerID)) INNER JOIN CustomPollerStatus CustomNodePollerStatus_CustomPollerStatus ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerAssignmentID = CustomNodePollerStatus_CustomPollerStatus.CustomPollerAssignmentID)
WHERE
(
(CustomNodePollers_CustomPollers.UniqueName = 'mtxrQueueTreeDropped') AND
(CustomNodePollerStatus_CustomPollerStatus.Status > '10000')
)
AND
(
(CustomNodePollerAssignment_CustomPollerAssignment.InterfaceID = 0)
)
AND isnumeric(CustomNodePollerStatus_CustomPollerStatus.Status) = 1
ORDER BY 4 DESC
) A UNION ALL
SELECT NodeID, NodeName, Poller_Name, CAST(Status AS varchar) AS Status, DateTime, PollerID FROM (
SELECT TOP 10000 Nodes.NodeID AS NodeID, Nodes.Caption AS NodeName, CustomNodePollers_CustomPollers.UniqueName AS Poller_Name, CustomNodePollerStatus_CustomPollerStatus.Status AS Status, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()) , CustomNodePollerStatus_CustomPollerStatus.DateTime) AS DateTime, CustomNodePollers_CustomPollers.CustomPollerID AS PollerID
FROM ((Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON (Nodes.NodeID = CustomNodePollerAssignment_CustomPollerAssignment.NodeID)) INNER JOIN CustomPollers CustomNodePollers_CustomPollers ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerID = CustomNodePollers_CustomPollers.CustomPollerID)) INNER JOIN CustomPollerStatus CustomNodePollerStatus_CustomPollerStatus ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerAssignmentID = CustomNodePollerStatus_CustomPollerStatus.CustomPollerAssignmentID)
WHERE
(
(CustomNodePollers_CustomPollers.UniqueName = 'mtxrQueueTreeDropped') AND
(CustomNodePollerStatus_CustomPollerStatus.Status > '10000')
)
AND
(
(CustomNodePollerAssignment_CustomPollerAssignment.InterfaceID = 0)
)
AND isnumeric(CustomNodePollerStatus_CustomPollerStatus.Status) != 1
ORDER BY 4 DESC
) B