Can I do this by MDX?

My fact table has a field QtyReleased. I would like to know the number of
records with QtyReleased > 1000.
Can we do this in SQL 2005 by MDX?
GuangmingI believe you can, but do you really need to do this with MDX? Why cant you
handle this on transactional side? Just make a field that contains 1 if
QtyReleased > 1000 and 0 if not. Then all you have to do is make a measure
from that field with a simple sum aggregate.
If you really need to do this with MDX look at the count functions..
MC
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com>
wrote in message news:6AAD5BCE-C77C-4D7E-BC0A-328628576F49@.microsoft.com...
> My fact table has a field QtyReleased. I would like to know the number of
> records with QtyReleased > 1000.
> Can we do this in SQL 2005 by MDX?
> Guangming
>|||MC is right, if you want a "record" count, add a calculated field on the
relational side and create a simple measure in the cube.
If however you want to calculate which hierarchy members have
QtyReleased > 1000, you would do this via MDX.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||I think the problem is coming from QtyReleased and Measure Count in same
measure dimention (fact table).
The rule is changing (QtyReleased > 1000 - > QtyReleased > 1500). I would
like to do it by MDX.
Count() does not take any condition like IIF. I tried both and not working.
Do you have any more thoughts?
Thanks,
Guangming
"Darren Gosbell" wrote:

> MC is right, if you want a "record" count, add a calculated field on the
> relational side and create a simple measure in the cube.
> If however you want to calculate which hierarchy members have
> QtyReleased > 1000, you would do this via MDX.
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>|||I still think MC's original suggestion is probably what you are after.
What you do is to create a view over your fact table and use the view in
your cube instead of using the fact table directly.
The view would look something like the following:
SELECT
..
<column list>
..
, CASE WHEN QtyReleased > 1000 THEN 1 ELSE 0 END AS QtyOverThreashold
FROM FactTable
Then you create a simple sum based measure over the QtyOverThreashold
column from the view.
If still think you need to do this in MDX (and you may - it's hard to
fully understand your situation over a newsgroup ). Then you need to
use the FILTER() function to give you the IIF type logic.
Assuming that you want to know the number of products with QtyReleased >
1000 the MDX would look something like this...
COUNT(
FILTER(
DESCENDANTS([Products].CurrentMember,,LEAVES)
,[Measures].[QtyReleased] > 1000
)
)
This gets the leaf level descendants of the currently selected member of
the product dimension and counts the number of members with QtyReleased
> 1000
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||> I tried the Count() one, which did not work. In my case
> [Measures].[QtyReleased] , and [Measures].[MeasureCount] a
re on Fact table
> as Measures, not as Dimensions.
> So I guess it does not work.
>
In that case just go with my first suggestion of creating a view with a
calculated column and create a measure in the cube off that.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell