September 14, 2011 at 4:37 am
When I sort a row group/column with name that starts with the Swedish letters Å, Ä or Ö, they get mixed up with A and O. They should be placed last in the alphabet. The collation of SQL Server is set to ..Finnish_Swedish...
Can I do the sorting in any other way?
September 14, 2011 at 2:12 pm
You could create another sort and use an expression like:
=IIF(LEFT(Fields!FieldsName.Value, 1) in (Å, Ä, Ö), 2, 1)
This sort should be placed in front of your other sort, so all normal characters will be assigned a value of 1 from this expression and be moved to the top and the swedish characters will be assigned a value of 2 and moved below.
September 15, 2011 at 12:45 am
Thanks! Yes, that's a simple solution I will use for now.
But, one problem still remain - when there is a Å, Ä or Ö in the middle of the value i sort I don't get the perfect sorting.
/ C
September 15, 2011 at 4:44 am
If you wanted to test for existence of Swedish characters anywhere within the string you would use the INSTR function.
Ex. =InStr(Fields!Description.Value, "car")
You can only test for one specific character at a time so this would likely be put into a CASE statement to be able to test all three character values. Let me know if you need additional help setting it up.
Good luck, Steve
September 15, 2011 at 5:05 am
christofer.jarlesjo (9/14/2011)
When I sort a row group/column with name that starts with the Swedish letters Å, Ä or Ö, they get mixed up with A and O. They should be placed last in the alphabet. The collation of SQL Server is set to ..Finnish_Swedish...Can I do the sorting in any other way?
Well that the server is set to that doesnt really mean that much. You can have different collation on different databases/tables/columns. And if your getting the order wrong... it aint Finnish_Swedish_CI_AS (which would be the normal one)
You can add COLLATE Finnish_Swedish_CI_AS to the order by clause to force SQL to sort it the way you want.
Exampel
declare @Customer table (FirstName varchar(100), LastName varchar(100))
insert into @Customer (FirstName, LastName)
select 'Oskar', 'Oskarsson' union
select 'Östen', 'Oskarsson' union
select 'Sven', 'Oskarsson' union
select 'Oskar', 'Österberg'
select * from @Customer order by LastName COLLATE Finnish_Swedish_CI_AS, FirstName COLLATE Finnish_Swedish_CI_AS
select * from @Customer order by LastName COLLATE Latin1_General_CS_AS, FirstName COLLATE Latin1_General_CS_AS
/T
September 15, 2011 at 5:27 am
Yes, but the I need to go thru all letters in the column for each row, which I'm afraid of will be bas performance. (If I understood you correct). But yes, that would solve it.
September 15, 2011 at 5:30 am
tommyh (9/15/2011)
christofer.jarlesjo (9/14/2011)
When I sort a row group/column with name that starts with the Swedish letters Å, Ä or Ö, they get mixed up with A and O. They should be placed last in the alphabet. The collation of SQL Server is set to ..Finnish_Swedish...Can I do the sorting in any other way?
Well that the server is set to that doesnt really mean that much. You can have different collation on different databases/tables/columns. And if your getting the order wrong... it aint Finnish_Swedish_CI_AS (which would be the normal one)
You can add COLLATE Finnish_Swedish_CI_AS to the order by clause to force SQL to sort it the way you want.
Exampel
declare @Customer table (FirstName varchar(100), LastName varchar(100))
insert into @Customer (FirstName, LastName)
select 'Oskar', 'Oskarsson' union
select 'Östen', 'Oskarsson' union
select 'Sven', 'Oskarsson' union
select 'Oskar', 'Österberg'
select * from @Customer order by LastName COLLATE Finnish_Swedish_CI_AS, FirstName COLLATE Finnish_Swedish_CI_AS
select * from @Customer order by LastName COLLATE Latin1_General_CS_AS, FirstName COLLATE Latin1_General_CS_AS
/T
I use a cube (MDX) and but I might use ORDER in MDX.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply