Solved

MDX Query Returning Multiple NULL Spend Amounts

Posted on 2009-04-10
1
972 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:Kaporch
1 Comment
 

Accepted Solution

by:
Kaporch earned 0 total points
ID: 24118105
I got it myself by modifying the query that the .net designer created, as shown below.
WITH MEMBER [Sum Total Spend] as Sum(Filter ({[Spend Date].[Calendar Year], [Spend State].[Spend State Name], [Beneficiary].[Party Classification Type].[Party Category]}, [Measures].[Total Spend].Value > 0))
MEMBER [Measures].[Filter Sum Total Spend] AS Filter({[Spend Date].[Spend Date Calendar Year], [Spend State].[Spend State Name], [Beneficiary].[Party Classification Type].[Party Category]}, [Sum Total Spend].[Value] > 99.99) 
select [Beneficiary].[Beneficiary Full Name] on rows,
[Filter Sum Total Spend] on columns
from Spends
Where ([Spend State].[Spend State Name].[&West Virginia],
[Spend Date].[Calendar Year].&[2008], 
[Beneficiary].[Party Classification Type].[Party Category].&[Prescriber])

Open in new window

0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now