I'm getting a 'SQL Error: Sort Order cannot be applied.' with the following advanced query when executed from Report Writer. The report gets generated but goes away when I acknowledge the popup for the SQL error and the report output is wiped from the results panel. I have Report Grouping level set to 'Modal' which is a custom (text) property. The report runs just fine from the web GUI console and sorts and groups correctly by 'Modal'. If I remove Report Grouping by Modal, the report runs fine within Report Writer. Can someone tell me what is wrong with my query and/or Report Writer?
Select NodeID, Node_Name, IP_Address, Modal, Vendor, Machine_Type, Description, IOS_Version, EntityType From ( SELECT
Nodes.NodeID, Nodes.Caption AS Node_Name, Nodes.IP_Address AS IP_Address, Nodes.Modal AS Modal, Nodes.Vendor AS Vendor, Nodes.MachineType AS Machine_Type, Nodes.Description AS Description, Nodes.IOSVersion AS IOS_Version,
(CASE
WHEN Nodes.EntityType = 'Orion.Nodes' THEN 'Physical Machine'
WHEN Nodes.EntityType = 'Orion.VirtualMachine' THEN 'Virtual Machine'
WHEN Nodes.EntityType = 'Orion.VIM.Hosts' THEN 'Virtual Host'
WHEN Nodes.EntityType = 'Orion.VIM.VCenters' THEN 'vCenter'
ELSE 'Unknown'
END) AS EntityType
FROM
Nodes
WHERE
(
(Nodes.Device_Type = 'SERVER'
AND Nodes.EntityType = 'Orion.Nodes'
AND Nodes.MachineType <> 'unknown')
)
) AS r
