Creating Subset MDX with Alias

Post Reply
mnasra
Posts: 116
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

Creating Subset MDX with Alias

Post by mnasra » Thu Feb 04, 2021 12:35 am

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.
Thanks
Micheline

Wim Gielis
MVP
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Creating Subset MDX with Alias

Post by Wim Gielis » Thu Feb 04, 2021 8:32 am

What is the MDX and what does the dimension look like ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

declanr
MVP
Posts: 1697
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Creating Subset MDX with Alias

Post by declanr » Thu Feb 04, 2021 8:49 am

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)}
Declan Rodger

lotsaram
MVP
Posts: 3477
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Creating Subset MDX with Alias

Post by lotsaram » Thu Feb 04, 2021 9:19 am

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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

mnasra
Posts: 116
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

Re: Creating Subset MDX with Alias

Post by mnasra » Thu Feb 04, 2021 10:47 pm

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
Thanks
Micheline

declanr
MVP
Posts: 1697
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Creating Subset MDX with Alias

Post by declanr » Fri Feb 05, 2021 7:02 am

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.
Declan Rodger

User avatar
PavoGa
MVP
Posts: 491
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Creating Subset MDX with Alias

Post by PavoGa » Fri Feb 05, 2021 1:52 pm

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.
Ty
Cleveland, TN

mnasra
Posts: 116
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

Re: Creating Subset MDX with Alias

Post by mnasra » Fri Feb 05, 2021 10:19 pm

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.
Thanks
Micheline

Wim Gielis
MVP
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Creating Subset MDX with Alias

Post by Wim Gielis » Fri Feb 05, 2021 10:32 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

User avatar
PavoGa
MVP
Posts: 491
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Creating Subset MDX with Alias

Post by PavoGa » Wed Feb 17, 2021 6:21 pm

mnasra wrote:
Fri Feb 05, 2021 10:19 pm
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.
Ty
Cleveland, TN

Post Reply