MDX issue with SUM function and + operator

Hi,

Facing a problem with the sum and aggregate functions when using the + operator
The simplified representation of the problem is as below

The following query returns null.

[Measures].[xyz] is a base measure, no mdx calculation

with member TestValue as
'[Measures].[xyz] + [Measures].[xyz]'

member SummedValue as
'sum (
{
DimensionA.Level2.Member1,
DimensionA.Level2.Member2,
DimensionA.Level2.Member3,
DimensionA.Level2.Member4
}
,TestValue
)

select
SummedValue on columns
from <cube>

The above query returns a valid integer value

when TestValue is just '[Measures].[xyz]' and not the sum(+) or subtraction(-),
though it works when it is multiplication or division (* or /).

Is there anything wrong in the usage or is there a workaround.

Regards

I ran the following query against the AdventureWorks cube, and it delivered the desired results.

Code Snippet

with member TestValue as

'[Measures].[Internet Order Count]+[Measures].[Internet Order Count]'

member SummedValue as

'sum (

{

[Customer].[Customer Geography].[State-Province].&[FL]&[US],

[Customer].[Customer Geography].[State-Province].&[AL]&[US],

[Customer].[Customer Geography].[State-Province].&[GA]&[US],

[Customer].[Customer Geography].[State-Province].&[MI]&[US]

}

,TestValue

)'

select

SummedValue on columns

from [Adventure Works]

Can you give some details on how your cube differs from this?

|||

we have this problem only in a particular environment.

Is this dependent on Service packs of SQL Server or 32/64 bit versions of SQL Server.

Regards

|||I tested in an XP SP2 32-bit / SQL Server 2005 SP2 environment. I can try a few others tomorrow. It would be helpful if you let the forum know what type of environment you are running in.|||

This might be a bit of a long shot, but try changing your expression to make it a bit more explicit. The '+' operator is overloaded to also act as a union operator, because you are passing in two members, maybe SSAS is choosing to make a set of two members rather than doing a numeric addition.

Try changing your calc expression from

with member TestValue as
'[Measures].[xyz] + [Measures].[xyz]'

To:

with member TestValue as ([Measures].[xyz]) + ([Measures].[xyz])

Note that the single quotes around inline calculated members are no longer required in SSAS 2005 and in fact it is better not to include them as SSAS can then report the location of any errors with more accuracy. (plus you get better syntax coloring support in SSMS)

|||

thanks for the quick replies...

this does not work in a 64-bit SQL Server SP1 ......SSAS version 9.00.2176.00 environment

but works in 32-bit SQL Server SP2......SSAS version 9.00.3042.00

this lists the fixes in SQL Server 2005 SP2

http://support.microsoft.com/default.aspx/kb/921896

is it linked to this fix

919957 (http://support.microsoft.com/kb/919957/) FIX: Some cells return the NULL value instead of returning the actual value when you query a dimension that contains a parent/child hierarchy in a SQL Server 2005 Analysis Services cube

Regards