Attribute Form Sorting

[gstults]

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

 

[Sridharan]

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

 

[gstults]

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

 

[mrdalton]

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

 

[chael]

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

 

[gstults]

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

 

[mrdalton]

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

 

[chael]

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.

 

[gstults]

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

 

[chael]

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

 

[gstults]

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

 

[nlim]

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.