Create dynamic subset with another dimension subset

[conray]

Hello everyone,

i am a fairly new developer in using Cognos TM1.

I would like to know if it is possible to create a dynamic subset for a dimension, based on another selected dimension.

For example:
Dimension A [Year]
2011
2012
2013

DImension B [Period]
201104
201105
201204
201205

In the cube viewer, when i choose [Year] as the title, i would like the following to be displayed:
[Year]: 2011
[Period]
|201104 |201105
Rows 0 0

[Year]: 2012
[Period]
|201204 |201205
Rows 0 0

I am not sure if this is the correct way to do it (using mdx expressions?), so please point me to any references if you have any.

Thanks,
ray

[lotsaram]

You obviously didn't try searching too hard, this was discussed about a week back ....
http://www.tm1forum.com/viewtopic.php?f=3&t=5584#p23794

[conray]

i did see that topic before i posted but i am still unsure how does the expression actually works.

Say for example, i have 2 dimension, 1 is Product and 1 is Relation.
In Product dimension, i have:
P123
P456

in Relation dimension, i have:
P123 | A
P123 | B
P123 | C
P456 | D
P456 | E
P456 | F

So suppose i have selected P123 in the product dimension, i should only be able to see P123 | A, P123 | B and P123 | C.

Now, the expression should be written in the Relation dimension?
i should have something like {FILTER({TM1SubsetAll([Relation])},[Relation]..... ( i am not sure how the syntax should be like)

[tomok]

You can't do what you are asking. The problem is how is the Relation dimension supposed to know what you have "selected" in the Product dimension? To write an MDX statement to filter the Relation dimension in the way you want you would have to store your Product dimension selection in a cube and then reference that in the MDX. You can create a cube that utilizes the }Clients dimenson so you cand store the Product selection but you would have to deal with creating the DBSW fornula in your input template or report to write the selected product in the selection cube before the MDX will return the list from the Relation dimension. If you are using an active form report you can also assemble the MDX in the report itself and reference it in the TM1RPTROW formula. Either will work but the point is you have to be referencing a specific Product before the subset in the Relation dimension will evaluate properly.

[jstrygner]

i did see that topic before i posted but i am still unsure how does the expression actually works.

Found some time to try to achieve what (I think) you need. The solution that worked for me is very similar to the case lotsaram gave you a link to, just requires a small trick with attributes you don't have but can easily create.

Here is the step by step instruction and below you have corresponding picture:
1. Create ProductCode text attribute for both dimensions and fill it with values like on the picture. I did it manually, but you can also do it e.g. via rules (if the algorithm is as simple as in your example).
2. Create a subset for the Relation dimension ({FILTER( {TM1SubsetAll( [Relation] )}, [Relation].[ProductCode] = [Product].[ProductCode])}).
3. Create a cube and attach the created in step 2. Relation subset either to rows or columns while your Product remains as title dimension. Switch between elements in Product dimension and recalculate to see the result.

HTH

[conray]

Hi jstrygner,

That is exactly what i required!

Thanks so much for the advice.

[Pulpa Chamoy]

Hello,
Your example works perfect, but when I add a consolidated element to dimension product is not selecting anything.
I tried filtering level 0 first with no luck. Can you please help?

Thank you.

[dharav]

Hello,
Your example works perfect, but when I add a consolidated element to dimension product is not selecting anything.
I tried filtering level 0 first with no luck. Can you please help?

Thank you.

Did You resolve the issue? I would like to know how?

I achieved same using MDX Statement where i utilised comparision of 2 dimension attributes and than drilldown the member.