Parser: The syntax for 'SCOPE' is incorrect

Question

I'm learning MDX and having trouble with SCOPE.

As a test I'm trying to create a new measure only for Fiscal Year 2011 but have a syntax error.

It isn't a Create Calculated Measure in SSAS Script but a query in SSMS.

Any help or pointers would be great.

 WITH MEMBER [Measures].[Test Scope]
    AS 1
    (
     SCOPE ([Measures].[Test Scope]);
      SCOPE ([Date].[Fiscal Year].&[2011]);
       THIS = [Measures].[Reseller Order Quantity] + 10000;
      END SCOPE;
     END SCOPE;
    )

SELECT 
{[Measures].[Reseller Order Quantity],[Measures].[Test Scope]} ON COLUMNS
,[Date].[Fiscal Year].[Fiscal Year].MEMBERS ON ROWS
FROM [Adventure Works]

Query (4, 2) Parser: The syntax for 'SCOPE' is incorrect.

Answer 1

You can’t use a scope statement in a query. It has to be defined in the MDX script of the cube or run in the context of a session. The most common way to handle this is the following.

WITH MEMBER [Measures].[Test Scope] AS 
 IIF(
  [Date].[Fiscal Year].CurrentMember is [Date].[Fiscal Year].&[2011],
  [Measures].[Reseller Order Quantity] + 10000,
  NULL
 )
SELECT 
{[Measures].[Reseller Order Quantity],[Measures].[Test Scope]} ON COLUMNS
,[Date].[Fiscal Year].[Fiscal Year].MEMBERS ON ROWS
FROM [Adventure Works]

There is also very old MDX syntax CELL CALCULATION in an MDX query which I would recommend not using without through testing since it is infrequently used. Off the top of my head it would look like this. It could “scope” over any measure or calculated measure or tuple. Here we have scoped over a null calculated measure.

WITH MEMBER [Measures].[Test Scope] AS 
 NULL
CELL CALCULATION [Test Scope Calc] 
 FOR '([Measures].[Test Scope],[Date].[Fiscal Year].&[2011])'
 AS '[Measures].[Reseller Order Quantity] + 10000'
SELECT 
{[Measures].[Reseller Order Quantity],[Measures].[Test Scope]} ON COLUMNS
,[Date].[Fiscal Year].[Fiscal Year].MEMBERS ON ROWS
FROM [Adventure Works]