Link to home
Start Free TrialLog in
Avatar of Kaporch
Kaporch

asked on

MDX Query Returning Multiple NULL Spend Amounts

I have the following query in a SSRS report.  I've tried multiple ways to suppress nulls.  In the Spending Amount Column which I prepared, I've used:

IIF(Sum([Measures].[Total Spend]) <> null, Sum([Measurs].[Total Spend]), 0)
IIF(Sum([Measures].[Total Spend]) is not null, Sum([Measurs].[Total Spend]), 0)
FILTER(IIF([Measures].[Total Spend Amount] > 99.99, [Measures].[Total Spend Amount],0),[Measures].[Total Spend Amount]>0 and not isEmpty([Measures].[Total Spend Amount]))

I'm trying to get a sum of total spends greater than $99.99 dollars, and not include nulls in my result set.  Does anybody see what I'm doing wrong?

My current measure is:
Sum(Filter([Measures].[Total Spend],([Spend Date].[Calendar Year], [Measures].[Total Spend]))>0)


WITH MEMBER [Measures].[Sum Total Spend] AS Sum(Filter([Measures].[Total Spend],([Spend Date].[Calendar Year], [Measures].[Total Spend]))>0) SELECT NON EMPTY { [Measures].[Sum Total Spend] } ON COLUMNS, NON EMPTY { ([Beneficiary].[Full Name].[Full Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Spend Date].[Calendar Year].&[2008] } ) ON COLUMNS FROM ( SELECT ( { [Spend State].[Spend State Name].&[West Virginia] } ) ON COLUMNS FROM [Spends])) WHERE ( [Spend State].[Spend State Name].&[West Virginia], [Spend Date].[Calendar Year].&[2008] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Kaporch
Kaporch

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial