Change subset dimension on change other dimension element

[Rosanero4Ever]

Hi all,

using cube viewer do you think is possible change a dimension subset automatically after changing an element of another dimension?
I would like associate a particular dimension subset to a particular element of another dimension.
For example, I could associate 2012 with a particular subset of costs while 2011 with another subset of costs.
Do you think this is possible?
Thanks in advance

[cgaunt]

I have never done it directly in cube viewer, but I have used VB code and the 'E_Pick' VB Macro to call a specific subset. You can predicate this on other values in your excel spreadsheet and this gives the appearance of the value of one element deriving the members of another subset. In reality, you have prepared 2 subsets and you just call them depending on the value that you have elsewhere in your spreadsheet.

Unless anyone has a an inspired piece of insight on how else this can be done automatically in the cube viewer, I would consider building a subset based on an attribute filter. As per your example, you can associate the year to the required dimension cost element(s). It still requires a user to select the correct subset so may not fit the automation requirement you are looking for.

[Rosanero4Ever]

Hi,

thanks for your answer.
At the moment I'm using the solution you wrote, i.e, I have many cost subsets. After changing year I must edit the subset of my cost dimension.
This isn't good because if the user forgets to perform this action, results are obviously wrong.
Regards

[cgaunt]

If the final results are sent to excel, then I would suggest using a VB control in excel to put in place the effect that you are looking to acheive. You have the subsets in place already, you just need a way to enforce the correct one is used. It is opening up a whole host of additional VB, because selecting the right subset is just one part of it. You will have to then go to the trouble of additionally extracting the data form TM1 to excel, formatiing it etc. Its not long before that VB becomes the major element of your deliverable.

Another alternative is to use an active form. You can build an expression that uses values elsewhere on your spreadsheet in your TM1RPTROW formula. When you update your sheet, it will pick the correct subset. Again, this does not acheive your original aim of doing this in Cube viewer directly.

Let see if anyone else has some valuable ideas as to how else this can be acheived.

[lotsaram]

Have a look at http://www.tm1forum.com/viewtopic.php?f=3&t=5584#p23794

[cgaunt]

Like Jstrynger originally wrote..... I never know this was possible. I too had to test it as like most of us, we dont belive things until we see them. Guess what? it worked. I had always predicated on the assumption that MDX statements can not look accross multiple dimensions, but here it does.

Back to the OP, this will work if the 2011 and 2012 accounts are unique to these years. In the event that there is a required account that falls into both years, this method would fall over. I will have a think about this and see if I can think of a solution.

[Rosanero4Ever]

Hi,

following the cgaunt advice, I solved creating a websheet (so i don't use cube viewer).
In this websheet I put a condition on the year:
if year=2012 then subset1 else subset2
I think isn't the best solution but it works well and is transparent to the user.
Thanks all