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.

Get the list of SSAS Databases

Most of the times, I found question on MSDN forums that “how to get the list of SSAS databases?”. So I thought lets create one post to answer this question.

If you are using SSAS 2008 or higher versions then you can use following DMV (Dynamic Management Views). Open your MDX query pane in SSMS and execute below query.

SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS

You can also retrieve the list of SSAS Databases by using AMO (Analysis Management Views). but for getting a list of all SSAS databases using AMO, your active directory user should have administrator privileges on SSAS server.

For using AMO, you need the reference of “Microsoft.AnalysisServices.dll” in your project. After referencing dll, Import it for using different classes of that dll (i.e Imports Microsoft.AnalysisServices).

Following is the sample code for getting the list of SSAS databases.

Dim strDatabaseName As String

Dim objServer As Server

objServer = New Server

objServer.Connect("localhost")

If objServer.Databases.Count > 0 Then

   For Each objDatabase As Database In objServer.Databases

        strDatabaseName = objDatabase.Name

   Next

End If

objServer.Disconnect()

Retrieve selected year data and same level data from previous year

Sometimes user comes with the requirement, in which they want to compare “this year data with the previous year’s data”, “this quarter data with the previous year’s same quarter data”, ”selected month data with the previous year’s same month data”, “selected day’s data with last year’s same day data”. This requirement is very general in most of the organizations as they want to analyze the trend of their business by comparing current data with previous one. We can solve such requirements by using some MDX functions. So let’s discuss this with some sample MDX queries.

Calculate selected year data and the previous year data:

Firstly we discuss on how to retrieve previous year data by using MDX and for that we can use MDX ParallelPeriod function.

Syntax of ParallelPeriod function is as follows;

ParallelPeriod( [Level_Expression [ ,Index [ , Member_Expression ] ] ] )

If you execute following MDX query it will return data for supplied year i.e. 2003 and previous year data i.e.2002. So basically we are using ParallelPeriod function in which we used “Calendar Year” level for getting Year level data and we have passed Index as 1 for getting last year.

WITH 
  MEMBER [Measures].[Last Year Data] AS 
    (
      ParallelPeriod
      (
        [Date].[Calendar].[Calendar Year]
       ,1
       ,[Date].[Calendar].CurrentMember
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Last Year Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2003];

If you are going to pass a member from “Calendar Year” level directly (i.e. [Date].[Calendar].[Calendar Year].&[2003]) then you can achieve your requirement by using MDX LAG function also. Following is the sample MDX in which I have used LAG function.

WITH 
  MEMBER [Measures].[Last Year Data] AS 
    (
      [Date].[Calendar].CurrentMember.Lag(1)
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Last Year Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE  ([Date].[Calendar].[Calendar Year].&[2003]);

In some cases user wants to pass particular date and want to view selected date’s year data and previous year’s data. In such requirement you can use MDX ANCESTOR function. In the following MDX query we are passing Date and retrieving data as per year.

WITH 
  MEMBER [Measures].[Selected Year's Data] AS 
    (
      Ancestor
      (
        [Date].[Calendar].CurrentMember
       ,[Date].[Calendar].[Calendar Year]
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
  MEMBER [Measures].[Previous Year's Data] AS 
    (
      Ancestor
      (
        [Date].[Calendar].CurrentMember
       ,[Date].[Calendar].[Calendar Year]
      ).Lag(1)
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Selected Year's Data]
   ,[Measures].[Previous Year's Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].[August 10, 2003]);

Output of all above mentioned MDX queries are same;

Output:

Calculate selected quarter data and data from previous year's same quarter:

Sometimes user select particular date and they want to view the selected date’s Quarter data as well as previous year’s same quarter data. You can fulfill this requirement by using some MDX functions. If you execute following MDX query then “[Measures].[Selected Day's Quarter Data]” will return Quarter data of selected date whereas “[Measures].[Previous Year's same Quarter Data] “ will return data from previous year’s same quarter.

WITH 
  MEMBER [Measures].[Selected Day's Quarter Data] AS
    (
      Ancestor
      (
        [Date].[Calendar].CurrentMember
       ,[Date].[Calendar].[Calendar Quarter]
      )
     ,[Measures].[Reseller Sales Amount]
    )
   ,FORMAT_STRING = "Currency"
  MEMBER [Measures].[Previous Year's same Quarter Data] AS
    (
      Ancestor
      (
        Cousin
        (
          [Date].[Calendar].CurrentMember
         ,Ancestor
          (
            [Date].[Calendar].CurrentMember
           ,[Date].[Calendar].[Calendar Year]
          ).Lag(1)
        )
       ,[Date].[Calendar].[Calendar Quarter]
      )
     ,[Measures].[Reseller Sales Amount]
    )
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Selected Day's Quarter Data]
   ,[Measures].[Previous Year's same Quarter Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].[September 5, 2003]);

Let me explain above MDX; Calculating value of “[Measures].[Selected Day's Quarter Data]” is pretty simple as we are simply using Ancestor function for retrieving Quarter level data. But for calculating value of a measure [Measures].[Previous Year's same Quarter Data] firstly we need to use MDX Ancestor function with “Calendar Year” level and LAG function for getting Previous year. After finding previous year, we used Cousin Function for retrieving same day of previous year. After retrieving previous year’s same day, again use Ancestor function for getting previous year’s same quarter.

Calculate month level data and previous year's same month data:

Sometimes user select particular date and they want to view the selected date’s Month level data as well as previous year’s same month data. You can fulfill this requirement by using some MDX functions. If you execute following MDX query then “[Measures].[Selected Day's Month level Data]” will return selected day’s Month level data  whereas “[Measures].[Previous Year's same Month Data] “ will return data from previous year’s same month.  

WITH 
  MEMBER [Measures].[Selected Day's Month level Data] AS 
    (
      Ancestor
      (
        [Date].[Calendar].CurrentMember
       ,[Date].[Calendar].[Month]
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
  MEMBER [Measures].[Previous Year's same Month Data] AS 
    (
      Ancestor
      (
        Cousin
        (
          [Date].[Calendar].CurrentMember
         ,Ancestor
          (
            [Date].[Calendar].CurrentMember
           ,[Date].[Calendar].[Calendar Year]
          ).Lag(1)
        )
       ,[Date].[Calendar].[Month]
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Selected Day's Month level Data]
   ,[Measures].[Previous Year's same Month Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].[September 5, 2003]);

Let me explain above MDX query; Calculating value of [Measures].[Selected Day's Month level Data]” is pretty simple as we are simply using Ancestor function for retrieving Month level data. But for calculating value of a measure [Measures].[Previous Year's same Month Data] firstly we need to use MDX Ancestor function with “Calendar Year” level and LAG function for getting Previous year. After finding previous year, we used Cousin Function for retrieving same day of previous year. And After retrieving previous year’s same day, again use Ancestor function for getting previous year’s same month.

Calculate data for selected date and same date's data from previous year: 

In some scenarios, user wants to compare transaction of selected date and same transaction on the previous year's same date. so for achieving such requirements you can write MDX query by using Ancestor function. Refer sample MDX query for the same.

WITH 
  MEMBER [Measures].[Transaction of Selected Date] AS 
    (
      [Date].[Calendar].CurrentMember
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
  MEMBER [Measures].[Transaction of Previous Year's same Date] AS 
    (
      Cousin
      (
        [Date].[Calendar].CurrentMember
       ,Ancestor
        (
          [Date].[Calendar].CurrentMember
         ,[Date].[Calendar].[Calendar Year]
        ).Lag(1)
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Transaction of Selected Date]
   ,[Measures].[Transaction of Previous Year's same Date]
  } ON COLUMNS
FROM [Adventure Works]
WHERE 
  [Date].[Calendar].[Date].[September 1, 2003];