Custom Sort Order in SSRS 2014

0
votes

I am having to create a custom sort order within a SSRS Matrix, it returns 2 rows of data and then a total. When I add the custom sort order within the Row Group Sort Order, its moving the Total to the top and the relevant rows below it instead of keeping the total on the bottom, below is the code that I am using, can someone point out where I am going wrong, think the time of day is getting to me.

Thanks 

=iif(Fields!loan_amount.Value="<£1000","1", 
iif(Fields!loan_amount.Value="£1000+","2",""
))

In response to my follow on question, I cant post the entire output of the report as it contains company personal data so what I have done is go a screen grab of the Tablix in design view, this is currently meant to be ordering by revenue but when you look at the second screen shot you can see that this is not happening. I went through and checked the output of my stored procedure and its passing the revenue out as a decimal which is fine. I have just put the data in to a straight forward table with no grouping and then ordered it by revenue and it works fine, it appears to only happen when I am putting it in to a matrix. Any help would be much appreciated.enter image description here enter image description here

1
reporting-servicesssrs-2008ssrs-2008-r2ssrs-2012

1 Answers

1
votes

Try this as the sort expression:

=IIf(Fields!loan_amount.Value="<£1000",1, 
   IIf(Fields!loan_amount.Value="£1000+",50,99))

An empty string will sort to the top. Using numbers for a sort is more reliable. Leaving a gap between numbers allows you to add some other conditions in here later with a little less pain.

Hope this helps you out.