Mdx query

[AliUgur]

hi All,

I am new to MDX queries and I try to write these codes on Performance Modeler expression window but ı got "Invalid Subset Expressions" error. Any one can help please?

I copied the code from the ınternet but i didnt work . I think there is no syntax problem but..

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [3_Sira_No] )}, 0)},
[z_Krediler_Fiili].([1_Krediler].[KREDILER_ISTIRAK_ELIMINASYON]) > 0 )}

[Steve Rowe]

You need to have an explicit reference to all the dimensions in the cube, except the one you are querying against. Unless the cube z_Krediler_Fiili only has the two dimensions 3_Sira_No and KREDILER_ISTIRAK_ELIMINASYON then you need more references in the cube query part
"[z_Krediler_Fiili].([1_Krediler].[KREDILER_ISTIRAK_ELIMINASYON], [dim].[element] , ....) "

[Wim Gielis]

Also, did you check that you get the correct results if you leave out the part with Filter ?
Work from the inside to the outside and test every part of the MDX formula.If it works, add the next part to it.

[AliUgur]

Thanks for reply.

My cube has 3 dimension

3_Sira_No ( 1,2,3,4,5,6,7,......)
4_Aylar(31.10.2016, 30.09.2016,...)
1_Krediler( KREDILER_ISTIRAK_ELIMINASYON, Curreny, Balance,...)

I try to do that if KREDILER_ISTIRAK_ELIMINASYON value is >0, ı want to see at the 3_Sira_No dimension .
I wrote This one ;

{ FILTER( {TM1SUBSETALL ( [3_Sira_No] ) }
[z_Krediler_Fiili].([4_Aylar].[31.10.2016],[1_Krediler].[KREDILER_ISTIRAK_ELIMINASYON]) > 0 ) }

This is what you mean Steve?

And also you are right Wim . İts is better to start with small pieces.

[Steve Rowe]

Hi Yes, that looks to be the correct form but I can't check the syntax in detail.

[AliUgur]

But when ı write these codes . I got an error Invalid Subset Expression. But ı am sure do not have syntax error.

[Steve Rowe]

In the second example you are missing a comma between the two lines, not sure if this is a typo or not?

[AliUgur]

Yes , you are right.

{ FILTER( {TM1SUBSETALL( [3_Sira_No] )},
z_Krediler_Fiili.([4_Aylar].[31.10.2016],[1_Krediler].[DOVIZ]) = "TRY" )}

I write this one. As I see, when there is a syntax error even you can not apply it. There is someting wrong what ı write.
My code can not read the value TRY which is currency. I am missing someting but ı coulnt get it.

[declanr]

Yes , you are right.

{ FILTER( {TM1SUBSETALL( [3_Sira_No] )},
z_Krediler_Fiili.([4_Aylar].[31.10.2016],[1_Krediler].[DOVIZ]) = "TRY" )}

I write this one. As I see, when there is a syntax error even you can not apply it. There is someting wrong what ı write.
My code can not read the value TRY which is currency. I am missing someting but ı coulnt get it.

Your cube name should be in square brackets.

[Steve Rowe]

Suggest you google "MDX_Primer New Version" and use this as a base for the syntax, it has many excellent examples.

[AliUgur]

You are right declanr

{ FILTER( {TM1SUBSETALL( [3_Sira_No] )},
[z_Krediler_Fiili].([4_Aylar].[31.10.2016],[1_Krediler].[DOVIZ]) = "TRY" )}

ı corrected it. But still getting error "Invalid expression" I wrote this code to dimension [3_Sira_No] expression window is it right?

[AliUgur]

I think I solved the problem. My "3_Sira_No" dimension's elements are 1,2,3,4,5,6,....., 21.000.000 or maybe little more:)

Thats why mo codes are right but dimension is big. Thats why mdx can not run on this dimension.

What do you think guys?

[declanr]

I think I solved the problem. My "3_Sira_No" dimension's elements are 1,2,3,4,5,6,....., 21.000.000 or maybe little more:)

Thats why mo codes are right but dimension is big. Thats why mdx can not run on this dimension.

What do you think guys?

I've never heard of TM1 giving an invalid expression message for a dimension being large before; I have seen it accept the code and lock up while it tries to work it but never say its invalid. That being said I have never tried writing MDX in performance modeler before either.
Try writing it in an architect subset editor window and it may give you a slightly different more detailed error message (it also might not but its worth a go.)

[AliUgur]

IBM didnt say about anything dimension size. That's my opinion actually. Sorry for that explaniton.

I checked the code in another small cube it worked perceftly.

I tried the code at performance modeler and architect. But same exact error I got.

I will make a small video. and will send it here. Cause maybe ı do someting wrong and ı want to learn the reason. Thanks for reply declanr.

[AliUgur]

I tried to take a video of the mdx . Can you please check what ı do wrong about it?

[AliUgur]

As you said I checked the TM1 architect errors and ı found someting like on the file.

[declanr]

It appears that you were correct and it is size related.
I thiught you had meant it would not allow you to save the expression but the video shows thatbit is valid. You are however unable to see the list on your PC - using the MDX in a TI would prove that the expression is correct.

[AliUgur]

I checked the code . It is correct and ı learned MDX:)

Thanks to everybody for sharing knowledge...