I found this tip for checking if a cell is at the leaf but I can't seem to
make the translation into my cube (I'm not that great with multi-dimensional
calcs):

http://sqljunkies.com/WebLog/mosha/a...egory/356.aspx
" Now, what if the knowledge whether the member (or cell) is a measure group
leaf or not is needed inside a calculation ? Frankly, we didn¹t think that
this would be an interesting scenario, and therefore AS2005 doesn¹t have MDX
function to check it. Recently, however, we have run into a couple of
customers who had such a requirement. In order to solve it, the following
workaround can be used:
CREATE IsLeafInsideFoo = false;
SCOPE (MeasureGroupMeasures("foo"),Leaves());
IsLeafInsideFoo = true;
END SCOPE;"

I had done something similar with a calculated member called IsUpdateable
but my expression is probably far more brute force:

IIF(NOT IsLeaf([Account].CURRENTMEMBER), 0, (IIF(NOT
ISLEAF([Year].CURRENTMEMBER), 0, (IIF(NOT ISLEAF([Scenario].CURRENTMEMBER),
0, (IIF(NOT ISLEAF([Market].CURRENTMEMBER), 0, (IIF(NOT
ISLEAF([Product].CURRENTMEMBER), 0, 1)))))))))

In essence testing the current member from each dimension if it is the leaf
and then returning a 0 or 1 accordingly.

I tried the obvious but that simply returns false everywhere (Sales is the
name of the Measures Group).

CREATE IsUpdatable = false;
SCOPE (MeasureGroupMeasures("Sales"),Leaves());
IsUpdatable = true;
END SCOPE;

What am I missing and is there an elegant solution to my enormous IIF block
using SCOPE? Any tips/pointers appreciated.

Follow-up: All my dimensions are Parent-Child which may be the reason
Leaves() isn't returning the values one would expect?


On 9/14/06 12:18 PM, in article C12EF918.1F75%zgrose (AT) mac (DOT) com, "Zoltan Grose"
<zgrose (AT) mac (DOT) com> wrote:

"Follow-up: All my dimensions are Parent-Child which may be the reason
Leaves() isn't returning the values one would expect?"

Well, Mosha hinted in his blog entry that Parent-Child hierarchies were
a special case, but didn't elaborate:

http://sqljunkies.com/WebLog/mosha/a...egory/356.aspx

- Parent Child Hierarchies

- Difference between Data Members and Leaf members

- Placeholder Members

However, we will not discuss them here, perhaps in another article.

Based on my testing with the P-C Employee hierarchy in the "Sales
Targets" measure group of Adventure Works, the values returned are,
indeed, all false; whereas with the Date hierarchy, values for quarters
(i.e. the granularity attribute) are true.

However, Mosha also drew a distinction between hierarchy and measure
group leaves - your IsUpdateable member detects a hierarchy leaf,
whereas the IsLeafInsideFoo member detects a measure group leaf - which
do you want?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Right. That got me worried. =)

Always hard for me to wrap my brain around the difference between the two. I
only have one measure group (Sales) with a single measure (Amount) and I'm
trying to determine with IsUpdateable if a send to that cell will be "pure"
or if it will be allocated to children. For example, reject data that is
attempting to write to Year.[Quarter 1] but allow data to Year.[Quarter
1].January.

I was able to clean up my expression a bit to get rid of the comma spam and
deep nesting. Currently I have:

CREATE IsUpdateable=0;
SCOPE (
Descendants([Account], , LEAVES),
Descendants([Year], , LEAVES),
Descendants([Scenario], , LEAVES),
Descendants([Market], , LEAVES),
Descendants([Product], , LEAVES)
);
IsUpdateable = 1;
END SCOPE

This expression becomes a LOT more readable for troubleshooting. Always
looking to improve though. Especially if I can make it dynamic enough (like
Leaves() would suggest) so that a single expression can be used no matter
the number of dimensions in the cube.

-zoltan


On 9/14/06 8:08 PM, in article OBPj3QH2GHA.3656 (AT) TK2MSFTNGP04 (DOT) phx.gbl,
"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote: