SSRS Multiple Sorts in one Matrix in One column

Question

This is a follow on question from one I have earlier posted but have not reviewed the answers for and I didnt want to confuse things. Can someone tell me if this is possible. I have 2 columns, Company and revenue, I want to initially sort by revenue "desc" but when Company equals Other I want to set this to be last in the order by despite whatever the revenue value is?

Thanks again for all the help I receive on here. P

I want my data to look like the following

Company Name Revenue
ACE £3
Something £2
Another N £1
Other £6

So its ordered by revenue but Company Name "Other" will always be the last entry despite what the Revenue value is. Thanks

Hi Chanom, thanks for the reply it should look something like this — PJD, May 12 '17 at 08:01
Didn't you try use CASE statement in sql like this ORDER BY CASE WHEN CompanyName = 'Other' THEN -1 ELSE Revenue END DESC — Chanom First, May 12 '17 at 08:56

score 0 · Answer 1 · answered May 12 '17 at 14:19

After many ways of trying to do this I managed to find the way that suited my needs best, I created a second column within my Matrix listing the Revenue, but when the Company Name = Other I set this to 1, then ordered my table based on the newly created column, then hid it and only showed the column with the Revenues in it, this forced Other to appear at the bottom. Thankfully none of the other companies will ever have a revenue of 1 or less so will never break my sort order. Hope this explanation helps someone else.

SSRS Multiple Sorts in one Matrix in One column

1 Answers1