"Bill H" <Bill H@discussions.microsoft.com> wrote in message
news:C559EC1D-03FD-454E-85C2-82FDD0FC69E6@microsoft.com...
> I'm new to reporting services and I've set up a report to extract data
> from
> another dbms using ODBC. I'd like to right-sort the text data upon
> output.
>
> The field I'm trying to sort on is a UnitNo field, where data can be
> pretty
> much any combination of non-control characters (char(32)-char(127)). Some
> examples are: 1, 10, 100, A1, A-1, 1-101, 1A/4, D/2, etc.
>
> The data-type is defined as Text. So, all Reporting Services ordering of
> output looks like:
>
> 1
> 10
> 11
> 12
> 2
> 21
> 22
> 3
> etc
>
> Changing the sort-type from ascending to descending doesn't help. In the
> dbms, the field definition contains the primary sort type so there is no
> problem sorting text data using a right-justified numeric sort algorithm.
>
> However, using ODBC as an extraction mechanism leaves me at the mercy of
> the
> Reporting Services tool. Is there a simple way I can change the sorting
> algorithm of the text data so my report sorts in UnitNo order?
>
> I've solved the problem from the dbms end by creating a new synonym field
> definition that right jistifies the data in a field of zeros (e.g. unit#
> A-1
> is converted to 0000A-1). I was hoping for a more elegant solution,
> though.
>
> Any help would be greatly appreciated.
>
> Bill