Sorting in SSRS 2008 using mulitple parameters and depending on a sort by selection

I have a report that has 8 sortby options.  I've pasted the sql code below.

ORDER BY R.CalloutLocation
	 , CASE WHEN @SortBy IN (1,4) THEN CalloutDate
			WHEN @SortBy IN (2,3) THEN R.CalloutReason
			WHEN @SortBy IN (5,6,7,8) THEN R.InstructorName
	   ELSE '0'
	   END ASC
	 , CASE WHEN @SortBy IN (1,3) THEN R.OffenderName
			WHEN @SortBy IN (2,5,8) THEN CalloutDate
			WHEN @SortBy IN (4,6,7) THEN R.CalloutReason
	   ELSE '0'
	   END ASC
	 , CASE WHEN @SortBy IN (3,6) THEN CalloutDate
			WHEN @SortBy IN (4,5,7) THEN R.OffenderName
			WHEN @SortBy = 8 THEN R.CalloutReason
	   ELSE '0'
	   END ASC	
	 , CASE WHEN @SortBy = 7 THEN CalloutDate
			WHEN @SortBy = 8 THEN R.OffenderName
	   ELSE '0' 
	   END ASC

Open in new window



However, when I run the report and select an option, it doesn't sort correctly.  I'd like to try doing the sorting in the report itself.  It currently only groups on the CalloutLocation.  Then there is a detail group below that.  Any help will be greatly appreciated.  Thanks
SherryDeveloperAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
nishant joshiConnect With a Mentor Technology Development ConsultantCommented:
Looks like in report,You are using CalloutDateName instead of CallOutDate file from dataset.

Try to use "CalloutDate" Field.


Thanks,
Nishant
0
 
SherryDeveloperAuthor Commented:
Here's the rdl if that will help.
Offender-Attendance-Roster.rdl
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The syntax looks the same as what I have in the article SQL Server CASE Solutions (scroll down to 'A typical practice is to pass a parameter to a stored procedure that is the desired sort order'), other than I don't have the ELSE'0' so maybe that's not needed.

Just to check the box, are you certain @SortBy is one of the values in the IN blocks?
0
 
SherryDeveloperAuthor Commented:
I removed the else '0'  and it seems to be work fine now.  Now all I have to do is figure out why the formatting of the time isn't working like it should.  I have it set for military time HH:mm in the rdl.  The sql has it as

CONVERT(VARCHAR(10), C.StartDateTime, 101) AS CalloutDate

Yet, it still shows the seconds and I can't seem to get rid of it.  Thanks for your help.  I had a feeling it would be something simple.
0
All Courses

From novice to tech pro — start learning today.