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.
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.
You\’re right, I don\’t have Excel installed on my test machine. That must be it.