How do I order this varchar field ? 1

[Recce]

Good day,

I have 'n varchar field in SQL server which gives me time bands. The problem I have is to neatly sort them in SSAS. This is what the time bands looks like. Can anyone maybe make a suggestion as to what I can change to neeatly order the result in my Cube so that it reads in sequence in Excel ?

case when MYTIME >= 0 and MYTIME <= 35 then '0-35 Days'
when MYTIME>= 36 and MYTIME <= 65 then '36-65 Days'
when MYTIME>= 66 and MYTIME <= 95 then '66-95 Days'
when MYTIME>= 96 and MYTIME <= 125 then '96-125 Days'
when MYTIME>= 126 and MYTIME <= 155 then '126-155 Days'
when MYTIME>= 156 and MYTIME <= 185 then '156-185 Days'
when MYTIME>= 186 and MYTIME <= 200 then '186-200 Days'
when MYTIME>= 201 and MYTIME <= 250 then '201-250 Days'
when MYTIME>= 251 and MYTIME <= 300 then '251-300 Days'
when MYTIME>= 301 and MYTIME <= 350 then '301-350 Days'
when MYTIME>= 351 and MYTIME <= 400 then '351-400 Days'
when MYTIME>= 401 and MYTIME <= 450 then '401-450 Days'
when MYTIME>= 451 and MYTIME <= 500 then '451-500 Days'
when MYTIME>= 501 and MYTIME <= 600 then '501-600 Days'
when MYTIME>= 601 and MYTIME <= 700 then '601-700 Days'
when MYTIME>= 701 and MYTIME <= 800 then '701-800 Days'
when MYTIME>= 801 and MYTIME <= 900 then '801-900 Days'
when MYTIME>= 901 and MYTIME <= 1000 then '901-1000 Days'
when MYTIME>= 1001 then '1000 + Days'
else '--No Payment Days--'

"We know nothing but, that what we know is not the truth..." - Me

 

[RiverGuy]

I have a dimension somewhat similar, although I'm not using logical numeric ranges, but rather descriptions which need to be sorted in a specific order. I have an attribute for Sort. AttributeHierarchyVisible is set to False so that the user cannot browse by this attribute. In the Attribute Relationships, I have my Sort attribute related to my Description attribute instead of the dimension Key. Then, in the properties of the Description attribute, my OrderBy property is set to AttributeKey. I have selected my Sort attribute as the OrderByAttribute.

You could alter your query to make your own Sort attribute like this:

RangeSort =
case when  MYTIME >= 0 and  MYTIME <= 35 then 1
     when  MYTIME>= 36 and  MYTIME <= 65 then 2
     when  MYTIME>= 66 and  MYTIME <= 95 then 3
--etc.
END

 

[Recce]

Hi RiverGuy,

Yes,that will work....

Problem is that I don't have an attribute to use for sort. I will go and see if I can find one but, what you say will work.

Thanks for this. Appreciate it.

"We know nothing but, that what we know is not the truth..." - Me

 

[RiverGuy]

That's what I was saying with my code. Your CASE statement let me to believe that you are using a view or a named query in SSAS. So you could just created another CASE statement in that query to make your Sort column in the resultset.

 

[Recce]

O,I understand....I'm going to have a look and see if I can manage that...Will let you know if I can maybe do something like that.

"We know nothing but, that what we know is not the truth..." - Me