I tried to explain what I need in many ways.
I hope It wasn't useless attempts.
So Could anybody gives ideas?
MDX query for filter
-
- Regular Participant
- Posts: 202
- Joined: Sat Dec 04, 2010 2:35 pm
- OLAP Product: PAL
- Version: 2.0.9
- Excel Version: 2016
-
- Posts: 74
- Joined: Thu Jun 17, 2010 10:35 am
- OLAP Product: TM1
- Version: 9.4 9.5.1
- Excel Version: 2003 - 2007
Re: MDX query for filter
Hi EP_explorer,
As I said in some posts before, you have to use INSTR function (which is not documented so it can be not supported in further version)
That will give you something like that:
Function "FilterbyPattern" doesn't work for cube or alias values indeed.EP_explorer wrote:Function Filter with "*" doesn't work - return empty selection
Apparently it is trying to find string "*" but not any elements as in FilterbyPattern
As I said in some posts before, you have to use INSTR function (which is not documented so it can be not supported in further version)
That will give you something like that:
Code: Select all
{FILTER(
{
TM1FILTERBYLEVEL(
{TM1SUBSETALL( [Time Monthly] )}, 0
)
}
, INSTR( [Time Monthly].CurrentMember.Name , [System Information].([System Information Measures].[FilterVal], [System Information Values].[Value] ) > 0 )
)
-
- Regular Participant
- Posts: 202
- Joined: Sat Dec 04, 2010 2:35 pm
- OLAP Product: PAL
- Version: 2.0.9
- Excel Version: 2016
Re: MDX query for filter
Thank you a lot. It works - except one little thing.
If there are in Cube 1 in list are values
Region
1 Region1
2 Region2
3 Region11
4 Region3
and in Cube 2 user chooses Region1
it shows
Region
1 Region1
3 Region11
of course it is better than nothing but may be ways exists to solve the problem
If there are in Cube 1 in list are values
Region
1 Region1
2 Region2
3 Region11
4 Region3
and in Cube 2 user chooses Region1
it shows
Region
1 Region1
3 Region11
of course it is better than nothing but may be ways exists to solve the problem
-
- Posts: 74
- Joined: Thu Jun 17, 2010 10:35 am
- OLAP Product: TM1
- Version: 9.4 9.5.1
- Excel Version: 2003 - 2007
Re: MDX query for filter
I am not sure to know what you want but if it is to let users choosing to use or not wildcard search,
the only solution I have in mind is to add an extra indicator and some basic condition in your filter.
the only solution I have in mind is to add an extra indicator and some basic condition in your filter.
Code: Select all
{FILTER(
{
TM1FILTERBYLEVEL(
{TM1SUBSETALL( [Time Monthly] )}, 0
)
}
,
[System Information].([System Information Measures].[FilterVal], [System Information Values].[WildCard]) = "T"
AND
INSTR( [Time Monthly].CurrentMember.Name , [System Information].([System Information Measures].[FilterVal], [System Information Values].[Value] ) > 0 )
OR
[System Information].([System Information Measures].[FilterVal], [System Information Values].[WildCard]) = "F"
AND
[Time Monthly].CurrentMember.Name = [System Information].([System Information Measures].[FilterVal], [System Information Values].[Value] )
)}
-
- Regular Participant
- Posts: 202
- Joined: Sat Dec 04, 2010 2:35 pm
- OLAP Product: PAL
- Version: 2.0.9
- Excel Version: 2016
Re: MDX query for filter
Thank you - your last reply was helpful
I did what I wanted
I did what I wanted