I'm new in the SSAS2005 world and was wondering if the following MDX script was good, especialy the non_empty_behavior part:
SCOPE ([ACCOUNTS].&[PU_MOI]);
This = Avg(
Descendants([ENTITIES].CurrentMember, , LEAVES) * Descendants([PRODUCTS].CurrentMember, , LEAVES)
);
Non_Empty_Behavior = [ACCOUNTS].&[PU_MOI];
END SCOPE;
As you can see, I want to display Averages for a special member of my ACCOUNTS dim and the same member is used in the non_empty_behavior property (if there is no data at an agregated level, there is not data on the leaves levels and so no need to calculate averages, at least I think so 
. Is it right ?
I also tried to code this with the new EXISTING function but it doesn't seem to work
SCOPE ([ACCOUNTS].&[PU_MOI]);
This = Avg(
EXISTING Leaves([ENTITIES]) * EXISTING Leaves([PRODUCTS])
);
Non_Empty_Behavior = [ACCOUNTS].&[PU_MOI];
END SCOPE;
Should I use EXISTING that way ?
Thx!
Unless you plan to support scenarios where there is multiselect on Entities or Products - leave EXISTING out of it - the Descendants function will work just fine. If you do need to support multiselect, than you can use EXISTING, but please don't use Leaves() function in the right-hand side of assignment. You can replace it with the key level of appropriate dimension. It would also help if Entities and Products were not parent-child dimensions.
Use of Non_Empty_Behavior here is probably OK, but only if you don't have some other calculations (or even unary operators) which can interfere with how PU_MOI is computed.
|||Thanks for your answer Mosha!
In fact, there may be a multi-select on Entities but the Average value wouldn't be of interest in that way so.. I also forgot to mention but Entities and Products are parent-child dims, so if I use the EXISTING formula, I have to use Leaves too, or it there another way ?
By the way, speaking of multi-selects, when retrieving coordinates of a cell from a MDX query result with a muti-select on Entities (from an MDX Query generated the old fashionned way, with an ugly Aggregate() function), how can I check that the Entity selected is not a real one but a calculation ? Maybe a Filter([Entities].Members) against the currently selected Entity ? Or is there a 'IsCalculatedMember' like function I could use ?
|||The fact that Entities and Products are parent-child will cause issues, but you still don't need to use Leaves function. Descendants with LEAVES flag is good enough here. For more differences between the two, check out this blog: http://www.sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx
> By the way, speaking of multi-selects, when retrieving coordinates of a cell from a MDX query result with a muti-select on Entities (from an MDX Query generated the old fashionned way, with an ugly Aggregate() function), how can I check that the Entity selected is not a real one but a calculation ?
Sorry, but I didn't understand the question.
