Tuesday, June 21, 2011

MDX for retrieving the sum of first 5 months for each year


Sometimes client comes with the requirement in which they want a report which should show the SUM of first 5 months or sum of first 6 months from each year. So let’s discuss this by using MDX. You can create MDX for getting sum of first five months in the following manner. I have build the below MDX on adventureworks sample.

WITH 
  MEMBER
 [Measures].[Sum Of First Five Months] AS 
    Sum

    (
      {
          StrToMember

          ("[Date].[Calendar].[Month].&["

              + 
                [Date].[Calendar Year].CurrentMember
.Member_Key
            + "]&[1]"

          )
        : 
          StrToMember

          ("[Date].[Calendar].[Month].&["

              + 
                [Date].[Calendar Year].CurrentMember
.Member_Key
            + "]&[5]"

          )
      }
     ,[Measures].[Reseller Sales Amount]
    ) 
SELECT 
  {[Measures].[Sum Of First Five Months]} ON 0
 ,[Date].[Calendar].[Calendar Year].MEMBERS
 ON 1
FROM [Adventure Works];


Following is the output of above mdx query.




Value $7,221,202.69 is the SUM of "Reseller Sales Amount" for first 5 months (i.e. Jan,Feb,March,April,May) of year 2006.

You can achieve same output by using different MDX.


WITH 
  
MEMBER [Measures].[Sum Of First Five Months] AS 
    
Sum
    (
      {
          
Descendants
          (
            [Date].[Calendar].
CurrentMember
           ,[Date].[Calendar].[Month]
           ,SELF
          ).
Item(0)
        : 
          
Descendants
          (
            [Date].[Calendar].
CurrentMember
           ,[Date].[Calendar].[Month]
           ,SELF
          ).
Item(4)
      }
     ,[Measures].[Reseller Sales Amount]
    ) 
SELECT  
  {[Measures].[Sum Of First Five Months]} ON COLUMNS
 ,{
    [Date].[Calendar].[Calendar Year].&[2006]
   ,[Date].[Calendar].[Calendar Year].&[2007]
   ,[Date].[Calendar].[Calendar Year].&[2008]
  } ON ROWS
FROM [Adventure Works]; 


If you execute above MDX query then you will get the same output as first one.

1 comment:

  1. Hi,

    DESCRIPCION FECHA AAAAA
    VENTAS 03/09/2012 10
    11/09/2012 10
    12/09/2012 10
    17/09/2012 10
    20/09/2012 10
    25/09/2012 10
    TOTAL 10
    LIQUIDACION 07/09/2012 3
    11/09/2012 3
    12/09/2012 3
    17/09/2012 3
    20/09/2012 3
    25/09/2012 3
    TOTAL 3
    TOTAL 13

    Thanks in advance for any help






    ReplyDelete