Months Not Sorting Properly
I have a report that has months as the column headers. The dataset is based on an SSAS cube. When building the dataset in query designer, everything sorts just fine by month. When the report is viewed, however, the months are in the complete wrong order.
In the group properties of the column group, I am sorting by month. But instead of sorting by the actual calendar, it's sorting alphabetically. I've included a screen shot.
I've seen other posts about this but cannot find a solution. Has anyone else encountered this and if so, how did you resolve it?
Thanks!
A. M. Robinson
November 2nd, 2011 11:01am
try to add this expression to your group by and sort by properties
year(Fields!Date.Value) & Right("0" & Month(Fields!Date.Value),2)Praveen Kumar
Free Windows Admin Tool Kit Click here and download it now
November 2nd, 2011 2:11pm
When using the expression you suggested, I get the following error:
The Sort expression for the grouping "Month" contains an error: Conversion from string "Month 4" to type 'Date' is not valid.
A. M. Robinson
November 2nd, 2011 2:19pm
Try to convert your string month value to integer like this
=Month(CDATE(Fields!MonthField.Value))Praveen Kumar
Free Windows Admin Tool Kit Click here and download it now
November 2nd, 2011 2:25pm
Praveen:
I was actually able to figure it out...thanks! I was using the wrong field in my expression, so I think I've got it!
Thanks for your help!!A. M. Robinson
November 2nd, 2011 3:11pm