7 Replies Latest reply on May 30, 2014 7:55 PM by rgward

    SQL help with Report Writer

    rgward

      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

        • Re: SQL help with Report Writer
          rgward

          bump...anyone?

          • Re: SQL help with Report Writer
            bluefunelemental

            I usually get that when I have order or group by clauses in my statement.

            Can you try it without the nested select statement ?

            1 of 1 people found this helpful
              • Re: SQL help with Report Writer
                rgward

                Bingo!  bluefenemental you nailed it.  Report Grouping is set to Nodes.Modal (custom property).  With grouping specified, it throws the sort error in Report Writer only.  If I remove grouping, the report executes fine in RW but I don't get the report in the order I need.  Any thoughts how to get around the grouping issue in RW?

                 

                I removed the nested select statement as Leon recommended as well.

                 

                Thanks for your insite!

                  • Re: SQL help with Report Writer
                    bluefunelemental

                    I don't have a fix- just ungroup while you need to work on field formatting- should still work fine in the web either way. Same thing I do when I have a ${variable} in the where clause.

                    If possible move out to the web-based report engine which I have not seen have the sort issue.

                      • Re: SQL help with Report Writer
                        rgward

                        Yep, that's what I do...remove grouping when working in RW. Been hesitant on using web reports.  Don't like having to manage reports in two places.. If SolarWinds, and I hope they do, would provide a migration tool for the RW reports so they can be edited in the web report builder I would jump to the web reports. But, I'm sticking with building advanced SQL reports in RW for now.

                  • Re: SQL help with Report Writer
                    Leon Adato

                    I agree with bluefunelemental:  It MAY have to do with the nested select statement. I guess I would have gone for a more linear SQL. I also like ot explicitly call out the table name just in case:

                     

                    SELECT Nodes.NodeID, Nodes.Caption AS Node_Name, Nodes.IP_Address, Nodes.Modal, Nodes.Vendor, Nodes.MachineType, Nodes.Description, Nodes.IOSVersion,

                    (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'

                    1 of 1 people found this helpful
                      • Re: SQL help with Report Writer
                        rgward


                        Thanks Leon for the simplifying the SQL query.  bluefenemental pinpointed the issue.  The report does have Report Grouping set to the custom property Nodes.Modal which I missed and failed to mention.  With grouping specified, it throws the sort error in RW.  If I remove it, the report executes fine in RW but I don't get the report in the order I need.  Any thoughts how to get around the grouping issue in RW?