Sort Expression on Group

• GBeezy

  SSCarpal Tunnel

  Points: 4639

  More actions

  June 22, 2012 at 10:09 am

  #260384

  I need to create Custom Sort on a Grouping I have. I need to sort this by specific Values and the Order should be:

  Initiation

  Planning

  Requirements

  Development

  Deployment

  If I sort on the Column Name of Phase it of course puts them either A-Z or Z-A. I need to achieve the custom order I mentioned above. I have been Googling this, but what I am seeing and trying has not worked.

  A push in the right direction will be greatly appreciated.

  Thanks in Advance!

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  June 22, 2012 at 10:52 am

  #1504632

  Add another column to your data set that has the correct ordering values

  Have a lookup to another table that gave you that ordering.

  SELECT ....

  ....

  , PO.order_value AS PhaseOrder

  FROM ....

  JOIN phaseOrdering as PO

  ON PO.phaseid = mybasetable.phaseid

  AND PO.report_name = 'MyReport';

  Once you have that in place, you can sort your RS group by that column now.

  Finally, just in case you cannot change the data set, you can do it with some ugly hard-coding in the report.

  To do this, you use an expression in place of a column and assign numeric values to each Phase, but this is also really ugly.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]