Creating Subset MDX with Alias
-
- Posts: 136
- Joined: Tue Aug 10, 2010 5:40 pm
- OLAP Product: Planning Analytics
- Version: 2.0
- Excel Version: EXCEL 2013
Creating Subset MDX with Alias
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.
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
Micheline
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Creating Subset MDX with Alias
What is the MDX and what does the dimension look like ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 1815
- 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
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":
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:
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:
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*")}
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)}
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
-
- MVP
- Posts: 3654
- 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
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.
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.
-
- Posts: 136
- 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
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
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
Micheline
-
- MVP
- Posts: 1815
- 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
So breaking down the MDX you already have:
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
Step 2: Filter the list from Step 1 to show only the elements where the principle name contains the text "pat":
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
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.
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.
Code: Select all
sMDX = '{TM1drilldownmember( {TM1FILTERBYpattern( {TM1SUBSETALL([ '| Dim1 |' ] )},'| pat |' )}, ALL, RECURSIVE )} ' ;
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}
Code: Select all
{FIlter(
{[Dim1].members},
INSTR ( 1, [Dim1].CurrentMember.Properties("Member_Name"),"pat", 1 )<>0
)}
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
)}
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
- PavoGa
- MVP
- Posts: 617
- 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: Charleston, Tennessee
Re: Creating Subset MDX with Alias
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
Cleveland, TN
-
- Posts: 136
- 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
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.
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
Micheline
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Creating Subset MDX with Alias
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:
Or even the Expand function:
It will not change the results but, at least to me, it's easier to grasp than the functions you had before.
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 | ' ))' );
Code: Select all
SubsetCreateByMDX( NEWSUB, Expand( 'Descendants( TM1FilterByPattern( TM1SubsetAll([%Dim1%]), %pat% ))' ));
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- PavoGa
- MVP
- Posts: 617
- 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: Charleston, Tennessee
Re: Creating Subset MDX with Alias
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: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.
Code: Select all
SubsetCreateByMDX( NEWSUB, 'TM1DRILLDOWNMEMBER(
FILTER(
EXCEPT( TM1SUBATALL( [Dim1] ), TM1FILTERBYLEVEL( TM1SUBSETALL( [Dim1] ), 0))
INSTR(1, [Dim1].[AliasName], "pat") > 0),
ALL, RECURSIVE)')
Ty
Cleveland, TN
Cleveland, TN