Chris Webb's BI Blog

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

Finding the currentmember on all dimensions, take#2

with 2 comments

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…

 

Written by Chris Webb

January 31, 2005 at 1:41 pm

Posted in MDX

2 Responses

Subscribe to comments with RSS.

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

    Mosha

    January 31, 2005 at 6:03 pm

  2. 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.

    Mosha

    March 4, 2005 at 5:57 pm


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,302 other followers