How can i use a subquery or (WITH)statment in MDX Query?

Question

I have a CustomerToFactor as a Measure and Customer as a Dimension. Now I want to create a MDX code like this SQL code but I can't. because (WITH) statements has another meaning in MDX.

    with Total_Customer(    
        select cus_id    
              ,sum(ctf_price) cus_total_price    
        from dbo.Customer    
        join dbo.CustomertoFactor on cus_id = ctf_cus_id    
        group by cus_id    
    )    
    select cus_id    
          ,cus_name    
          ,ctf_date    
          ,ctf_price    
          ,(cus_total_price / 100 * ctf_price) as Price_pro_customer    
    from dbo.Customer    
    join dbo.CustomertoFactor on cus_id = ctf_cus_id    
    join Total_Customer on Total_customer.cus_id = dbo.Customer.cus_id   


SELECT NON EMPTY { [Measures].[ctf_date]     
                  ,[Measures].[ctf_price] 
                  , (?)  Price_pro_customer    
                  } ON COLUMNS    
      ,NON EMPTY {[Customer].[Customer - cus_name].[Customer - cus_name].ALLMEMBERS}    
FROM [CustomerToFactor]  

---

Thanks for your Answers. but it doesn't work. Actually I want it to be grouped for every name you name. for Example: for the name Alex only the sum would have to be calculated for Alex(100+300 = 400) as well as Group by.

Answer 1

I do not really understand the point of the calculation :) But anyway, in MDX you can have your own measures calculated like this:

WITH MEMBER [Measures].[Price_pro_customer] AS 
        (SUM([Measures].[ctf_price]) / 100 * [Measures].[ctf_price])

SELECT NON EMPTY { [Measures].[ctf_date]     
                 ,[Measures].[ctf_price] 
                 ,[Measures].[Price_pro_customer] 
                 } ON COLUMNS    
        ,NON EMPTY {[Customer].[Customer - cus_name].[Customer - cus_name].ALLMEMBERS}    
FROM [CustomerToFactor]

I am not sure you'll get the same result as the SQL query though, since you have [Customer].[Customer - cus_name].[Customer - cus_name].ALLMEMBERS on the rows which basically does a GROUP BY on the customer name.

So if in the table you had several rows for the same customer the output of MDX query should be 1 row for each customer. The SUM([Measures].[ctf_price]) is also different since it sums over all customers

Answer 2

I think you should create a date dimension reference to ctf_date. Then your mdx should be as below:

WITH MEMBER [Measures].[Price_pro_customer] AS
SUM([DimDate].[ctf_date].[All], [Measures].[ctf_price]) / 100 * [Measures].[ctf_price]

SELECT NON EMPTY { 
                   [Measures].[ctf_price] ,
                   [Measures].[Price_pro_customer]    
                 } ON COLUMNS ,   
       NON EMPTY {[Customer].[Customer - cus_name].[Customer - cus_name].ALLMEMBERS * 
                  [DimDate].[ctf_date].[ctf_date].ALLMEMBERS} ON ROWS    
FROM [CustomerToFactor]