MDX wildcard filter based on cube value

Post Reply
CRP0021
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

Post by CRP0021 »

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!
Line Filters.png
Line Filters.png (42.67 KiB) Viewed 2160 times
declanr
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

Post by declanr »

You can use INSTR e.g.:

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
CRP0021
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

Post by CRP0021 »

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)}
Mark RMBC
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

Post by Mark RMBC »

Hi,
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.
for PAX sheet,

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)}"
regards,

Mark
declanr
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

Post by declanr »

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

Post by declanr »

CRP0021 wrote: Thu Apr 21, 2022 7:23 am 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)}
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
declanr
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

Post by declanr »

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

Post by Mark RMBC »

Hi,
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.
Oh yeah! ;)
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.
News to me too and I don't like that either!

regards,

Mark
User avatar
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

Post by gtonkin »

CRP0021 wrote: Thu Apr 21, 2022 7:23 am 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)}
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)}
Also, AFAIK, the 4th argument, that MS MDX says is ignored and is for compatibility, I have always used as a 1 to indicate ignore case.
I ran some rudimentary test in PAfE and setting the 4th argument to 0 only matches exact case, setting to 1 ignores case.
Keith Would
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

Post by Keith Would »

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.

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)}
Post Reply