MDX Filter on cube by values

[Chuks]

Hi All,

I want to do a filter on an active form with 2 TM1RPT row dimensions to find out the list of combinations that match a string value in the cube.

Cube name : test
Dimensions: Dim1, Dim2, Dim3, Dim4,Dim5, Dim6, Dim7 and Test Measures.

Example data in Cube:

Dim1 Dim2 Type
Name1 D1 Active
Name2 D2 Active
Name3 D3 Inactive
Name4 D3 Active

I have used the below code, and as you can see expect Dim 1 and Dim2 all the other dimensions have value at a fixed element. I want to find out the elements from Dim1 which has type set to Active by considering all Elements from Dim2.

I wasn't sure how to omit Dim2 or Add "!" function in MDX similar to the one we have in rules and hence used Dim2. Current member.

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Dim1] )}, 0)},[test].([Dim2].CurrentMember,[Dim3].[E1],[Dim4].[E2],[Dim5].[E3],[Dim6].[E4],[Dim7].[E5],[Test Measures].[Type]) = "Active")}

Expected Result after applying MDX:

Dim1 Dim2 Type
Name1 D1 Active
Name2 D2 Active
Name4 D3 Active

Please let me know if the above result can be achieved..

Thanks in Advance!

[Mark RMBC]

I have tried in vain to achieve this and without any success! So would be very interested if anyone has a solution.

I have therefore done either of the following when faced with this issue (which I suspect has already crossed your mind!):

1) Created an attribute that mirrors the string element, in your case, [Test Measures].[Type] and have the MDX reference the attribute
2) Made sure I use numerics and not string for these type of flags. So in your case, Active would be 1 and Inactive 0 or whatever. This way the MDX is easier to compile.

regards, Mark

[Chuks]

I have tried in vain to achieve this and without any success! So would be very interested if anyone has a solution.

I have therefore done either of the following when faced with this issue (which I suspect has already crossed your mind!):

1) Created an attribute that mirrors the string element, in your case, [Test Measures].[Type] and have the MDX reference the attribute
2) Made sure I use numerics and not string for these type of flags. So in your case, Active would be 1 and Inactive 0 or whatever. This way the MDX is easier to compile.

regards, Mark

Hi Mark,

Thanks for your reply!

1) It has to be a measure because its based on 2 dimensions and cannot be associated with 1 single dimension., Unless i create a dimension combining both the Dim1 and Dim2 in which i would end up creating a lot of elements.

2) I also tried a filter on numeric measures, but it still didnt work.

[paulsimon]

Hi

I am not sure that you will get what you want as any MDX will only apply to a single dimension. I am not sure that an MDX expression in a TM1RPTROW on Dim1 is going to accept Current Member on Dim2.

If your Active Form is going to only be used in Excel and not TM1 Web then a simple solution would be to output the element where you store Active or not and then use a Data Filter in Excel.

Regards

Paul Simon