MDX Query

[garry cook]

Looking for a bit of help please. Got an MDX in a dimension called "Contingencies" as per the following - essentially, it pulls out a list of elements beginning with "financial contigency" that has data in a different cube (Contingency Challenge). Works fine so get Financial Contingency 1, 2, 3, etc returned where data exists in other cube.

{
TM1FILTERBYPATTERN(
{FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Contingencies] )}, 0)},
[Contingency Challenge].(
[Period].[2009 - P03],
[Project Title].[CSR 0001],
[Contingencies].CURRENTMEMBER
) > 0
)}, "financial contingency*"
)
}

The bit in bold is what I'm having a problem with. I've hardcoded in 2009 - P03 to give it the element reference for the "Period" dimension in the above example and it works fine.

What I'm now trying to do is replace that argument with a further MDX pull from a string cube called MDX Period. It's very simple, just a 2 dim cube with Project Title (which I've pulled in above as CSR 0001) and a string dim with one element "Current Period". Therefore it's just a list essentially.

However, when I put in

{
TM1FILTERBYPATTERN(
{FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Contingencies] )}, 0)},
[Contingency Challenge].(
[Period].{[MDX Period].(
[Project Title].[CSR 0001],
[General Text].[Current Period]
)}
,[Project Title].[CSR 0001],
[Contingencies].CURRENTMEMBER
) > 0
)}, "financial contingency*"
)
}

It fails even though the cross section of CSR 0001 and Current Period is "2009 - P03".

Syntax looks like it should be right to me but may be wrong or it may be something to do with it being pulled from the string cube but am going round in circles a bit here.

Probably clear as mud but if not, can anyone help?

TIA

[Mike Cowie]

Hi Garry,

The following won't work because the {} brackets indicate an MDX set, rather than something that can attach to/define an element:

Code: Select all

[Period].{[MDX Period].(
[Project Title].[CSR 0001], 
[General Text].[Current Period]
)}

So, one function you might try is the StrToMember function: http://technet.microsoft.com/en-us/libr ... 46022.aspx

I've gotten it to work in TM1 before, though it's success may vary based on your version. Taking your example here, I would try the following:

Code: Select all

{
TM1FILTERBYPATTERN(
{FILTER( 
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Contingencies] )}, 0)}, 
[Contingency Challenge].(
StrToMember('[Period].[' + [MDX Period].(
[Project Title].[CSR 0001], 
[General Text].[Current Period]
) + ']'),
[Project Title].[CSR 0001], 
[Contingencies].CURRENTMEMBER
) > 0
)}, "financial contingency*"
)
}

If it helps, you can separate out the StrToMember piece and validate it in your Period dimension first:

Code: Select all

{ StrToMember('[Period].[' + [MDX Period].(
[Project Title].[CSR 0001], 
[General Text].[Current Period]
) + ']') }

Note the {} around that because in order to use just this piece in a subset, you need it to return a set, which the {} braces do.

Anyway, hope this helps get you the rest of the way.

Regards,
Mike