Aggregate(), Sum() functions using calculated members does not work in Analysis Services 2005 SP2 (9.00.3042.00 version) but works in Analysis Services 2000 SP4

When any aggregate function, viz, Aggregate(), Sum() is used on the set of calculated members in Analysis Services 2005 SP2 (9.00.3042.00 version), they are not computing the value and return nulls instead where as the same functions work correctly in Analysis Services 2000 SP4. This seems to be a bug and hope that soon some hotfix would be released.

The workaround for this is not to use these functions for calculated members and rather calculate the values using the mathematical operator. e.s. instead of

'aggregate( [Organization].[Organizations].[Calc5],
                      [Organization].[Organizations].[Calc3],
                      [Organization].[Organizations].[Calc4],
                      [Organization].[Organizations].[Calc6],
                      [Organization].[Organizations].[Calc7]
} )'

define the formula as

'                    [Organization].[Organizations].[Calc5] +
                      [Organization].[Organizations].[Calc3] +
                      [Organization].[Organizations].[Calc4] +
                      [Organization].[Organizations].[Calc6] +
                      [Organization].[Organizations].[Calc7]
'

 

The issue is detailed in below section:

The below Adventureworks query gets the result properly for '[Organization].[Organizations].[SelectMembers] ' member:

with member [Organization].[Organizations].[SelectMembers] as 'aggregate(
     {    [Organization].[Organizations].&[5],
                      [Organization].[Organizations].&[3],
                      [Organization].[Organizations].&[4],
                      [Organization].[Organizations].&[6],
                      [Organization].[Organizations].&[7]}
)'
SELECT          {
                      [Date].[Fiscal Year].&[2002],
                      [Date].[Fiscal Year].&[2003],
                      [Date].[Fiscal Year].&[2004]
                }
                *
                {    [Organization].[Organizations].&[5],
                      [Organization].[Organizations].&[3],
                      [Organization].[Organizations].&[4],
                      [Organization].[Organizations].&[6],
                      [Organization].[Organizations].&[7],
                     [Organization].[Organizations].[SelectMembers]}
ON ROWS ,

           {[Account].[Accounts].&[101],[Account].[Accounts].&[52]
           }
ON COLUMNS
FROM [Finance]

However, when the organizations members are converted as calculated members and referred in the aggregate() formula, the values returned are "Null" for '[Organization].[Organizations].[SelectMembers]' member.

with
member [Organization].[Organizations].[Calc5] as '[Organization].[Organizations].&[5]'
member [Organization].[Organizations].[Calc3] as '[Organization].[Organizations].&[3]'
member [Organization].[Organizations].[Calc4] as '[Organization].[Organizations].&[4]'
member [Organization].[Organizations].[Calc6] as '[Organization].[Organizations].&[6]'
member [Organization].[Organizations].[Calc7] as '[Organization].[Organizations].&[7]'
member [Organization].[Organizations].[SelectMembers] as 'aggregate(
{                     [Organization].[Organizations].[Calc5],
                      [Organization].[Organizations].[Calc3],
                      [Organization].[Organizations].[Calc4],
                      [Organization].[Organizations].[Calc6],
                      [Organization].[Organizations].[Calc7]
} )'

SELECT          {
                      [Date].[Fiscal Year].&[2002],
                      [Date].[Fiscal Year].&[2003],
                      [Date].[Fiscal Year].&[2004]
                }
                *
                {    [Organization].[Organizations].[Calc5],
                      [Organization].[Organizations].[Calc3],
                      [Organization].[Organizations].[Calc4],
                      [Organization].[Organizations].[Calc6],
                      [Organization].[Organizations].[Calc7],
                      [Organization].[Organizations].[SelectMembers]
}
ON ROWS ,

           {[Account].[Accounts].&[101],[Account].[Accounts].&[52]
           }
ON COLUMNS
FROM [Finance]

However, the similar query for FoodMart2000 returns the same result for both the approach for '[Store].[SelectMembers] ' member:

with member [Store].[SelectMembers] as 'Aggregate(
{
[Store].[All Stores].[USA].[CA],
[Store].[All Stores].[USA].[OR],
[Store].[All Stores].[USA].[WA]}
)'
select crossjoin({[Time].[1997],[Time].[1998]} ,
{
[Store].[All Stores].[USA].[CA],
[Store].[All Stores].[USA].[OR],
[Store].[All Stores].[USA].[WA],
[Store].[SelectMembers] }
)
on rows,
{[Account].[All Account].[Net Income].[Net Sales].[Gross Sales],[Account].[All Account].[Net Income].[Net Sales].[Cost of Goods Sold]} on columns
from budget

The query returns the same result for '[Store].[SelectMembers]' member even though it is referring the calculated members:

with
member [Store].[All Stores].[USA].[CalcCA] as '[Store].[All Stores].[USA].[CA]'
member [Store].[All Stores].[USA].[CalcOR] as '[Store].[All Stores].[USA].[OR]'
member [Store].[All Stores].[USA].[CalcWA] as '[Store].[All Stores].[USA].[WA]'
member [Store].[SelectMembers] as 'Aggregate(
{
[Store].[All Stores].[USA].[CalcCA],
[Store].[All Stores].[USA].[CalcOR],
[Store].[All Stores].[USA].[CalcWA]}
)'
select crossjoin({[Time].[1997],[Time].[1998]} ,
{
[Store].[All Stores].[USA].[CalcCA],
[Store].[All Stores].[USA].[CalcOR],
[Store].[All Stores].[USA].[CalcWA],
[Store].[SelectMembers] }
)
on rows,
{[Account].[All Account].[Net Income].[Net Sales].[Gross Sales],[Account].[All Account].[Net Income].[Net Sales].[Cost of Goods Sold]} on columns
from budget