SSRS bar chart order by value

Question

I have the following data:

-----------------
Name|Value|Type
-----------------
A   | 110 | Daily
-----------------
A   | 770 | Weekly
-----------------
B   | 150 | Daily
-----------------
B   | 700 | Weekly
-----------------
C   | 120 | Daily
-----------------
C   | 840 | Weekly

In SSRS bar chart, the Name will be X axis, the Value will be Y axis, the Type will be series.

What I need is the bar chart will sort by the Weekly Value descending, so the expected order should be:

C Weekly
C Daily
A Weekly
A Daily
B Weekly
B Daily

How to do that? In query or in SSRS chart setting?

John Woo John Woo · Accepted Answer · 2013-06-26T05:21:43

You can use ROW_NUMBER() to generate sequential number for each Name based on the Value for Weekly type and which will then be the basis on how the records will be sorted.

WITH records
AS
(
    SELECT  Name, 
            ROW_NUMBER() OVER (ORDER BY Value DESC) rn
    FROM    tableName
    WHERE   Type = 'Weekly'
)
SELECT  a.*
FROM    tableName a
        INNER JOIN records b
            ON a.Name = b.Name
ORDER   BY b.rn, a.Type DESC

SQLFiddle Demo

OUTPUT

╔══════╦═══════╦════════╗
║ NAME ║ VALUE ║  TYPE  ║
╠══════╬═══════╬════════╣
║ C    ║   840 ║ Weekly ║
║ C    ║   120 ║ Daily  ║
║ A    ║   770 ║ Weekly ║
║ A    ║   110 ║ Daily  ║
║ B    ║   700 ║ Weekly ║
║ B    ║   150 ║ Daily  ║
╚══════╩═══════╩════════╝

SSRS bar chart order by value

1 Answers