I want to filter dimension member by user input. I have an 'age' dimension, who's members consist of lets say 10, 11, 12, ..., 20, say the user wants only data from ages <= 12, the resulting table should look like

measure1 measure
--------------------------------------------
10 xxxx xxx
11 xxxx xxx
12 xxxx xxx

Is there a way to limit dimension member using the FILTER keyword? If not any suggestions will be greatly appreciated.

If you can construct the MDX from user input, then like:

StrToValue(Age.CurrentMember.Name) <= 12)
- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thank you so much. However I can't quite get it to run, I got a syntax error (token is not valid, apparently because of the "All Age" member, any idea on how to get around it?) when using the StrToValue function in the Filter statement

select
{[Measures].[Id]} on columns,
{Filter([age].Members, StrToValue([age].CurrentMember.Name) < 12)} on row
from tes

However I changed StrToValue to VBA!Value and it works like a charm

select
{[Measures].[Id]} on columns,
{Filter([age].Members, VBA!Val([age].CurrentMember.Name) < 12)} on row
from tes

I was under the impression that VBA!Val and StrToValue are equivalent

----- Deepak Puri wrote: ----

If you can construct the MDX from user input, then like

StrToValue(Age.CurrentMember.Name) <= 12


- Deepa

*** Sent via Developersdex http://www.developersdex.com **
Don't just participate in USENET...get rewarded for it

VBA!Val and StrToValue are not quite equivalent - I don't know all the
differences, but in your case Val() may be evaluating the [All Age]
member name as 0, whereas StrToValue() raises an error, since it is not
numeric.

My suggested MDX: 'Age.Years.Members' lists the members of the "Years"
level of the [Age] dimension (all of which presumably have names that
are numeric strings). This excludes the [All Age] member, whose level is
higher.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!