Group By Question

[Annie123]

In a dimension table you have an id with a corresponding name. For Microstrategy Desktop, all of the attributes/metrics are linked by the ID. I would like to have the Select statement choose the id to do all calculations, but I want them to be Grouped by the name.

Is there a way to group by name vs id or perhaps have both name and id in the select statement?

thanks in advance,
annie123

 

[aabid]

According to best practices, ID & Desc columns are 1-to-1 mapping to each other. If you group by ID or Desc does not really matter as long as you are displaying the Desc. Grouping by ID is the best approach for performance reasons.

My question is, Is there a specific reason why you want to group by Desc? The only reason i can think of is if the Desc column is not a 1-to-1 related to ID. In that is true, then you might want to re-define your attribute.

Hope this helps. Or if you can give me specific examples or reasons of why you need to group by Desc column, i may be able to throw some more light on your issue.

Aabid

 

[nlim]

i think so, in the report, under data>>vldb settings. there should be a setting under the select folder called "group by non ID" or something like that. it will place id and name on the select and group bys.

 

[Annie123]

Thanks aabid and nlim
Actually in the lkp table there is a 1to1 relationship ,however, some of the descriptions are identical and that is why on my report, I only want to group by description. Another words, I would like to have all the id's group under the same descriptive name. example:
ID Desc

1 Black
2 White
3 Black
4 White

nlim, I did not know about the group by vldb setting and will try that.

 

[aabid]

Based on your example you specified, your ID and Desc are not 1-to-1 related. Also, you did mentioned "I would like to have all the id's group under the same descriptive name." If this is what you want to do, then you might want to create two attributes:

1. Product
2. Product Color

Create a 1-to-M relationship between Product Desc & Product (Example, One Product Color has many Products). Then place Product Color on your report. This will group all product ids by your product descriptive names.

Hope this helps.

Aabid