Dynamic Subset Based on Attribute of Another Dimension

[tomok]

I’m trying to create a dynamic subset where an element in a title dimension is based on an attribute of the selection in another title dimension and I am stuck. Here are the facts:

Cube has 7 dimensions:
Version
Source
Lender
Borrower
Note
Time
Measure

The Note dimension has individual notes where the numbering scheme is a leaf element from the Lender and Borrower dimensions, separated by a dash and then a suffix. Example, 002-004-U1, where 002 represents an element from Lender and 004 represent an element from Borrower. The Note also has a Lender and Borrower attribute. In this case the Lender attribute would be 002 and the Borrower attribute would be 004.

I have a cube view that has Version, Source, Lender, Borrower, Note as Titles, Time as rows and Measure as columns. I would like to have the selections for Lender and Borrower be dynamic, based on the Note#. For example, when I select 002-004-U1 I would like the Lender element resolve to 002 and Borrower to 004.
I know how to get this to work in an Active Form but I can’t seem to get it right in a cube view.

I tried this MDX statement for the dynamic subset on Lender:

Code: Select all

{FILTER({TM1SUBSETALL([Lender])},[Lender].CurrentMember.Name = [}ElementAttributes_Note].( [Note].[002-192-U1], [}ElementAttributes_Note].[Lender]))}

This works fine because I am explicitly naming the element from the Note dimension. I took out the reference to the actual member from the Note dimension, thinking TM1 will get the context from the view, i.e., plug in the Note from the selected Note element in the view:

Code: Select all

{FILTER({TM1SUBSETALL([Lender])},[Lender].CurrentMember.Name = [}ElementAttributes_Note].( [}ElementAttributes_Note].[Lender]))}

This does not work on 10.2.3. Any ideas?

[declanr]

I have only ever used such MDX on dimensions that were note Title Elements; by which I mean it has always worked for me when the dimension to be changed is a row or column but never when the one to be changed is a Title dimension itself.

Have you been able to get it to work in previous versions?

[tomok]

Have you been able to get it to work in previous versions?

No, this is my first attempt at trying this. I was just trying to make a view that was a little more user friendly. Currently, the user has to select the Note AND then select the Lender and the Borrower before they can enter any data. Was just looking for a way to automatically select both. I thought it would work considering I had already had the Lender and Borrower as an attribute of the Note. It appears that leaving off the specific note in the MDX does not result in TM1 picking the note from the context of the view, it simply defaults to picking the first element in the Note dimension.

[tomok]

Update:
It just doesn't seem to work when both dimensions are Title dimensions. However, moving the Lender and Borrower dimensions into either Rows or Columns makes it work. Not as aesthetically pleasing but OK.

[declanr]

Update:
It just doesn't seem to work when both dimensions are Title dimensions. However, moving the Lender and Borrower dimensions into either Rows or Columns makes it work. Not as aesthetically pleasing but OK.

Yes, that's how it has always worked for me. Not so bad in an active form as you can just hide the extra TM1RptRow Column. But in cubeviewers when the intention is for it to come back with just 1 element I have sometimes ended up with 2 or 3 single element dimensions stacked on top of my measures etc in order to get it to work; then sadly users move them out of the way as it takes up too much real estate on the screen... still better than them having to pick multiple elements mind you.

[Elessar]

Hi all,

Such linked Dynamic subsets, being both in title, work fine in PAW. Just checked it on 2.0.9, 2.0.52.
I don't know when it was introduced, but I guess that this works from the beginning of PAW life.