Showing posts with label ordering. Show all posts
Showing posts with label ordering. Show all posts

Saturday, February 25, 2012

Changing Ordering of a Dimension Attribute

I have a dimension attribute called Month. It is sorting alphabetically instead of based on it's underlying key value. How do I change this?

Thanks,

ChrisChange the order by from "Name" to "Key". No idea what "Attribute Name" and "Attribute Key" do however.|||If you choose attribute name, you can define the order by another field.
I have in each dimension an order field and I have configured each dimension to sort by this field. By default I fill in this field with name. But if a user request another order I can change values in this field without changing dimension structure.

Changing Ordering

Not quite sure how to word this.
Is there any way to change the ordering of SQL Server
I need it to order
ABC2 then ABC12 then ABC110 (this is by entire string, rather than
individual characters)
currently it orders it ABC110, ABC12, ABC2
is there anyway we can change this?
On Thu, 2 Feb 2006 02:11:27 -0800, Sian wrote:

>Not quite sure how to word this.
>Is there any way to change the ordering of SQL Server
>I need it to order
>ABC2 then ABC12 then ABC110 (this is by entire string, rather than
>individual characters)
>currently it orders it ABC110, ABC12, ABC2
>is there anyway we can change this?
Hi Sian,
ORDER BY ColumnName DESC
?
Hugo Kornelis, SQL Server MVP
|||Sorry I don't think I explained my problem properly. I can't just use desc
as this isn't what I need.
For example, I have the following parts on the shelf, in this (logical) order:
Table 1
FDB2
FDB12
FDB125
FDB1000
FDB2100
However, if I run a report that includes these part numbers on T21, the
report sorts them as follows:
Table 2
FDB1000
FDB12
FDB125
FDB2
FDB2100
I can see the sorting logic here (ie part no. is not an integer and
therefore it is read and sorted as text - from left to right) but I’m afraid
this sort order (Table 2) is no good.
We need a system option to manage/redefine sort orders for alpha-numeric
part numbers on all reports that contain part number lists.
|||"Sian" <Sian@.discussions.microsoft.com> wrote in message
news:D7CC01E7-6CD8-4DEC-B621-4F2889B1BCFA@.microsoft.com...
> Not quite sure how to word this.
> Is there any way to change the ordering of SQL Server
> I need it to order
> ABC2 then ABC12 then ABC110 (this is by entire string, rather than
> individual characters)
> currently it orders it ABC110, ABC12, ABC2
> is there anyway we can change this?
If the structure is consistant: 3 caracters followed by n figures I'd sort
on the value of the string minus the first 3 positions. Something like:
Order on VAL(MID([Myfield];4))
Or use another trick to get rid of all but figures '0123456789'
Function NumOnly(strSource) As String
' Get rid of all but figures
' on the fly adapted from other routine and untested
Dim strOK As String
Dim strPart As String * 1
Dim intLus As Integer
Dim intPlaats As Integer
NumOnly = ""
strOK = "0123456789"
While Len(strSource) > 0
strPart = Left(strSource, 1)
strSource = Mid(strSource, 2)
intPlaats = InStr(1, strOK, strPart)
If intPlaats > 0 Then
NumOnly = NumOnly + strPart
End If
Wend
End Function
and go with Order on VAL(NumOnly([MyField])
|||On Mon, 6 Feb 2006 02:11:30 -0800, Sian wrote:

>Sorry I don't think I explained my problem properly. I can't just use desc
>as this isn't what I need.
>For example, I have the following parts on the shelf, in this (logical) order:
>Table 1
> FDB2
> FDB12
> FDB125
> FDB1000
> FDB2100
>However, if I run a report that includes these part numbers on T21, the
>report sorts them as follows:
>Table 2
> FDB1000
> FDB12
> FDB125
> FDB2
> FDB2100
>I can see the sorting logic here (ie part no. is not an integer and
>therefore it is read and sorted as text - from left to right) but Im afraid
>this sort order (Table 2) is no good.
>We need a system option to manage/redefine sort orders for alpha-numeric
>part numbers on all reports that contain part number lists.
>
Hi Sian,
I was afraid of this...
The problem is caused by a flawed design. Apparently, these part numbers
are not a single code, but a concatenation of two attributes. And at
least one of those attributes has more meaning than just identifying a
part.
Since "FDB" and "1000" are two distinct attributes, they should have
been stored in two distinct columns. You can always concatenate them in
the front-end, in a view, or -if you really must- in a computed column
in the base table.
Until you get your design fixed, you can try using this kludge as a
temporary workaround:
ORDER BY LEFT(BadColumn, 3),
CAST(SUBSTRING(BadColumn, 4, LEN(BadColumn) - 3) AS integer)
However, this *will* break if you have any data in the table where the
part number doesn't follow the same layout. And it will perform very
bad, because the optimizer can't leverage an index on the BadColumn to
facilitate the sorting operation.
Hugo Kornelis, SQL Server MVP