Finding the currentmember on all dimensions

In yesterday’s entry I mentioned it would be nice if you could iterate over all the dimensions in your cube and find the currentmember on each of them. Mosha then hinted that this was possible, and there’s nothing I like more than a challenge. Here’s the solution in a Foodmart 2000 query - and it uses all-AS2K functionality too!

WITH MEMBER MEASURES.TEST AS ‘GENERATE(
HEAD(
UNION(
[Customers].[City].MEMBERS
, {CUSTOMERS.CURRENTMEMBER} AS CURRENTCUST
), DIMENSIONS.COUNT)
, IIF(
DIMENSIONS(RANK(CUSTOMERS.CURRENTMEMBER, [Customers].[City].MEMBERS)-1)
IS CUSTOMERS
, CURRENTCUST.ITEM(0).ITEM(0).NAME
, DIMENSIONS(RANK(CUSTOMERS.CURRENTMEMBER, [Customers].[City].MEMBERS)-1).CURRENTMEMBER.NAME
), ", ")’
SELECT {MEASURES.TEST} ON 0, 
[Education Level].[Education Level].MEMBERS ON 1
FROM SALES
WHERE([Customers].[All Customers].[Canada].[BC].[Burnaby],[Gender].[All Gender].[F] )

All you need is a level with more members than you have dimensions in the cube. OK, it’s not elegant, but it works…