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…
Close, but still not universal – because it hardcodes the name of the dimension with at least as many members as there are dimensions (and assumes that such dimension exist). Also, probably there was no need to crossjoin with Customers.CurrentMember, since instead <set>.Current could\’ve been used for doing Rank. But this is probably as far as you can get with AS2K…