3 Replies Latest reply on Aug 18, 2011 3:48 PM by pmailloux

    Sort grouping as Descending?

    pmailloux

      I've used the "Report Grouping" tab in Orion Report writer to group my report output by a custom field (Bandwidth) but it's only listing the output in ascending order. Is there a way to make the output display in descending order? I can't find any options for this in the "Report Grouping" tab and editing the SQL by hand has no effect.


      I assume the Report Writer does the Report Grouping using it's own code as follows:

      "<LayoutGroups><Group Field="Bandwidth" GroupHeader="${Bandwidth}" WEBURL="" Alignment="0" ForeColor="0" BackColor="16777215" FontSize="8" FontName="Arial" FontBold="TRUE" FontItalic="FALSE" FontUnderline="FALSE" FontStrike="FALSE" Transparent="FALSE"></Group></LayoutGroups>"

      I don't see where I can change the order of the group in there though.

      Does anyone know how I might be able to change the order of the grouping for this report?

        • Re: Sort grouping as Descending?
          qle

          Could you post your SQL query, specifically the ORDER BY part of it?

            • Re: Sort grouping as Descending?
              pmailloux

              It's not a matter of sorting the outputs by using ORDER BY. It's a matter of the Orion "Report Grouping" not giving me an option to sort the Bandwidth groupings as descending. Screen shot for reference: http://i.imgur.com/juzTZ.jpg

              Anyway here is the entire SQL query with the ORDER BY in bold at the bottom.

               

              DECLARE @StartDate DateTime
              DECLARE @EndDate DateTime

              SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
              SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))

              set nocount on
              create table #tmpJoin (fromDate datetime, toDate datetime)
              insert into #tmpJoin values(@StartDate,@EndDate)
              set nocount off

              SELECT Interfaces.InterfaceId,
              Nodes.NodeID,
              Nodes.HA,
              Nodes.Site,
              Nodes.VendorIcon AS Vendor_Icon, 
              Nodes.Caption AS NodeName,    
              Interfaces.InterfaceIcon AS Interface_Icon,
              WANBandwidth AS Bandwidth,
              Maxbps_In95,
              Maxbps_Out95,
              Maxbps_95
              FROM Nodes
              INNER JOIN Interfaces
                  ON (Nodes.NodeID = Interfaces.NodeID)

              INNER JOIN   
              (
                  SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95
                  FROM (
                      SELECT DISTINCT A.InterfaceID
                      FROM dbo.InterfaceTraffic A
                      JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate
                  ) AS AA
              ) as RESULT_IN
                  ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)

              INNER JOIN   
              (
                  SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95
                  FROM (
                      SELECT DISTINCT A.InterfaceID
                      FROM dbo.InterfaceTraffic A
                      JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate
                  ) AS AA
              ) as RESULT_OUT
                  ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)

              INNER JOIN   
              (
                  SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95
                  FROM (
                      SELECT DISTINCT A.InterfaceID
                      FROM dbo.InterfaceTraffic A
                      JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate
                  ) AS AA
              ) as RESULT_MAX
                  ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)
              WHERE interfaces.WANFeed = 'Primary' AND nodes.region <> 'ENG'
              ORDER BY WANBandwidth DESC, MAXbps_95 DESC

              Drop table #tmpJoin

                • Re: Sort grouping as Descending?
                  pmailloux

                  After extensive searching and trial and error, this seems to be a deficiency in the Orion Report Writer / Report Web Interface.

                  There doesn't seem to be any way at all to change the order of the groupings. This also applies to SummarMonth's where you have a column of months in ascending order only.