Hi Expert,
I am using an active form. I need to filtrate my first column with a value.
I created an MDX to do that and it works perfectly:
example:
{TM1FILTERBYPATTERN( {TM1DRILLDOWNMEMBER( {TM1SUBSETALL( [Classe] )}, ALL, RECURSIVE )}, '*am*')}
This will give me all the classes that contains AM
Now, I want the user to be able to enter am in a CELL (say $B$10).
How do I replace the '*am*' with the value in B10.
Thanks a million.
Micheline
PS: I tried the following as suggested in another chat, but it did not work
{TM1FILTERBYPATTERN( {TM1DRILLDOWNMEMBER( {TM1SUBSETALL( [Classe] )}, ALL, RECURSIVE )}, ' " &$B$10& " ' ) }
mdx replace value with excel coordinate
-
- Posts: 136
- Joined: Tue Aug 10, 2010 5:40 pm
- OLAP Product: Planning Analytics
- Version: 2.0
- Excel Version: EXCEL 2013
mdx replace value with excel coordinate
Thanks
Micheline
Micheline
-
- Posts: 136
- Joined: Tue Aug 10, 2010 5:40 pm
- OLAP Product: Planning Analytics
- Version: 2.0
- Excel Version: EXCEL 2013
Re: mdx replace value with excel coordinate
Correction.
I tried
{TM1FILTERBYPATTERN( {TM1DRILLDOWNMEMBER( {TM1SUBSETALL( [Classe] )}, ALL, RECURSIVE )}, ' " &$B$10& " ' ) }
in a different environment (I think excel is newer) it worked.
Lesson learnt.
thanks
Micheline
I tried
{TM1FILTERBYPATTERN( {TM1DRILLDOWNMEMBER( {TM1SUBSETALL( [Classe] )}, ALL, RECURSIVE )}, ' " &$B$10& " ' ) }
in a different environment (I think excel is newer) it worked.
Lesson learnt.
thanks
Micheline
Thanks
Micheline
Micheline
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: mdx replace value with excel coordinate
Hi,
Just for info, I have moved away from using TM1FILTERBYPATTERN because it can't handle aliases.
The following topics describe the issues, and the workarounds using the INSTR function.
regards,
Mark
Just for info, I have moved away from using TM1FILTERBYPATTERN because it can't handle aliases.
The following topics describe the issues, and the workarounds using the INSTR function.
regards,
Mark
-
- MVP
- Posts: 3128
- 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: mdx replace value with excel coordinate
TM1FILTERBYPATTERN can handle aliases. Provide the alias name as a third argument within double quotes. I only found out last week and it seems this was new info to the community.
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
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: mdx replace value with excel coordinate
Cheers Wim, I wasn't aware of the mystery 3rd parameter, and useful to know.
I should have read George Tonkin's MDX guide a bit more closely! That one went right over my head.
I should have read George Tonkin's MDX guide a bit more closely! That one went right over my head.
- gtonkin
- MVP
- Posts: 1211
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: mdx replace value with excel coordinate
3rd parameter is in my guide only because Wim found it!