TM1 MDX did not work in TI

[dharav9]

Hi, All

Can someone assist with following MDX to work in TI process?

sMDX = '{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SubsetToSet([Employee], "Dyna_Existing")}, 0)}, "*Hargrove,Chr*")}';

I placed this MDX inside the subset editor expression window and it worked. Same MDX is not working from the TI process.

Please Advise.

Thank You
Dharav

[gtonkin]

Not seen this before but try replacing the , in the wildcard string with another * and see if there is perhaps something going wrong due to a comma in the string.

[Elessar]

Hi,

Can the subset you are using be private?

[Mark RMBC]

Hi,

If the advise above does not work:

I wonder if you are running into the same issue I had here:

viewtopic.php?t=15087

This is an issue with FilterByPattern picking up an alias, which I presume is what Hargrove,Chr is? This was an issue with PAX, so never tested with a TI.

My workaround was to use Instr, so something like:

{FILTER({TM1FILTERBYLEVEL( TM1SubsetToSet([Employee], "Dyna_Existing"),0)}, INSTR([Employee].[AliasName], "Hargrove,Chr") <>0)}

Please note that Instr is case sensitive, so if you cannot be sure of the case then you may need to create another alias, which is all upper or all lower and make the Instr use Lcase or Ucase. Also note that Instr will check for spaces!

regards,

Mark

[lotsaram]

I think Mark RMBC correctly identified the issue. TM1FilterByPattern acts on the alias assigned to the subset. So if the wildcard searc on "*Hargrove,Chr*" relies on an alias then that is the reason.

For something that will ALWAYS work then you need to get the attribute value with CurrentMember.Properties("attributeName") and INSTR which is the equivalent of SCAN in MDX.

[Mark RMBC]

Hi lotsaram,

good point about the .Properties, I think that this is the only notation that will be acceptable in the next gen version of planning analytics, so mdx something like this:

Code: Select all

{FILTER({TM1FILTERBYLEVEL( TM1SubsetToSet([Employee], "Dyna_Existing"),0)}, INSTR([Employee].currentmember.Properties("AliasName"), "Hargrove,Chr") <>0)} 

regards,

Mark

[dharav9]

I think Mark RMBC correctly identified the issue. TM1FilterByPattern acts on the alias assigned to the subset. So if the wildcard searc on "*Hargrove,Chr*" relies on an alias then that is the reason.

For something that will ALWAYS work then you need to get the attribute value with CurrentMember.Properties("attributeName") and INSTR which is the equivalent of SCAN in MDX.

Thank You Lotsaram & Mark & all.

I had tried with INSTR function before I posted in the forum. I hadn't had utilize currentmemeber.properties extension. It work with that extension.

Appreciated.
Dharav