Wednesday, August 12, 2015

Using Filter() in ASO Allocations and Procedural Calcs


 The following statement worked in 11.1.2.2.100. Pay special attention to the green section. This Filter() statement serves to pull all level 0 members from the Measures dimension and remove any that are dynamic (MEMBER_TYPE of 2). Since dynamic members aren't allowed in a POV, this is a very useful command.


execute allocation process on database ASOSamp.Sample with
POV "Crossjoin( {FILTER([Measures].LEVELS(0).Members,[Measures].CurrentMember.MEMBER_TYPE <> 2)},
Crossjoin( {[Curr Year]},
Crossjoin( {[Jan]},
Crossjoin( {[Sale]},
Crossjoin( {[Cash]},
Crossjoin( {[Coupon]},
Crossjoin( {[1 to 13 Years]},
Crossjoin( {[Under 20,000]},
Crossjoin( {[004118]},
{[Digital Cameras]})))))))))"
AMOUNT "100"
TARGET ""
RANGE "{[80101]}"
SPREAD;

The previous statement throws an error in 11.1.2.4.002.

ERROR - 1300040 - Argument [Target] is missing dimension [Measures].
ERROR - 1241192 - Allocation terminated with Essbase error 1300040 in TARGET.

It's acting like I never bothered mentioning the Measures dimension.

Now there is some debate as to whether you should be able to use Filter() in a POV. The POV is a an MDX Set after all. So maybe we were getting a free ride for the past couple of years? I'm using Filter() all over the place in my ASO calcs and allocations. It would be a shame to lose the functionality. Hopefully I'm just doing something wrong. Has anyone else tried this in 11.1.2.4?

Update on 9/9/2015: This issue has been raised with Oracle and defect 21798739 has been assigned.

Update on 12/14/2015: This issue has been fixed in 11.1.4.2.006.

No comments: