Solved

MDX Sum Filter

Posted on 2009-03-31
2
1,937 Views
Last Modified: 2012-05-06
I have the following MDX query.  I am trying to select members with sum totals greater than $500 for a quarter.  I tried adding a filter without the sum and > than 500, that worked.  When I added the sum and greater than sign, I got the error: Query (26, 2) The > function expects a string or numeric expression for the 1 argument. A tuple set expression was used.

I am new to MDX and so I'm not sure what I need to change.  Please let me know.
--works fine
select {[Party Role].[Beneficiary].[Beneficiary Role].&[Host]} ON COLUMNS,
{Filter ([Year Quarter Desc].Members, [Measures].[Total Spend Amount]> 500)} ON ROWS
FROM Spends
 
--doing a sum and greater than does not work
select {[Party Role].[Beneficiary].[Beneficiary Role].&[Host]} ON COLUMNS,
{Filter ([Year Quarter Desc].Members, sum([Measures].[Total Spend Amount])) > 500} ON ROWS
FROM Spends

Open in new window

0
Comment
Question by:Kaporch
2 Comments
 

Accepted Solution

by:
Kaporch earned 0 total points
ID: 24038485
I think I might have found the answer on my own, below.
WITH MEMBER Measures.Year AS SUM
	([Year Quarter Desc].&[2008/Q1], [Measures].[Total Spend Amount])
	select [Activity Start Date].[Date] ON COLUMNS,
	{Filter ([Year Quarter Desc].&[2008/Q1], [Measures].[Year] > 500), 
	Filter([Year Quarter Desc].&[2008/Q2], [Measures].[Year] > 500)} ON ROWS
	from spends
WHERE {[Party Role].[Beneficiary].[Beneficiary Role].&[Host]}

Open in new window

0
 

Expert Comment

by:neasar
ID: 33625529
Thanks :) very handy indeed.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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