×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

*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

Attribute Form Sorting

Attribute Form Sorting

Attribute Form Sorting

(OP)
Is is possible to have an attribute display one form but sort by a different one?  For example, a CalanderMonth attribute with a 'Number' form (1,2,3,4,etc...) and a 'Description' form(January, February, March, etc...).  I want to display only the 'Description form, but have it default sort to the 'Number' form.  The only way I have been able to do this is to include the 'Number' form in the display, which I don't want to do.

Is this possible?

Thanks,

Gregg Stults
Pacific Seafood
gstults@pacseafood.com

RE: Attribute Form Sorting

can change the sorting thing based on id from the report....just checkout the sorting option and u will find thing to sort by id...

sri

RE: Attribute Form Sorting

(OP)
Sri,

Thank you for the response.  The particular situation I am having problems with is actually in the report prompts.  Many of our reports have a Select Month prompt and the sort order within the prompt is where I am having the problems.  If you know any way to make a series of calender months sort by the month number, I'd love to hear it.

Thanks,

Gregg

RE: Attribute Form Sorting

Hi Gregg,

I'm having a similar issue with forms in Microstrategy, except that my problem is with selecting rather than sorting.  I have attributes set up very similar to you, with a 'Number' or ID form (1,2,3) and a 'Description' form ('USA','Canada').  My problem is that the SQL that is generated uses the description form for any kind of 'where' clause, causing massive table scans and slow performance.  Getting Microstrategy to use the ID form in the select would be ideal for my situation.  Have you had any luck on this?  Sorry I've answered your post with a question of my own, but they were related to the same thing.

I'm sure there is a solution to this, but I've been unable to figure it out.  Anyone????

Thanks!

Mark

RE: Attribute Form Sorting

These sound like 2 different problems, and I addressed the last post with my 2 cents in its own thread.

As to the first problem, the ID is related to the element sort order.  Date IDs might look like 10282002...if the ID was 20021028 then you can sort order the attribute.  Perhaps this will require some changes to your WH...I would test it in an isolated environment first.

Chael

RE: Attribute Form Sorting

(OP)
Mark,

I can think of kinda a reverse solution to your problem.  You could create an index in you database for the description column.

Gregg

RE: Attribute Form Sorting

Gregg,

I thought about that, but indexing a text field is sort of bothersome and could eat up quite a lot of space in the DB.  I'd like to get the ID thing sorted out before we go down that route.

Have you had any luck with your problem?

Mark

RE: Attribute Form Sorting

In the form expression for the ID you can use the applysimple function, I believe.  So you don't have to change anything in the warehouse...run a datepart or something equivalent on the ID and change the ID as MicroStrategy sees it.  So if the ID format isn't sequential in the browse order you may be able to trick it through MicroStrategy.

Just a thought...a little messy.  

-- Another approach --
The root of the problem is that MSTR is doing a select distinct against all the IDs and returning the prompt data elements in the order they exist in the DB.  You could add an extra column to the table and refer to it as the ID...that column could be scripted to giving a number based off the alpha order of the elements in the table you are using as the lookup.

RE: Attribute Form Sorting

(OP)
Chael,

Thanks for the responses.  I think I understand the suggestions you made and I guess the bottom line is Microstrategy can't do what I am asking without a bit of a kludge(not that I am above kludging things).  Another thought I had which is even more 'kludgy' but pretty easy would be to modify the descriptions for the months to something like 1-January, 2-February, 3-March, etc...  Then the sort would work.

Regarding the second approach you mentioned, My date tables all use surrogate keys and I might be able to acheive what you described by reordering those surrogate keys.

Thanks,

Gregg

RE: Attribute Form Sorting

Just to make sure -- are you in MicroStrategy 7 or 6?  When I was working with 6 we had to cludge the descriptions as you mentioned.  7 is more robust and offers more flexibility.  I think altering the descriptions should be your last option.

I hope you have at least looked at these 2 tech notes:
TN4000-007-0180
TN4100-7X0-0075

Chael

RE: Attribute Form Sorting

(OP)
Chael,

We are using veriosn 7.2.  I had looked at those tech notes but they were not quite what I needed.  I played around with it a bit more and the second option you described seems to work well.  I just left the Description form unsorted and it seemed to use the ID(my surrogate keys) default sort.

There are a few other tables I need to play with, but now I think I have a better grasp on what Microstrategy is doing.

Thanks for your help.

Gregg

RE: Attribute Form Sorting

Gregg,  this might address your prompt sort issue. In your calendarmonth attribute, modify the ID form and set the default sort order to ascending.
Now make a prompt of the "choose from attribute list" type for calendarmonth, then update schema.

the desc should be sorted according to the id in your prompt.  I tried it and it works for me, let us know if it works for you too.

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! Already a Member? Login

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