How to filter a dimension based on an attribute value of a specific element?

[fcapell]

Hi everyone!

I was tasked to build a report based on a TM1 cube. Here is the architecture:
- Cognos BI 10.2.0
- TM1 9.5.2

I have this dimension which contains stores. It has several root members which represent different kinds of aggregations: geography, revenues cluster, etc. Each store has more than one parent.

This report has a value prompt on which user selects store from a combo box (which is populated by a query). This report also has some crosstabs. One of them has to present the chosen store and its revenue cluster, which consists of a consolidated member. But I'm having trouble selecting this parent member.

I have tried the ancestors function, but it won't return the correct consolidated member. I guess it's because the store has more than one parent.

This dimension has an attribute which contains the cluster name. So I'm trying to use this attribute to select the consolidated member. But I just can't figure it out how to accomplish this. I mean, for a given dimension element, I have an attribute containing the other dimension element I need. How can I bring this second dimension element into the query?

Any suggestions would be gratefully appreciated  :)

Best regards,

Fausto