Search code examples
ssasmdx

MDX to SSAS calculation

I have written a MDX query which works fine in SQL Server Management Studio. My query is as follows:

SELECT [Measures].[Item Count] 
ON 0 
FROM [Inventory]
where [DateDiscontinued].[Date].[Discontinued Cal Year].&[0].&[0]

This query gives me all the items which are Discontinues.Now the problem is that when I copy paste the query in BIDS(SSAS Cube Calculation) as a calculated Member I get an error. It says it can't read the select statement. Do I need to write different MDX to support SSAS Calculated member or different function which supports SSAS Calculated Memeber?

Solution

  • The create member right syntax is:

    CREATE [ SESSION ] [HIDDDEN] [ CALCULATED ] MEMBER CURRENTCUBE | Cube_Name.Member_Name 
   AS MDX_Expression
      [,Property_Name = Property_Value, ...n]
......[,SCOPE_ISOLATION = CUBE]

    Then ,your create member should be like:

    CREATE MEMBER CURRENTCUBE.Measures.MyMeasure AS
([Measures].[Item Count] , 
 [DateDiscontinued].[Date].[Discontinued Cal Year].&[0].&[0])