sql server reporting services: adding All option to a sorted list -- sql server reporting services

adding All option to a sorted list

GuyR
2/13/2005 2:07:01 AM

sql server reporting services:

Hi.

My DB holds a table with a varchar column containg numbers.
In RS I need to create a dataset that will sort these numbers and add 'All'
value to this dataset.

My problems is:
As the dataset holds sting values and it performs lexicographic sort, if I
perform cast to int on these numbers (to sort them correctly) I can't add the
'All' value.

Furthermore, Does anyone knows how to refer to a custom made dataset defined
in the RS?

Thanks,
Guy

Re: adding All option to a sorted list

Wayne Snyder
2/14/2005 8:49:43 AM

Try something like

select right('0000' + myvarcharfield,4) from table order by 1
union
select 'All'

for a set of numbers not longer than 4 characters... This will sort them
correctly I think without converting them to ints

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

[quoted text, click to view]