MDX to SSAS calculation

Question

I have written a MDX query in SQL Server Management Studio that works fine. Here is my query:

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

This query gives me all the things Discontinues. Now the problem is that I get an error when I copy the query as a measured member in BIDS(SSAS Cube Calculation). It says that the selected statement can not be read. Do I need to write different MDXs to support SSAS Calculated member or different SSAS Calculated Memeber supporting function?

Answer 1

The right syntax for create member is:

CREATE [ SESSION ] [HIDDDEN] [ CALCULATED ] MEMBER CURRENTCUBE | Cube_Name.Member_Name 

   AS MDX_Expression

      [,Property_Name = Property_Value, ...n]

......[,SCOPE_ISOLATION = CUBE]

Then , our member should be created in this way:

CREATE MEMBER CURRENTCUBE.Measures.MyMeasure AS

([Measures].[Item Count] , 

 [DateDiscontinued].[Date].[Discontinued Cal Year].&[0].&[0])

Check out Msbi certification training that enables you to master MSBI tools like SSIS, SSRS, and SSAS using SQL Server.