MDX wildcard filter based on cube value
-
- Posts: 27
- Joined: Mon Aug 21, 2017 2:14 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2016
MDX wildcard filter based on cube value
Hi all,
Just looking for a bit of advice compiling an MDX statement.
Here are some details:
I have a 2 dimensional cube called Sales which contains a Line dim and Measures.
I want to apply filters to the line dim based on values in the cube.
I'm able to apply filters properly when the value typed into the lookup cell matches exactly or is selected from a picklist, but I'm having trouble when the value typed into the lookup cell should be a wildcard search.
Here's a visual for more details. What I'm trying to achieve is the scenario 3 below.
Any advice would be greatly appreciated!
Just looking for a bit of advice compiling an MDX statement.
Here are some details:
I have a 2 dimensional cube called Sales which contains a Line dim and Measures.
I want to apply filters to the line dim based on values in the cube.
I'm able to apply filters properly when the value typed into the lookup cell matches exactly or is selected from a picklist, but I'm having trouble when the value typed into the lookup cell should be a wildcard search.
Here's a visual for more details. What I'm trying to achieve is the scenario 3 below.
Any advice would be greatly appreciated!
-
- 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: MDX wildcard filter based on cube value
You can use INSTR e.g.:
INSTR documentation: https://docs.microsoft.com/en-us/sql/md ... rver-ver15
Code: Select all
{ FILTER(
{ TM1SUBSETALL( [Line] ) },
INSTR([Sales].([Sales_m].[Manager]), "Bob") >0
)}
INSTR documentation: https://docs.microsoft.com/en-us/sql/md ... rver-ver15
Declan Rodger
-
- Posts: 27
- Joined: Mon Aug 21, 2017 2:14 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2016
Re: MDX wildcard filter based on cube value
Thank you Declan.
That works well.
One small wrinkle though if the lookup cell value is bob (lower case) instead of Bob this doesn't work.
I'd have to account for users typing in either I imagine.
I found this which actually works very well in PAW but not in PAX.
{FILTER([Line].MEMBERS,Instr(1,[Sales_m].[Manager],"bob",1)<>0)}
That works well.
One small wrinkle though if the lookup cell value is bob (lower case) instead of Bob this doesn't work.
I'd have to account for users typing in either I imagine.
I found this which actually works very well in PAW but not in PAX.
{FILTER([Line].MEMBERS,Instr(1,[Sales_m].[Manager],"bob",1)<>0)}
-
- 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 wildcard filter based on cube value
Hi,
imagine cell A1 contains the search text, e.g. Bob
To cater for lower or upper or any case input by the users you can do 2 things, firstly in another cell in the PAX worksheet have a formula something like =LOWER(A1), let us imagine that is in cell A2.
The MDX would then look something like this:
regards,
Mark
for PAX sheet,One small wrinkle though if the lookup cell value is bob (lower case) instead of Bob this doesn't work.
I'd have to account for users typing in either I imagine.
imagine cell A1 contains the search text, e.g. Bob
To cater for lower or upper or any case input by the users you can do 2 things, firstly in another cell in the PAX worksheet have a formula something like =LOWER(A1), let us imagine that is in cell A2.
The MDX would then look something like this:
Code: Select all
="{FILTER([Line].MEMBERS,Instr(1,LCASE([Sales_m].[Manager]),' " &A2& " ',1)<>0)}"
Mark
-
- 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: MDX wildcard filter based on cube value
You could also skip the additional excel cell and just put lcase around A2 also (in the MDX formula) - just for the benefit of keeping all of the "logic" in 1 place.Mark RMBC wrote: ↑Thu Apr 21, 2022 8:07 am To cater for lower or upper or any case input by the users you can do 2 things, firstly in another cell in the PAX worksheet have a formula something like =LOWER(A1), let us imagine that is in cell A2.
The MDX would then look something like this:
Code: Select all
="{FILTER([Line].MEMBERS,Instr(1,LCASE([Sales_m].[Manager]),' " &A2& " ',1)<>0)}"
Declan Rodger
-
- 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: MDX wildcard filter based on cube value
This is interesting - are you sure it is working differently in PAW to PAX?
This formula is pretty much the same as what I initially provided, the only real difference is instead of TM1SubsetAll it uses Members. This will give you the same result, as long as there is only 1 instance of each element in the hierarchy. If the same element exists under multiple parents, .members will give you multiple results.
But if it truly is working differently in PAW and PAX, then something sounds wrong. Both *SHOULD* be using the same engine.
Declan Rodger
-
- 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: MDX wildcard filter based on cube value
Just did a quick test. You are right, PAW is completely case insensitive and accepts upper or lower case for INSTR searches.
That's new information to me - I don't like the same MDX in different tools producing different results.
That's new information to me - I don't like the same MDX in different tools producing different results.
Declan Rodger
-
- 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 wildcard filter based on cube value
Hi,
regards,
Mark
Oh yeah!You could also skip the additional excel cell and just put lcase around A2 also (in the MDX formula) - just for the benefit of keeping all of the "logic" in 1 place.
News to me too and I don't like that either!Just did a quick test. You are right, PAW is completely case insensitive and accepts upper or lower case for INSTR searches.
That's new information to me - I don't like the same MDX in different tools producing different results.
regards,
Mark
- gtonkin
- MVP
- Posts: 1192
- 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 wildcard filter based on cube value
Not sure that your MDX is correct here as you dropped the reference to the Sales cube before addressing [Sales_m].[Manager]
Possibly this would work:
Code: Select all
{FILTER([Line].MEMBERS,Instr(1,[Sales].([Sales_m].[Manager]),"bob",1)<>0)}
I ran some rudimentary test in PAfE and setting the 4th argument to 0 only matches exact case, setting to 1 ignores case.
-
- Posts: 13
- Joined: Tue Dec 06, 2016 11:24 am
- OLAP Product: TM1
- Version: 10.3.0
- Excel Version: 2010
Re: MDX wildcard filter based on cube value
This is really helpful, thanks everyone.
While testing, I wrote the wrong dimension name in the cube, but it still worked (in PAfE)?
It doesn't work if you don't put in any dimension name.
'This one below works as well somehow, despite referring to the wrong dimension for Forename?
While testing, I wrote the wrong dimension name in the cube, but it still worked (in PAfE)?
It doesn't work if you don't put in any dimension name.
Code: Select all
{FILTER([Permanent Staff].MEMBERS,Instr(1,[Current year staff forecast].([Year for payroll total].[Forename]),"an",1)<>0)}
'This one below works as well somehow, despite referring to the wrong dimension for Forename?
Code: Select all
{FILTER([Permanent Staff].MEMBERS,Instr(1,[Current year staff forecast].([Permanent staff].[Forename]),"an",1)<>0)}