Search code examples
sql-serverssisssasmdx

What´s wrong with this MDX query Error: Query (3, 8)?


I tried to execute this query and i can´t understand what´s wrong with it.

 WITH MEMBER Time.T1 AS 'SUM([Time].&[TY].&[0]:[Time].&[TY].&[11])' 
MEMBER Time.T2 AS 'SUM([Time].&[TY].&[0]:[Time].&[TY].&[11])' 
MEMBER Measures.Col1 AS '(Scenario.&[PO], [T1], [GNAm])' 
MEMBER Measures.Col2 AS '(Scenario.&[ACFC], [T2], [GNAm])' 
MEMBER Measures.Diff AS '[Col2] - [Col1]' 
MEMBER Measures.Perc AS 'IIF([Col1] = 0, [Col1], [Diff] / [Col1])', FORMAT_STRING = '#0.00%' 
SELECT {[Col1], [Col2], [Diff], [Perc]} on Columns, Descendants([Account].[Account].[Expenses]) on Rows 
FROM DS_GNA 
WHERE (&[44026-90],&[010000])

get error message:

Error: Query (3, 8) Parser: The syntax for '&' is incorrect.

Solution

  • Basically the following just repeats the comments to your question. I've totally excluded your WHERE clause as best to see if it runs without an exception first. As mentioned by TTeeple I've just included ampersands before key values:

    WITH 
      MEMBER Time.T1 AS 
        Sum([Time].[TY].&[0] : [Time].[TY].&[11]) 
      MEMBER Time.T2 AS 
        Sum([Time].[TY].&[0] : [Time].[TY].&[11]) 
      MEMBER Measures.Col1 AS 
        (
          Scenario.[PO]
         ,[T1]
         ,[GNAm]
        ) 
      MEMBER Measures.Col2 AS 
        (
          Scenario.[ACFC]
         ,[T2]
         ,[GNAm]
        ) 
      MEMBER Measures.Diff AS 
        [Col2] - [Col1] 
      MEMBER Measures.Perc AS 
        IIF
        (
          [Col1] = 0
         ,[Col1]
         ,
          [Diff] / [Col1]
        ) 
       ,FORMAT_STRING = '#0.00%' 
    SELECT 
      {
        [Col1]
       ,[Col2]
       ,[Diff]
       ,[Perc]
      } ON COLUMNS
     ,Descendants([Account].[Account].[Expenses]) ON ROWS
    FROM [DS_GNA];