0

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

PJD
  • 743
  • 2
  • 12
  • 38

1 Answers1

1

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.

R. Richards
  • 24,603
  • 10
  • 64
  • 64
  • Hi R, Thanks for the tip, that now seems to be working better but I seem to have a general overall issue with sorting data. E.g. I have a table Matrix with a list of . group name, company names and company revenues, I am grouping my data on the group name as I can have multiple company names associated with one group, on the initial run it runs the report and sorted on group name which is fair enough but I want to sort on revenue, when I set this either within the group or within the Table Matrix the revenues are all over the place, could there be something else wrong with the report overall? – PJD May 11 '17 at 21:28
  • As long as the revenue numbers are that, numbers, the sorting should work. If, for some reason, they numbers are really coming to the report as text, the sort will not work, and the numbers will need to be converted first. Also, when sorting in a matrix, you may have to play around to figure out which group/level to sort at. It may not be the one you thing, so try a few. – R. Richards May 11 '17 at 21:34
  • Each group has its own sort order so you can sort differently within each scope of the table. Try adding a sort by revenue at your desired detail level. – StevenWhite May 11 '17 at 21:34
  • If all the above fail, post a sample of your data, the report design , the current output and the desired output. I'm sure it'll be something simple.. – Alan Schofield May 11 '17 at 21:46
  • I have managed to sort out where I was going wrong with this, when I was ordering the data within the Matrix, I was ordering based on the revenue, not on the sum of the revenue as that is what I was showing within the Matix, once I had made this change everything was working fine. As r. Richards was the person who answered the original question I will mark his response as the answer. Thanks again for all the help. – PJD May 12 '17 at 14:15