Search code examples
sqlmdxolap-cubessas-2008

MDX query adding SUM column


I have written the following MDX

WITH MEMBER [Measures].[SumReturns] AS
  ([Dimension1].[GCode].defaultmember,[Measures].[GScore Sum])  

SELECT
   non empty  
   (  

      {[Dimension1].[Description].children},  
      {[Dimension1].[GCode].children},  
      {
         [Measures].[GScore Sum],  
         [Measures].[SumReturns]  
      }

   )  
   on columns,  
   non empty
   (
      [Dimension2].[DCode].[DCode] *  
      [Dimension2].[DName].[DName] *   
      [Dimension2.[Barcode].[Barcode] *  
      [Dimension2].[LN].[LName] *  
      [Dimension2].[FN].[FName]   

   )
   on rows
FROM [MCube]
WHERE 
(
   {[Dimension2].[HARC].[DCode].&[0000]}
)  

The query is giving me the results below:

Actual Results

But what I expected instead was these results:

Expected Results

Any suggestions on how to get the desired results?


Solution

  • Here is the query that worked to get the required results. No need of calculated measure

     select  
        non empty  
        (      
        [Dimension1].[Description].children,  
        [Dimension1].[GCode].members,  
        [Measures].[GScore Sum]   
        )  
        on columns,  
        non empty
        (
        [Dimension2].[DCode].[DCode] *  
        [Dimension2].[DName].[DName] *   
        [Dimension2.[Barcode].[Barcode] *  
        [Dimension2].[LN].[LName] *  
        [Dimension2].[FN].[FName]   
    
        )
        on rows
        from 
        [MCube]
        where 
        (
    
        {[Dimension2].[HARC].[DCode].&[0000]}
    
        )