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…

One thought on “Finding the currentmember on all dimensions

  1. 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…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s