MDX - Query Analyzer Linked Server

• Jeff Welcome-130556

  SSCommitted

  Points: 1544

  More actions

  May 28, 2004 at 3:52 pm

  #108443

  Hello,

  I have recently built a cube.  My user base desires to query the cube with Query Analyzer.  I have set up a Linked Server and am able to get simple MDX queries to execute through Query Analyzer with OPENQUERY.  I run into problems when I attempt to run MDX query through that contains calculated members, since you are required to put an apostrophe in the member definition.  I have attempted many ways to get the syntax to work correctly and have failed in each. 

   

  I have tried using dynamic sql with exec(@MDX), attempted replacing the single quotes with others, the list goes on.  Does anybody have thoughts about how I can accomplish this?

   

  Thanks in advance,

  Jeff

  PS: 

  Below is the MDX query I am attempting to get to run

  -----------------------------------------------------------------------

   WITH MEMBER [Measures].[YTD Transfer] AS

   'SUM(PeriodsToDate([Date].[Fiscal Year]),[Measures].[Comp DC Transfer Qty])'

  SELECT {[Measures].[Comp DC Transfer Qty],

   [Measures].[YTD Transfer]} ON COLUMNS,

  [Date].[Week Ending].Members ON ROWS

  FROM [Transfer Comp]

  )

  ------------------------------------------------------------------------

   

• jordanac

  Hall of Fame

  Points: 3603

  More actions

  May 31, 2004 at 6:14 am

  #508365

   

  Have you tried SET QUOTED_IDENTIFIER ON .....

  Does this help

   

  SET QUOTED_IDENTIFIER ON

  SELECT "[Promotion Media].[Media Type].[MEMBER_CAPTION]"

               AS PromotionMediaType,

              "[Measures].[Unit Sales]" AS UnitSales,

              "[Measures].[Sales Count]" AS SalesCount,

              "[Measures].[Store Sales]" AS StoreSales

  FROM OPENQUERY(FoodMart_OLAP, 'SELECT {[Measures].MEMBERS} ON COLUMNS,  ORDER(EXCEPT([Promotion Media].[Media Type].MEMBERS, {[Promotion Media].[Media Type].[No Media]}),

       [Measures].[Unit Sales],DESC) ON ROWS FROM Sales')

  FOR XML RAW

   

   

  
  Andy.