A classic problem in reporting is when the end-user ask you to display Month Names instead of Month Numbers and when the table is sorted by months...
The sort will not work anymore in this case, this case may happen for other columns like (week of the day, a status like 0=Low, 1=Medium, 2=High, 3=Critical, or whatever).

To handle this, you have to create a enumerated list for the column and defined the custom sort in its definition.

I show an example with Northwind:

Step 1: create your enumerated list

• Open the Repository Source Northwind with the Server Manager (try to run it as Administrator when you can to avoid security issues).
  
• Expand the TreeView and display the [Order Month] column in the [Orders] table.
  
• Create an enumerated list using the helper button
  
• you will be re-directed to the new enumerated list created
  
• in Select SQL Statement, type:
  
  Code:

  SELECT DISTINCT 
  DatePart('m',[Orders.OrderDate],1,0), Format([Orders.OrderDate], "mmmm") 
  FROM Orders 
  ORDER BY 1

  
  
• we have added a second column to display the month name, you can check your enum values with the related helper button
  
• finally, set the Properties 'Use defined position to sort in reports' to True, this property will tell the engine to use the position in the list (the collection of Values) to perform the sorting.
  
• save your Repository Source
  

Step 2: check it with a report

• Create a new report.
  
• In the Model, select [Order Month].
  
• Execute the report -> The months are correctly sorted ! This comes from the flag set to True in the enumerated list. Etonnant non ?
  

Edited by user Thursday, May 22, 2014 4:56:59 PM(UTC)  | Reason: Not specified