I'm trying to create a set of values in lower level dimension that is
filtered by the higher level dimension. For instance, I'm trying to run
the following MDX query on the Food Mart sample database:

WITH
SET [MySet] AS 'NonEmptyCrossjoin({[Customers].[State
Province].Members},{[Customers].[Country].[Canada]},1)'
SELECT [MySet] ON COLUMNS,
{[Measures].[Unit Sales]} ON ROWS
FROM Sales

I want the set MySet to include all state/provinces where the country
is equal to Canada. Note that Country is the first level in the
dimension and State Province is the second level.

The above query does not work - it always returns an empty set. How do
I fix this query to get it to work?

Thanks for your help!

Are you using AS2k? I get a syntax error when trying to run your sample
as you cannot crossjoin a dimension with itself. If you are dealing with
levels in a hierarchy, the normal way of getting members from a lower
level is to use the DESCENDANTS() function.

eg.

WITH
SET [MySet] AS 'Descendants([Customers].[Country].[USA],[Customers].
[State Province])'
SELECT [MySet] ON COLUMNS,
{[Measures].[Unit Sales]} ON ROWS
FROM Sales

Your other problem is that there is not data in foodmart under Canada!


--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

That works great.

Although, I think in the end I am just going to separate out the levels
into different dimensions so that I can cross-join at will. In my case
(not reflected in the sample query above) the two levels can be treated
independently even though they are related.

Thanks!

Make sure you have a look at virtual dimensions, as if you are splitting
one dimension with many levels into many dimensions with one level the
performance will degrade.

If you can look into attributes in AS2k5, it sounds like they might
match exactly what you are trying to achieve.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell