Group sorting

[BHScripter]

Hello:

I am new to SSRS, I am using the "BIDS" version that comes with sql server 2005. I am much more familiar with Crystal reports than this so I am slogging through.

I have the following in my dataset query:
'Q' + datename(Q,a.EndDt) + ' ' + datename(YYYY, a.EndDt) enddate,

When I group on enddate in my tabular report, it is sorting the quarters out of order. (This report is run for one calendar year at a time) I can see the sorting tab for the group but I'm not sure what to put in the expression field to have it sort the quarters correctly.

Any help is greatly appreciated

 

[RiverGuy]

Something like this should work:

=RIGHT(Fields!EndDate.Value.ToString , 4) & LEFT(Fields!EndDate.Value.ToString , 2)

If you're doing one year at a time, it should in theory sort just fine if you just use =Fields!EndDate.Value. But the code I posted should work should you have to run multiple years together.

 

[BHScripter]

Hi:
I'm sure your code is good, I must be doing something wrong. I pasted that in the "Sorting" expression field for that group and it is still sorting the group Q2, Q4, Q3, Q1 within the same year. I added an order by clause into my query and that worked out. Thanks for the help.

 

[BHScripter]

Actually, the order by clause for some reason caused some of the data to disappear so I did left(Fields!EndDate.Value, 2) and that worked.

 

[RiverGuy]

One thing that always gets me is that I sometimes place the sort in the incorrect grouping or details section.