Creating Subset MDX with Alias

[mnasra]

Hi all,

I am trying to create a subset by mdx and assigning an alias. I found other threads in the forum suggestion to use subsetAliasSet
either i do it wrong, or I have a bug.
I can create the subset using MDX perfectly.
But if I put another line with SubsetAliasSet, the subset becomes empty but the Alias is Set.

Is there a way to put the alias in the mdx expression?

The weird part, is that the mdx expression is still in the dynamic subset (when displaying it), but it gives empty subset.

PS: I tried also, subsetMDXSet, same thing. because I have the subsetaliasset.

[Wim Gielis]

What is the MDX and what does the dimension look like ?

[declanr]

My guess is that you are using FilterByExpression to return members that match based on their name. The problem is that when you apply an alias, it will search the alias that is selected to match it against FilterByExpression.

So when you do that sort of thing you need to break the link between what is displayed and what you are searching.

For example if you had something like below to search for elements that contain the word "apples":

Code: Select all

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Store] )}, "*apples*")}

It might return 4 elements if the principle name for your dimension has 4 elements that contain "apples".
But, It would fail to return anything if your selected alias was just numeric codes.

In that case you could replace it with:

Code: Select all

{FIlter({[Store].members},INSTR ( 1, [Store].CurrentMember.Properties("Member_Name"),"apples", 1 )<>0)}

The "Member_Name" properties can be used to always query the principle name of the element. And INSTR is finding the position of your string (note it IS case sensitive.)


If its not the Principle Name but rather an alias that you are wanting to evaluate then you can just replace "Member_Name" with the alias name or you can go the shorthand route:

Code: Select all

{FIlter({[Store].members},INSTR ( 1, [Store].[Alias_Name],"apples", 1)<>0)}

[lotsaram]

As declanr said already, the TM1FilterByPattern MDX function acts on the alias which is assigned to the subset. So if the fiter relied on a wildcard match on the principal element name and this string isn't present in the alias then you will end up with a null set.

To build a more robust MDX you will need to use the MDX InStr function where you can then discriminate which property you want to use to search for the string match.

[mnasra]

Hi Experts,

you are all right. This is what I have:
Pat is my pattern (in this case, it is the rollup of the locations by branch). I am trying to create a subset of locations by branch.

SubsetcreatebyMDX(NEWSUB, '{TM1drilldownmember( {TM1FILTERBYpattern( {TM1SUBSETALL([ '| Dim1 |' ] )},
'| pat |' )}, ALL, RECURSIVE )} ' );

I obviously am not good with Mdx. I have been trying almost all day to use the INSTR, and I could not make it work (obviously I dont understand it).
I appreciate your help so much.
Thansks
Micheline

[declanr]

So breaking down the MDX you already have:

Code: Select all

sMDX = '{TM1drilldownmember( {TM1FILTERBYpattern( {TM1SUBSETALL([ '| Dim1 |' ] )},'| pat |' )}, ALL, RECURSIVE )} ' ;

You are finding all elements in Dim 1 that contain the text "pat" and then drilling down to show them and all of their descendants.
What I still don't know from the code is whether "pat" can be found in the principle name or an alias.

Step 1: Return all elements in Dimension 1

Code: Select all

{[Dim1].members}

Step 2: Filter the list from Step 1 to show only the elements where the principle name contains the text "pat":

Code: Select all

{FIlter(
   {[Dim1].members},
   INSTR ( 1, [Dim1].CurrentMember.Properties("Member_Name"),"pat", 1 )<>0
)}

Here the "INSTR" logic is relative to our Step 1 list.
The first 1 is used to say that it should start searching for "pat" at the first character of the elements name.
.CurrentMember.Properties("Member_Name") shows the Principle Name of the element. The "Member_Name" bit could be replaced with the name of an alias, if its the alias where "pat" appears.
I should add that the 1 at the end of it possibly isn't needed, it is supposed to be optional but there was a bug with INSTR back many versions ago that required you to use it and I've just kept throwing it in since.
The <> 0 Check is just saying to return only the elements that have pat in them, as ones that don't have pat would return 0. (You could swap it to just >0 if you prefer.)

Step 3: Drill Down your members from Step 2

