Subset MDX Compile Error

[jim wood]

Guys,

I'm getting strange one. On a dimension called Project_Hierarchy (Don't ask) I am able to record an expression and the subset is created generating the following MDX:

{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Project_Hierarchy] )}, 0)}, [Project_Hierarchy].[Facility] = "A04")}

However when I go to stop recording and include the code within the subset I get the following error:

MDX Error.JPG (12.04 KiB) Viewed 3243 times

The Facility attribute is rule based pulling from a project attributes cube. I have checked and the rule is fed correctly.

Any ideas? I tried recording btw after I noticed that the subset (that was already created) was coming back with no content.

Cheers,

Jim.

PS. You all know how much I love MDX so you can guess how much fun I'm having with this.

[lotsaram]

Is "facility" an ambiguous name? As in is it the name of either an element or a subset in the project_hierarchy dimension?
You could get rid of the ambiguity in name or else use the full MDX syntax not the TM1 shortened syntax which would be something like [project_hierarchy].CurrentMember.Properties("facility").Value
(Or something like that, but don't take it verbatim as I'm on the train on the mobile)

[jim wood]

Brilliant thanks. I think you're on to something there. In all other cases the element attribute name differs slightly from the project attribute. This also is a defined level in the hierarchy. I'll try the full strength first and let you know.

Thanks again,

Jim.

PS. I still really don't like MDX

[jim wood]

Lotsaram,

You were very close and not a bad coding on your mobile. Once I took away the .value it worked a treat so the code finally looked like this:

Code: Select all

{TM1SORT( 
{
FILTER(
    {TM1SUBSETALL( [Project_Hierarchy] )},
    ([Project_Hierarchy].CurrentMember.Properties("Facility")="A04" OR [Project_Hierarchy].CurrentMember.Properties("Facility")="A43"  OR 
      [Project_Hierarchy].CurrentMember.Properties("Facility")="A44" OR [Project_Hierarchy].CurrentMember.Properties("Facility")="A49")
    )
}
, ASC)}

Thanks again,

Jim.

[jim wood]

After applying the code I'm seeing something really funny it keeps on disappearing which is very strange. Any ideas??