Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Finding the currentmember on all dimensions, take#3

with 2 comments

Following on from Mosha’s comment revealing the new .CurrentOrdinal function, I thought I’d mention how this solves the problem even though, for me, it’s still not quite resolved…

After a bit of grovelling, Mosha sent me the following example of how .CurrentOrdinal works:

with member measures.x as

generate(strtoset("{"+rept("measures.defaultmember,", dimensions.count-1)+"measures.defaultmember}") as a,

dimensions(a.currentordinal-1).name + ",")

select x on 0 from [Sales]

The reason .CurrentOrdinal is the key is that it allows you to create a set containing n instances of the same member and still know what iteration you were on inside a GENERATE statement. This frees you from the restriction present in my first attempt at this problem, which relied on a level being present in your cube which had at least as many members on it as there were dimensions. Remember that without this new function, you could only use the RANK function to try to find the iteration number and this only worked if each of the members in the set were unique.

The problem I had was that the vba REPT function doesn’t work on my Yukon install, and I couldn’t find another way of generating a set containing n instances of the same member. However it seems to work for Mosha so I can only assume it’s a bug in the February CTP of some sort, and in later builds we’ll all be able to use this technique.

 

Written by Chris Webb

March 15, 2005 at 1:21 pm

Posted in MDX

2 Responses

Subscribe to comments with RSS.

  1. I finally understood why REPT worked for me, but didn\’t for you. REPT is _not_ a VBA function, rather it is imported from Excel. Since I have Excel installed – it works. But you probably don\’t – this is why it doesn\’t.

    Mosha

    March 18, 2005 at 3:43 am

  2. You\’re right, I don\’t have Excel installed on my test machine. That must be it.

    Chris

    March 18, 2005 at 9:47 am


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

Follow

Get every new post delivered to your Inbox.

Join 3,081 other followers

%d bloggers like this: