I have a column chart that uses grouping by month on the x-axis. The datasource field is an SSAS time dimension-month heirarchy (string).The problem is when I try and sort by month (Jan, Feb, March, April, etc) it sorts as a string (April, Feb, Jan, etc). How would I format the sorting to show the proper month order. Thanks

u need to add one more column called month_number or something which is like number:

select datepart(mm, getdate())

u can then sort it by this number and it will work fine.

regards,

vijay

Vijay,

Is there anyway to do this kind of sorting on the fly within SSRS as I am using an SSAS datasource rather than a SQL datasource.

What I am looking for is howtoformat a month string, within RS, so that it is read as a month and can be sorted according to month (Jan, Feb, March, etc) instead of alphabetically.

Thanks for your help.

This code snipet returns the month as a number... ideally you want to be passing this value to your chart.

select month(getdate())

You could use the following expression within SSRS to convert the month in string format to an integer:

=Month(CDATE(Fields!MonthField.Value + ", 01, 2008"))

hope this helps,

sivilian

Jon,

I am using an SSAS datasource, not a SQL datasource for my reports. I want to know how to format the month string, within RS, to an int (Jan is 1 and Feb is 2) to be able to sort on that.

So, how would I format let's say Month(Format(Fields!Month_Display.Value)) into an int representation ofmonth ...... Month_Display is the string for month (Jan, Feb, March, etc).

I think what you are trying to tell me is how my query must sort the Months, but I am using an SSAS datasource, which already has the months stored within the dimension as a heirarchy.

Thanks a bunch for your help!!

Sivilian,

That's what I needed, thanks a bunch!!!!

Just found another easy way to do this....

Go to the "Month" Group properties and then to the sorting. By default the sort column is by [Month] and order by A-Z. select and delete the sort option so that you will not see any sort options for the particular group.

Then the report runs as a charm.

Optionally. right click on rdl file on the solution and choose. show code option. there will be an xml displayed. search for "month" until you end up something like below. you should remove these tags and it will work like a charm.

 <SortExpressions>
                    <SortExpression>
                      <Value>=Fields!MonthName.Value</Value>
                    </SortExpression>
                  </SortExpressions>

The Best Solution!