Solved

MDX Query Returning Multiple NULL Spend Amounts

Posted on 2009-04-10
1
975 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

728 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