Answering XRANGE Questions

So a recent post on OTN brought up the question of how dimension order affects the outputs from @XRANGE.  So let’s do a little bit of testing.

For testing this I'm building a simple BSO cube, I'll require at least 3 dimensions to test, so let’s keep it simple, Account, Period, Year.  Something like this:

Next I need a way to make sure I can tell what intersections actually were captured in my total, so I will use numbers starting at 1 and doubling every time it moves on (1,2,4,8,16 etc).. Sound kind of familiar? Yes it’s our old friend binary, by converting my result from decimal to binary I will see exactly which members were used

 

First thing I want to test is if the results change based on dimension order, so let’s sum up P4 of FY16 to P6 of FY17, quick calc script looks like this:

SumAccount(@SUMRANGE(Account1,@XRANGE(FY16->P4,FY17->P5));)

Result is: 131064

Which is exactly the expected result, but what if we change around our outline?

So I swap year and period dimensions in the outline, run the calc again and guess what happens... the same result again.  This is great, that's what we wanted to see, so this rules out outline order as having any affect.  But now that we know that, let’s see what else we can figure out.

What if I swap the parameters around?

SumAccount(@SUMRANGE(Account1,@XRANGE(P4->FY16,P5->FY17));)

Result is: 134316056

What it has actually summed up in this case is the following intersections:

P4->FY16
P4->FY17
P4->FY18
P5->FY16
P5->FY17

And if you look at the table, that makes total sense, it’s looping through the members, in the order they are presented to the function. What you told the function to do is, first look at P4, loop through all years from FY16 on, when you get to the end, and go to P5 and start years over at the first member, till you get to FY17.

And that's exactly what it did.

So there you have it, that is about as clear as it gets, the function is straight forward, and does only what you tell it to, right? well I did run into one issue, and if you figure out why before I do, please let me know, but when I run this calc, no matter how I fix on years, it will only ever calculate the first year in the fix:

FIX(FY16,FY18)
SumAccount(
@SUMRANGE(Account1,@XRANGE(FY16->P1,@CURRMBR(Year)->@CURRMBR(Period)));
)
ENDFIX

In this case only FY16 would be calculated, but if I did this:

FIX(FY18)
SumAccount(
@SUMRANGE(Account1,@XRANGE(FY16->P1,@CURRMBR(Year)->@CURRMBR(Period)));
)
ENDFIX

FY18 would be calculated, in order to calculate all 3 years, I needed to repeat the fix block 3 times, and I just can't figure out why.  If I use a single dimensional XRANGE like this:

FIX(FY16:FY18)
SumAccount(
@SUMRANGE(Account1,@XRANGE(P1,@CURRMBR(Period)));
)
ENDFIX

It works as expected.  So if anyone knows why, please do fill in the blanks for me, but I think this could be a bug.