Mosha’s teasing me now, I think. Anyway, here is a solution to the problem that is still AS2K-compatible, doesn’t hard-code any dimension names and doesn’t rely on having levels with set numbers of members:
WITH
MEMBER MEASURES.TEST AS ‘")"’
CELL CALCULATION DEMO FOR ‘({MEASURES.[TEST]})’ AS ‘
IIF(CALCULATIONCURRENTPASS()=129,"(", "") +
DIMENSIONS(129 – CALCULATIONCURRENTPASS()).CURRENTMEMBER.NAME +
IIF(CALCULATIONCURRENTPASS()=(130-DIMENSIONS.COUNT),"", ",")
+ CSTR(CALCULATIONPASSVALUE(MEASURES.TEST, -1, RELATIVE))
‘, CALCULATION_PASS_NUMBER=129, CALCULATION_PASS_DEPTH=128, SOLVE_ORDER=2, CONDITION=’CALCULATIONCURRENTPASS()>(129-DIMENSIONS.COUNT)’
SELECT {MEASURES.TEST} ON 0 FROM SALES
Since you can only have 128 dimensions in a cube in AS2K you’re safe on the hard-coded pass-depth, but it’s still not a truly satisfying solution for me. Unfortunately, until my Yukon test server is back working again (hurry up with the reinstallation, Colin!) I won’t be able to test any solutions that use new MDX functionality.
Jon Axon and I have just been discussing what the Yukon solution might be: as Jon pointed out, since Yukon automatically resolves infinite recursion by taking the value from the previous calculation pass, without using CalculationPassValue(), then the same approach as above would look a lot nicer when expressed in Yukon MDX. But Mosha did say that his solution used GENERATE() too, so I reckon he has a completely different way of doing it. I’m sure it will be blindingly obvious when he finally tells me what it is…
This is pretty impressive. Anyway, I have cheated a little bit, because the Yukon solution uses MDX function that you probably didn\’t hear about yet. As soon as it gets documented, we will post the solution here.
Well, February CTP is out, so I can tell you the solution now. February CTP contains .CurrentOrdinal function which allows you to get the position of the tuple currently being iterated in the set.