Creating a subset by filtering cube values

[AlastairFM]

Hi,

I am trying to create a subset for all those elements for which the cell value for those elements starts with some specified text. The cube I am using is basically a 2 dimensional cube were the last dimension consists of 2 elements ; one a string element that holds the name and another a numeric element that holds the value I wish to return.

In other words I am trying to filter a dimension's elements by a cube's values in order to generate a subset. Of course I can create a simple TI process to do this and using action buttons to kick them off this works very well, but does anyone have an idea's how this can be done with an MDX statement ?

You can of course use 'TM1FILTERBYPATTERN' to perform pattern (wildcard) matching on element names but as far as I am aware you cannot pattern match on attribute values or cell values (which are the same really).

You can use MDX to test against a cube's value but only for equality testing (as below) - no wildcards.

Code: Select all

{FILTER( {TM1SUBSETALL( [Records] )}, [Record Amount].[Name] = "Adam")}

Any suggestions would be appreciated.

Thanks.

[lotsaram]

Yes this should be possible.

starting from the simplest case to match an element name

Code: Select all

{FILTER(
  {TM1SUBSETALL( [ADimension] )},
  [ADimension].CurrentMember.Name = ACube.([Dim1].[Ele1], [Dim2].[Ele2])
)}

... and if you want all elements starting with a string then replace the = with > (you would probably also need to add an AND condition to the filter as also < whatever the next letter of the alphabet is).

[rozef]

You can also use the well non referenced MDX function INSTR which filter by pattern alias or cube values :
(It would be probably too easy if it was documented)

Code: Select all

{ FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [MyDim] )}, 0)}, INSTR([MyDim].[MyAlias], "String" ) > 0 ) }

Cheers,