MDX Filtering at fact level

Question

im pretty newbie at mdx sintaxys.

I have the next requirement that i need to be able to solve it using mdx, if its possible. I need to show the number of SALE transactions which amout value is greater than "X" , the number of SALE transactions which amount value is less than "Y" , the number of CREDIT transactions which amout value is greater than "Z". and so on. my cube has a measure called "amount" with a aggregate function "sum" and transactionNumber with a agregate function "count" and a time dimesion, transactionType dimension and others.

the thing is, that X, Y and Z are dynamics values and configured by users, i need to read those values, build the query and execute it vía xmla.

I'm wating a resultset as the next one

                  Greater than > 200 USD       less than < 0.10       total

          SALE            150                         10               300
          CREDIT          200                         30               600
          VODI            10                           2                60

any help you can provide me, i'll appreciate it

Darren Gosbell Darren Gosbell · Accepted Answer · 2010-03-09T03:20:22

This would only be possible if you had an attribute that was at the transaction level, otherwise your measures will be pre-aggregated to a higher level.

If you did have something like a [Transaction ID] attribute, you could write a query like the following.

WITH 
  MEMBER Measures.[Greater than 200 USD] as 
    SUM(Filter([Transaction ID].[Transaction ID].[Transaction ID], Measures.Amount > 200)
       , Measures.Count)
  MEMBER Measures.[Less than 0.10 USD] as 
    SUM(Filter([Transaction ID].[Transaction ID].[Transaction ID], Measures.Amount > 200)
       , Measures.Count)
  MEMBER Measures.Total as Measures.Count
SELECT
  {Measures.[Greater than 200 USD]
    ,Measures.[Less than 0.10 USD]
    ,Measures.[Total]} ON columns
 , [Transaction Type].[Transaction Type].[Transaction Type] ON Rows
FROM <Cube>

MDX Filtering at fact level

1 Answers