Sorting Data by NOT a-z - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Sorting Data by NOT a-z

Author

Topic

v02468
Starting Member

3 Posts

Posted - 2010-03-03 : 13:50:28

Hey everyone. I'm cutting my teeth on Reporting Services at the moment and ran into a bit of a snag.

I have a report I'm putting together that displays data against several different parameters. The results I would like to sort in a way that is not alphabetical. I saw that you could sort against a parameters values, so I created a parameter with the fields I wanted and the order I wanted the results sorted, but when you modify the group and add a sort there doesn't seem to be any way to NOT use alphabetic sorting.

So the question boils down to: Is there a way to sort that is NOT alphabetical? And how do I access/use it?

We are using SQL Reporting Services 2008.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 09:43:12

sort not alphabetical? what does that mean? there must be column based on which you need to sort right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

v02468
Starting Member

3 Posts

Posted - 2010-03-04 : 13:55:13

An example may help,

Suppose I have a record of students and some are Freshman, some are Sophomore, Junior, Senior, and some are 5th Year Senior.

I want to sort the records to display Freshman, Sophomore, Junior, Senior, 5th Year Senior. Unfortunately I can only choose to sort alphabetically which will never end up in the correct pattern.

Does that help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 13:58:26

yup. very much. its just a matter of adding a derived column and sorting based on it

like


SELECT columns..
FROM
(
SELECT other columns...,
CASE yourfield
WHEN 'Freshman' THEN 1
WHEN 'Sophomore' THEN 2
WHEN 'Junior' THEN 3
WHEN 'Senior' THEN 4
WHEN '5th Year Senior' THEN 5
END AS SortOrder
FROM Table
)t
ORDER BY SortOrder ASC