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