Sorting records from an unsorted source in Reporting Services

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.

Parameter definition

Parameter definition

DataSet definition

DataSet definition

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
@Users VARCHAR(8000)
AS
BEGIN
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
END

(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.

Parameter

Parameter

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: