I am trying to filter a hierarchy with another hierarchy but I don't seem to be able to get it working.. I am not sure if it is possible but I can't even do that in the UI itself so I am not very sure.
I have two hierarchies called "Region - District" and "Account Unit - Account", what I want to do is simple -- filter the Account Unit - Account hierarchy with a member in Region - District.
Here's what I have tried but both return the whole thing for me. Any help will be appreciated!
WITH
MEMBER [Measures].[ParameterCaption] AS '[Dim Account].[Account Unit - Account].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Dim Account].[Account Unit - Account].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Dim Account].[Account Unit - Account].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT
{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
} ON COLUMNS ,
[Dim Account].[Account Unit - Account].ALLMEMBERS ON ROWS
from
(
SELECT ( STRTOSET("[Dim Geography].[Region - District].[Region].&[Central].&[Mid West]", CONSTRAINED)
)
ON COLUMNS
FROM [US Profiling Cube History]);

WITH
MEMBER [Measures].[ParameterCaption] AS '[Dim Account].[Account Unit - Account].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Dim Account].[Account Unit - Account].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Dim Account].[Account Unit - Account].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT
{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
} ON COLUMNS ,
[Dim Account].[Account Unit - Account].ALLMEMBERS ON ROWS
FROM [US Profiling Cube History]
where ([Dim Geography].[Region - District].[Region].&[Central].&[Mid West]);