Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


How do I order this varchar field ?

How do I order this varchar field ?

How do I order this varchar field ?

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--'

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

RE: How do I order this varchar field ?

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

RE: How do I order this varchar field ?

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.

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

RE: How do I order this varchar field ?

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.

RE: How do I order this varchar field ?

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.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close