Code: Select all

{TM1DrillDownMember(
   {FIlter(
      {[Dim1].members},
      INSTR ( 1, [Dim1].CurrentMember.Properties("Member_Name"),"pat", 1 )<>0
   )},
   All, RECURSIVE
)}

BUT... all of that is pointless. Since in your case PAT is a single element, that means you don;t need to search by expression.

Code: Select all

{TM1DrillDownMember(
   {[Dim1].[Pat]},
   All, Recursive
)}

This code {Dim1].[Pat]} will return the element pat. Then you apply the drill around that.

Edit - I added the bits after "BUT" after I reread the post. Figured I'd leave the first bit in for anyone else who stumbles here in the future.

[PavoGa]

Just a note, if your dimension contains elements that appear in multiple consolidations, .members will return each of those occurrences. TM1SUBSETALL will return only the unique members.

[mnasra]

Thank you all, but I think I misled you.
My mdx is doing the right thing except for 1 little (Big for me) detail. (it is not coming with my Alias called: description)

SubsetcreatebyMDX(NEWSUB, '{TM1drilldownmember( {TM1FILTERBYpattern( {TM1SUBSETALL([ '| Dim1 |' ] )},
'| pat |' )}, ALL, RECURSIVE )} ' );


I kind of resolved my problem, but I dont like the solution:
I created the subset with the mdx above.
I Set ALIAS in the same TI process.
The process gave me plenty of MINOR errors, but it created the subsets.

Then in Architect, in every single one of the subsets, I changed the mdx expression to the ALIAS of PAT.
it worked.
But if I run the process again, it is all gone.

When I tried to put the ALIAS of PAT in the mdx, it Aborts.

[Wim Gielis]

So the TI process creates multiple subsets, based on a loop of some sort ?
Do you each time destroy the subset(s) and recreate them again ?

Please consider this simpeler syntax:

Code: Select all

SubsetCreateByMDX( NEWSUB, 'Descendants( TM1FilterByPattern( TM1SubsetAll([ ' | Dim1 | ']), ' | pat | ' ))' );

Or even the Expand function:

Code: Select all

SubsetCreateByMDX( NEWSUB, Expand( 'Descendants( TM1FilterByPattern( TM1SubsetAll([%Dim1%]), %pat% ))' ));

It will not change the results but, at least to me, it's easier to grasp than the functions you had before.

[PavoGa]

Thank you all, but I think I misled you.
My mdx is doing the right thing except for 1 little (Big for me) detail. (it is not coming with my Alias called: description)

SubsetcreatebyMDX(NEWSUB, '{TM1drilldownmember( {TM1FILTERBYpattern( {TM1SUBSETALL([ '| Dim1 |' ] )},
'| pat |' )}, ALL, RECURSIVE )} ' );


I kind of resolved my problem, but I dont like the solution:
I created the subset with the mdx above.
I Set ALIAS in the same TI process.
The process gave me plenty of MINOR errors, but it created the subsets.

Then in Architect, in every single one of the subsets, I changed the mdx expression to the ALIAS of PAT.
it worked.
But if I run the process again, it is all gone.

When I tried to put the ALIAS of PAT in the mdx, it Aborts.

Okay, looking at your MDX statement: it is applying the pattern filter on leaves and consolidations, then trying to drill down on that set which includes the consolidations. Any leaf elements that do not match the pattern will show up as a result of the drill down. So my question is are you wanting the branches that match the pattern, the locations and branches that match the pattern, or the locations of branches that match the pattern. It does not sound like the first one, your MDX is trying to return the second (but depending on your data returning undesired elements), but some other statements made indicate you may be wanting the last of the three options (return the branches that match "pat" and their locations)? If the latter:

Code: Select all

SubsetCreateByMDX( NEWSUB, 'TM1DRILLDOWNMEMBER( 
    FILTER(
        EXCEPT( TM1SUBATALL( [Dim1] ), TM1FILTERBYLEVEL( TM1SUBSETALL( [Dim1] ), 0))
        INSTR(1, [Dim1].[AliasName], "pat") > 0),
    ALL, RECURSIVE)')

This MDX drills down and returns the consolidations that match the pattern and all of their children.