You may already be aware – possibly the hard way – that solve order has changed in AS2005. Notably, calculated members defined with query or session scope can’t have an effective solve order lower than any calculated member defined on the server, the only exceptions being calculated members which use the AGGREGATE() function and (I believe) ones which use visualtotals functionality.
Here’s an example of this happening, using the [Gross Profit Margin] calculated measure in AdventureWorks:
with
member [Product].[Category].AggTest as Aggregate([Product].[Category].[Category].members)member
[Product].[Category].SumTest as Sum([Product].[Category].[Category].members)select
[Measures].[Gross Profit Margin] on 0,[Product].[Category].
allmembers on 1from
[Adventure Works]
In this case you can see Aggtest is evaluated at a lower solve order than [Gross Profit Margin], whereas Sumtest is evaluated at a higher solve order. MDX Solutions has a great writeup of this behaviour (courtesy of George) so I suggest you buy a copy if you’d like to know more about the details; it gets even more fun when you want to create calculated members with query or session scope which have solve orders higher than some server calcs and lower than others – one way of doing this is to use SUM() for the former and AGGREGATE for the latter depending on what the currentmember on Measures is, eg IIF(Measures.CurrentMember is Measures.ABC, SUM([MySet]), AGGREGATE([MySet])).
What isn’t in the book, though, is what has been happening since RTM. One post on the MSDN forums that I’ve pointed to before refers to a property called CalculationPassMode which will be in SP1:
However, as Mosha comments in his reply, there are some unpleasant side effects. There is apparently another property coming called SCOPE_ISOLATION which won’t be in SP1 which will give us back the AS2000 behaviour of query and server solve orders working properly together. I’ll let you know if and when it does appear.
