MDX Filter by string cell value (Updated)

[Bakkone]

UPDATED


Hi,

I have the following MDX subset that I use as a picklist. The plan is to have it change depending on a selection in another picklist.

{FILTER(
TM1SUBSETALL( [SubAccount] ),
[SubAccount].[MainAccount] = [Cube].([Measure].[MainAccountPicklist])
)}

The Im fairly sure problem is that the statement [Cube].([Measure].[MainAccountPicklist]) isnt recognized as a string. If I just write the account as "1910" everything works great.

How do I write [Cube].([Measure].[MainAccountPicklist]) for it to be acceptet by MDX as a string? I tried enclosing it in ' and " which of course makes the statement a string. I tried also tried enclosing a " with ' and adding the statement with +. But I couldn't get it to work.

Im sure someone here knows this by heart. I looked in the MDX Primer but it only states that this should work, and that strings need to be enclosed in " ", not how to used string cells.


Update:

It seems the problem is not the filter not understanding that the cell value is a string. The problem is that the picklist doesnt know which row it is on, and always
The MDX statement doesnt know which member on the row dimension it is at, and therefore dynamically updates according to the first element on the rows string value.

Rewritten MDX looks like this:

{FILTER(
TM1SUBSETALL( [SubAccount] ),
[SubAccount].[MainAccount] = [Cube].([Row].CurrentMember, [ColumnMeasure].[MainAccountPicklist])
)}

How would I get the MDX to understand on what row it is? The above statement does not work.

[ardi]

How many dimensions do you have in your Cube?

[Bakkone]

Hi Ardi,

Just updated my post since I realized what the actual problem was. Maybe you can still help me. The problem is in the rows.

[lotsaram]

Just updated my post since I realized what the actual problem was. Maybe you can still help me. The problem is in the rows.

The problem is in the rows but perhaps better put the problem is in your understanding of what you can do in the rows.

MDX cannot "know which row it is on" and show the "same subset" but with different members on each row. That's not the way it works. MDX can only take member context from title elements of a view, by definition row and column elements are unknown and have no fixed member as row or column elements could be any element from those dimensions.

What you could do to achieve the result that you want (usually called "cascading picklists") is to define different subsets using a naming convention such that the prior choice from the same row context can be picked up by a rule in the }Picklist cube which then selects (e.g. via attribute lookup) the correct picklist in the 2nd dimension. This isn't difficult to achieve but does require a bit of thought for the design and naming convention and more than likely some TI to automate production of the subsets. Depending on the particular use case and dimension sizes there could be a large number of subsets. (so you might need to consider a naming like }Picklist_something to hide the subsets in server explorer)

[Bakkone]

Hi,

Thx for the help. I went with the picklist-cube + defined subsets. Not as smooth as I want but it gets the job done.