TM1 Forum

Hi,

I am wondering whether it is possible to write an mdx to achieve the following.

Suppose I have these dimensions

Dim1: Site1, Site 2, Site 3 (L0)
Dim2: Site 1 (L1) - children Costcenter1, Costcenter 2 (L0)
Site 2 (L1)......

Suppose I have a cube that uses both of these dimensions.
Now I would like the view to automatically change the selection in dim2 to the children of whatever was selected in Dim1.
I.e. If I select Site1 in dimension 1, I would like the selection in Dim2 to automatically change to Costcenter1 & 2
If I the select Site2 in dimension 1, I would like the selection in dim2 to adjust itself to costcenter3, 4,...

I can do this in an excel sheet no problem but I would very much like to have this behaviour directly in a view (for use in contributor).

Is this possible?

Thx,
Jeroen

I don't know but this question has been coming up a lot of late.

Possibly the CurrentMember property might work but I haven't had a chance to test it.

Assuming there is a viable relationship between dimensions it can be done. I do stuff like this all the time, where the choice from one dimension dictates the selectable list from a another and so on. The caveat is that when doing it from a view, everything has to be done via subsets and there is no way in a view to hold user state. What I mean by that is there is no way for Dim2 to know that you selected a particular element from Dim1. You have to record that selection from Dim1 into a cube so you can write an MDX statement in Dim2 to pull the elements you want. I create a User Parameters cube that includes the }Clients dimension to store these values. However, you have to WRITE the selections to the cube and if you want everything to be views you would have to have 2 views, one for the User Parameters cube where you record the user selections (so the MDX code could evaluate the subset(s)) and the other to show the results. This really doesn't work nicely unless it is done via an Excel/TM1 Web sheet. I don't believe you can isolate this into a single view using the CurrentMember property because Dim2 has no way of knowing what the CurrentMember from Dim1 is unless that member from Dim1 is stored somewhere.