Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MDX Query Returning Multiple NULL Spend Amounts

Posted on 2009-04-10
1
Medium Priority
?
977 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

Introduction: This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes. It demonstrates how to build a report using SQL Ser…
Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

604 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