NON EMPTY issue

Apologies if this is a bit simple but it's doing my head in a bit

Why would the following MDX return 4 correct records

Code Snippet

SELECT { [Measures].[Signed Surface Area], [Measures].[Amount Signed] } ON COLUMNS ,

non empty{ DESCENDANTS( [Property].[Customer].[All Property], [Property].[Customer].[Customer] ) } ON ROWS

FROM [Lease]

WHERE ( [Time Calcs].[YTD],[Time].[Year].&[2007].&[2].&[5], [Currency].&[26], [Unit of Measure].&[2] )

however adding another measure returns 50 additional spurious measures, all with blank rows

Code Snippet

SELECT NON EMPTY { [Measures].[Signed Leases], [Measures].[Signed Surface Area], [Measures].[Amount Signed] } ON COLUMNS ,

NON EMPTY { DESCENDANTS( [Property].[Customer].[All Property], [Property].[Customer].[Customer] ) } ON ROWS

FROM [Lease]

WHERE ( [Time].[Year].&[2007].&[2].&[5], [Currency].&[26], [Time Calcs].[YTD], [Unit of Measure].&[2] )

The "signed leases" measure was the field added

Presumably you mean that adding the Signed Leases measure returns another 50 spurious rows, not measures? Is Signed Leases a calculated measure, and if so what is its definition? I'm guessing [Time Calcs].[YTD] is another calculated measure (and is probably the culprit) - what is its definition? Have you been using the Non_Empty_Behavior property anywhere?

Chris

|||

Chris,

Yes, you were correct in your assumption. TimeCalcs was OK, it was actually the fact that signed leases was calculated. Adding an iif statement to create a null sorted it