MDX Fillter in Cube

[bogdan]

Hello, I create an MDX query that filters by the value of the cube, a dimension which we asked, but why it does not work?


This query does not return result

Code: Select all

{Filter ({TM1SUBSETALL( [Model] )}, [cost centers mapping 4].(STRTOMEMBER('[OPEX cost centers].['+[OPEX cost centers].CurrentMember.Name+']'),  [!y22].[Choose]) = "1")}

And here works

Code: Select all

{Filter ({TM1SUBSETALL( [Model] )}, [cost centers mapping 4].(STRTOMEMBER('[OPEX cost centers].['+'206'+']'),  [!y22].[Choose]) = "1")}

Thanks

[Headon01]

Bogdan,

I hope I am reading this correctly but I believe you are trying to filter on a cube that has 3 dimensions ("Model", "OPEX cost centers", "!y22" variable?) and return the corresponding "Model" members. If this is true, you don't need to specify any dimension that uses all the members (in this case "OPEX cost centers"). So, you can use the MDX statement:

Code: Select all

Filter(TM1SUBSETALL( [Model] ), [cost centers mapping 4].([!y22].[Choose]) = "1")

I've also removed all the braces from the MDX statement because if the MDX function returns a set, you don't have to wrap it in the set braces. It won't hurt to leave them in but it makes it harder to read the statement.

Micheal

[bogdan]

I have cube "Measures" with dimensions ("Model", "OPEX cost centers", 'Business', 'Measures' ... etc) and cube 'Сost centers mapping 4' ("Model", "OPEX cost centers", "!y22")

And that I had to select dimension 'OPEX cost centers' in cube 'Measurs' measurement 'Model' has been filtered items from the cube 'Сost mapping centers 4' on the selected element from the dimension 'OPEX cost centers'.

For example in Cuba 'Сost mapping centers 4' such values.

Opex Cost Centers | Model | !y22
203 | BOO | 1
203 | BAA | 1
203 | CAA | 0
210 | BOO | 0
210 | BAA | 1
210 | CAA | 0

And that would be a value of 203 in the dimension "OPEX cost centers" in Cuba 'Measures' dimension 'Model' we had available values ('BOO', BAA '). And if you select '210' dimension 'Model' we had available values ('BAA ') .

Thanks

[blackhawk]

bogdan,

To be able to use CurrentMember it has to have some context for that dimension; which either must be for that dimension you are filtering or via a query to another cube. The MDX you supplied is a dimensional MDX expression not an MDX query. You are also working with [Model] dimension so there is no context of CurrentMember for the OPEX Cost Centers dimension.

Another way to think about it is this: While iterating through the Model dimension, how does your filter statement know which OPEX Cost Center to select and put into the expression?

You are either going to have to put in a cube expression or use a generate statement. I would consider using a cube query rather than a generate because a generate statement can be a performance issue. The cube query, though will have to be fully qualified to work. It is in the form of:

(CubeName).(dim1,dim2,dim3,...)

Hope that helps.

[bogdan]

bogdan,

To be able to use CurrentMember it has to have some context for that dimension; which either must be for that dimension you are filtering or via a query to another cube. The MDX you supplied is a dimensional MDX expression not an MDX query. You are also working with [Model] dimension so there is no context of CurrentMember for the OPEX Cost Centers dimension.

Another way to think about it is this: While iterating through the Model dimension, how does your filter statement know which OPEX Cost Center to select and put into the expression?

You are either going to have to put in a cube expression or use a generate statement. I would consider using a cube query rather than a generate because a generate statement can be a performance issue. The cube query, though will have to be fully qualified to work. It is in the form of:

(CubeName).(dim1,dim2,dim3,...)

Hope that helps.

You can throw an example of how to implement this task?

[tomok]

You can throw an example of how to implement this task?

This is must reading for anyone first learning MDX....http://www.bihints.com/creating_dynamic ... x_a_primer

[BariAbdul]

This is must reading for anyone first learning MDX....http://www.bihints.com/creating_dynamic ... x_a_primer

Thanks Tomok,This is the updated MDX Primer link:
http://www.pxbconsulting.com/downloads.html

[bogdan]

This is must reading for anyone first learning MDX....http://www.bihints.com/creating_dynamic ... x_a_primer

Thanks Tomok,This is the updated MDX Primer link:
http://www.pxbconsulting.com/downloads.html

I read this book. there is no information how to accomplish my task