MDX Question

[jim wood]

Hi Guys,

I have been dipping my toe in to the world of MDX after going through the excellent documentation posted on this forum. (Many thanks Philip.) Basically I am trying to find any outlet code that starts L0, L1, L2, L3, L4, L5, L6, L7, L8 or L9. I have done this within the subset editor for now and I have created the following code:

{TM1SORT({UNION(
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l0*")},
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l1*")},
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l2*")},
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l3*")},
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l4*")},
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l5*")},
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l6*")},
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l7*")},
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l8*")},
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l9*")})}
,ASC)}

I have tried changing things round a bit to try and get it working but I have had no success. Is my problem that UNION only works in TI or is my first stab at code got errors in?

Can anyone help?

Cheers,

Jim.

[AnthonyT]

Not that I am in any way proficient with MDX, but I think that your example will only do a UNION between the first two statements, if at all

I would either try to limit the FILTERBYPATTERN to look for anything that starts with an "l" or try the following

Code: Select all

TM1SORT(

UNION(
UNION(
UNION(
UNION(
UNION(
UNION(
UNION(
UNION(

{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l0*")},
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l1*")}),
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l2*")}),
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l3*")}),
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l4*")}),
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l5*")}),
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l6*")}),
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l7*")}),
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l8*")}),
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [taps outlet] )}, 0)}, "l9*")}
)
,ASC)

Incase I have typed this incorrectly - I am doing a UNION join with the result of the preceeding join

(I am sure that someone has a far better way of doing this - always ready to learn)

[jim wood]

I think I get what you mean. It sounds to me like UNION is the new IF!

[jim wood]

Within the definition of UNION on MSDN I found the following: "This function returns the union of two or more specified sets."

This would tend to suggest that you can bring together multiple sets and you are not limited to 2. Is any limitation due to TM1 compatibility issues?

[Renaud MARTIAL]

Hello,

I think you don't have to use 'union'. Just add members between {}, separated by a comma.

Try the following

Code: Select all

TM1SORT(
  TM1FilterByLevel( 
      { TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L0*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L1*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L2*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L3*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L4*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L5*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L6*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L7*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L8*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L9*") 
      }
      , 0 )
  , ASC )

Regards,

Renaud.

[jim wood]

Hello,

I think you don't have to use 'union'. Just add members between {}, separated by a comma.

Try the following

Code: Select all

TM1SORT(
  TM1FilterByLevel( 
      { TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L0*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L1*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L2*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L3*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L4*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L5*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L6*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L7*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L8*") ,
        TM1FilterByPattern(  {TM1SUBSETALL( [taps outlet] )}, "L9*") 
      }
      , 0 )
  , ASC )

Regards,

Renaud.

That worked a treat. Thanks for your help guys.

[Steve Vincent]

i think you'll find UNION has to be the first thing in the MDX, i've used it quite sucessfully in MDX as in the following;

Code: Select all

{UNION (
{FILTER (
  {TM1SUBSETALL( [Ops Plan OBS] )}
,  ([ops plan].([Version].[Current Prog Req Supp], [Ops Plan Measures].[Ops Plan Headcount]))
 - ([ops plan].([Version].[Current Agreed Sol], [Ops Plan Measures].[Ops Plan Headcount]))
 > [ops plan].([Version].[Current Prog Req Supp], [Ops Plan Measures].[Ops Plan Headcount]) * 0.01
)}
,
{FILTER (
  {TM1SUBSETALL( [Ops Plan OBS] )}
,  ([ops plan].([Version].[Current Agreed Sol], [Ops Plan Measures].[Ops Plan Headcount]))
 - ([ops plan].([Version].[Current Prog Req Supp], [Ops Plan Measures].[Ops Plan Headcount]))
 > [ops plan].([Version].[Current Prog Req Supp], [Ops Plan Measures].[Ops Plan Headcount]) * 0.01
)}
)}