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?
Guangming
I 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] are 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