Can not sort month-year column?

Question

Can not sort month-year column?

Ritesh_Dev

Ten Centuries

Points: 1299
More actions
December 13, 2011 at 1:44 pm

#249604

I have a date column. I created Month-Year column in my report using expression:
=MonthName(Month(Fields!new_sitevisiteddateutc.Value))& "-"& Year(Fields!new_sitevisiteddateutc.Value)
But I am not able to sort month-year wise.
Like I want MAY-2009 JUNE-2009 JANUARY-2011 MARCH-2011
But I am getting JUNE-2009 MARCH-2011 MAY-2009 JANUARY-2011
Can some one help me in this?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

okbangas SSChampion Points: 11773 More actions · Answer 1

For this you either need a column with the month in something like ISO format, eg 201103 for March 2011, or you need a colum for year and a column for month number. These columns does only have to exist in your dataset, not in the table or tablix, but you are going to sort by these columns.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Jared SSC Guru Points: 61793 More actions · Answer 2

ankurk2 (12/13/2011)
I have a date column. I created Month-Year column in my report using expression:
=MonthName(Month(Fields!new_sitevisiteddateutc.Value))& "-"& Year(Fields!new_sitevisiteddateutc.Value)
But I am not able to sort month-year wise.
Like I want MAY-2009 JUNE-2009 JANUARY-2011 MARCH-2011
But I am getting JUNE-2009 MARCH-2011 MAY-2009 JANUARY-2011
Can some one help me in this?

Add a sort column as 200905,200906,201101,201103.

However, I would simply store the month-year as I did above and then maybe a year column and month column that also holds integers. Then you can use script and functions to convert the integers to text.

Jared

Jared
CE - Microsoft

Ritesh_Dev Ten Centuries Points: 1299 More actions · Answer 3

with this expression:

=MonthName(Month(Fields!new_sitevisiteddateutc.Value))& "-"& Year(Fields!new_sitevisiteddateutc.Value)

I am easily getting what i want but can't sort it . Can you tell me in detail.

One more thing this new column is column group. I am using ssrs2008.

Jared SSC Guru Points: 61793 More actions · Answer 4

ankurk2 (12/13/2011)
with this expression:
=MonthName(Month(Fields!new_sitevisiteddateutc.Value))& "-"& Year(Fields!new_sitevisiteddateutc.Value)
I am easily getting what i want but can't sort it . Can you tell me in detail.
One more thing this new column is column group. I am using ssrs2008.

Ok, the sort is sorting this way because it is sorting alphabetically. You have to give it a number to sort on. Therefore, you have to give a column that represents the month and year as YYYYMM.

Jared

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 5

p-nut (12/13/2011)
ankurk2 (12/13/2011)
with this expression:
=MonthName(Month(Fields!new_sitevisiteddateutc.Value))& "-"& Year(Fields!new_sitevisiteddateutc.Value)
I am easily getting what i want but can't sort it . Can you tell me in detail.
One more thing this new column is column group. I am using ssrs2008.
Ok, the sort is sorting this way because it is sorting alphabetically. You have to give it a number to sort on. Therefore, you have to give a column that represents the month and year as YYYYMM.
Jared

Sort on this only "Fields!new_sitevisiteddateutc.Value" NOT on "MonthName(Month(Fields!new_sitevisiteddateutc.Value))& "-"& Year(Fields!new_sitevisiteddateutc.Value)"

Jared

Jared
CE - Microsoft

Ritesh_Dev Ten Centuries Points: 1299 More actions · Answer 6

Hi Jared

In Column Group properties- i am sorting with date field now and I got the result.

Thanks for your help.