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
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>