Page 1 of 1
MDX Question
Posted: Thu Jul 10, 2008 1:56 pm
by 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.
Re: MDX Question
Posted: Thu Jul 10, 2008 2:15 pm
by 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)
Re: MDX Question
Posted: Thu Jul 10, 2008 2:18 pm
by jim wood
I think I get what you mean. It sounds to me like UNION is the new IF!
Re: MDX Question
Posted: Thu Jul 10, 2008 2:28 pm
by 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?
Re: MDX Question
Posted: Thu Jul 10, 2008 2:29 pm
by 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.
Re: MDX Question
Posted: Thu Jul 10, 2008 2:39 pm
by jim wood
Renaud MARTIAL wrote: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.
Re: MDX Question
Posted: Thu Jul 10, 2008 4:18 pm
by 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
)}
)}