Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MDX Query Returning Multiple NULL Spend Amounts

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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…
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. …
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. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month13 days, 9 hours left to enroll

580 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