Problem with selecting all levels from hierarchy prompt!!

[RRCR]

I have created prompt on below hierarchy.

Product roll Up Name

Product Level 1 Cd

Product Level 2 Cd

Product Level 3 Cd


The data looks like this.

Product Rollup Name Product Level 1 Cd Product Level 2 Cd Product Level 3 Cd

Rlup1 Prd1 Prd2 Pens

Rlup2 Prd5 Prd8 Pens

Rlup3 Prd6 Prd7 Pens



In the above data “Pens” (Product Level 3 Cd) belong to all three Product Rollups i.e. Rlup1,Rlup2 and Rlup3. In this case ,if I drill down from “Rlup1” to “Pens” and select “Pens” in my prompt value I will get the data for all 3 rollups. Actually I want return the only “Rlup1”. I can get this, if I can select both Rollup and level in my prompt values. In MicroStrategy I want to have the functionality of having rollups and as well as there all subsequent levels in my prompt values.. How Can I do this? Any idea?

Appreaciate your help!!

Thanks,

 

[mstrNate]

How is your key (ID) built for each level of the hierarchy? For example, does Pens have a key (or ID) of 1 when you descend thru the Rlup1 hierarchy, and 2 for the Rlup2 hierarchy, and 3 for the Rlup3 hierarchy? Do you have a key value to go along with all those descriptions or are you just using the description to define the attribute? If you have a different key for each "Pens" value depending on what level you descended thru, then I think you should be able to get the rollup functionality you want.

Let me know if that's not clear, or if you have already defined the keys that way, or if I'm off base.

Nate

 

[RRCR]

Hi Nate,

I'm just using the description to define the attribute. I have a key in the table but which is not unique.

Assume that I have a unique key in my table. But I have the duplicate data product levels. ex:- for product level 1 cd I may have the "Prd1" more than one time for different keys and differnet hierarchies.If I create the attribute on key/ID for "product level 1 cd", I will get the all duplicate values in my prompt box. i.e if I have "Prd1" 100 times in the table I will get this prd1 100 times in my list box. But i want have only one value for one product. I think it is not possible.

Please let me know if you have any idea on reolving these kind of issues.

Thanks for your time and help.

 

[mstrNate]

After you get your key's unique, if you wanted, you could probably combine the descriptions with each other as you descend thru the hierarchy. For example:

Product Rollup Name Product Level 1 Cd Product Level 2 Cd Product Level 3 Cd

Rlup1 Rlup1 || Prd1 Rlup1 || Prd1 || Prd2 Rlup1 || Prd1 || Prd2 || Pens

Rlup2 Rlup2 || Prd5 Rlup2 || Prd5 || Prd8 Rlup2 || Prd5 || Prd8 || Pens

Rlup3 Rlup3 || Prd6 Rlup3 || Prd6 || Prd7 Rlup3 || Prd6 || Prd7 || Pens



That can be applied at both a description and key level. You can do a different variation of that as well, perhaps if you don't want to show all the values concatenated together like that, you could just concat the product rollup name with whatever level you're on (e.g. Rlup3 || Pens). I think something you want to work on also, is setting up surrogate keys for the dimensional data. If you haven't read Ralph Kimball's book(s), e.g. The Data Warehouse Lifecycle Toolkit, they might help. Let me know what you think.

Nate