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].[Tot al Spend Amount] > 99.99, [Measures].[Total Spend Amount],0),[Measures].[Tot al 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].[Tot al Spend],([Spend Date].[Calendar Year], [Measures].[Total Spend]))>0)
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].[Tot
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].[Tot
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.