Sort grouping as Descending?

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?

Find more posts tagged with

reports_in_npm

report_grouping

report_writer

sql_report

report

Accepted answers

All comments

qle

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

FormerMember

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

FormerMember

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.