HACK ALERT! HACK ALERT!
Don’t admit you know this, don’t talk about it with developer friends, and whatever you do, don’t blog about it – you’ll quickly become a laughing stock among people who know better.
On my beautiful SSRS report, I had a parameter called Users. This parameter was populated via a DataSet which in turn was populated from a web service in the form of a comma-delimited list of users. The source was a jumble, a mess, and not in any kind order. Not good is what I am saying. Because I was working in Reporting Services, my options were limited. So I did what I do – I hacked away until it worked.
My solution was to create a DataSet called “SortedUsers”. It has a single parameter, which was the unsorted list of users I got from the web service.
I then created a stored procedure that took that nasty list, did a little razzle dazzle by converting it to XML and selecting from this XML using an ORDER clause to get them in the order I wanted:
CREATE PROCEDURE dbo.SortThemThereUsers
SET NOCOUNT ON;
DECLARE @Delimiter CHAR(1) = ‘,’
DECLARE @Results XML
SELECT @Results = CONVERT(xml,'<root><u>’ + REPLACE(@Users,@Delimiter,'</u><u>’) + ‘</u></root>’)
SELECT [User] = T.c.value(‘.’,’varchar(25)’)
FROM @Results.nodes(‘/root/u’) T(c)
ORDER BY [User] ASC
(You can test it by running this bit of SQL: EXEC SortUsers @Users = ‘SinatraF,MartinD,DavisS,LawtonP’)
Finally, I bound the Users parameter to the UserSorted DataSet. Voila, perfectly sorted users from an unsorted source.