Aging Report MDX query issue?

Question

Below is my MDX query to generate aging report. I want to use a named SET inside a calculated measure. I am getting this error:

Query (3, 1) The function expects a tuple set expression for the 1 argument. A string or numeric expression was used.

Can this be resolved?

WITH 
  SET [Cnt] AS 
    {'FILTER(
          [Cheque Detail Fact Keys].[Cheque Master ID].[Cheque Master ID]
         ,[Measures].[Paid Amt]<>0
       )'
    } 
  SET [x] AS 
    {
      ClosingPeriod
      (
        [Cal Date].[Month].[Month]
       ,[Cal Date].[Month].[All]
      )
    } 
  MEMBER [Measures].[0-30] AS 
    Sum
    (
      [x].Item(0).Lag(1) : [x].Item(0).Lag(0)
     ,Count(Cnt) //[Measures].[Master Count] 
    ) 
  MEMBER [Measures].[31-60] AS 
    Sum
    (
      [x].Item(0).Lag(2) : [x].Item(0).Lag(1)
     ,Count(Cnt) //[Measures].[Master Count] 
    ) 
  MEMBER [Measures].[>60] AS 
    Sum
    (
      NULL : [x].Item(0).Lag(4)
     ,Count(Cnt)  //[Measures].[Master Count] 
    ) 
SELECT 
  {
    [Measures].[0-30]
   ,[Measures].[31-60]
   ,[Measures].[>60]
  } ON 0
 ,{[Customer].[Name].[Name].ALLMEMBERS} ON 1
FROM [My Cube];

Answer 1

I think it is just the first section of your script. Why are you using a string? Try deleting the apostrophies and just add in .MEMBERS for clarity:

WITH 
  SET [Cnt] AS 
    {FILTER(
          [Cheque Detail Fact Keys].[Cheque Master ID].[Cheque Master ID].MEMBERS
         ,[Measures].[Paid Amt]<>0
       )
    } 
   ...
   ...

The error message seems a bit mysterious but I believe that the curly braces are a function themselves - they convert any members inside them into a set. So you have effectively written this {<some string>} but the curly brackets are expecting something that can be a set.

I get the same error message when running the following AdvWrks script:

WITH 
  SET [Cnt] AS 
    {'FILTER(
          [Date].[Date].[Date].MEMBERS
         ,[Measures].[Internet Sales Amount] > 10000
       )'
    } 
SELECT 
  [Measures].[Internet Sales Amount] ON 0
 ,[Cnt] ON 1
FROM [Adventure Works];

To use a count of Cnt in the following measures you can create a measure but I don't think this will be context aware as the set is evaluated before anything else:

WITH 
  SET [Cnt] AS 
    {FILTER(
          [Cheque Detail Fact Keys].[Cheque Master ID].[Cheque Master ID]
         ,[Measures].[Paid Amt]<>0
       )
    } 
  MEMBER [Measures].[Master Count]  AS 
    [Cnt].count
  SET [x] AS 
    {
      ClosingPeriod
      (
        [Cal Date].[Month].[Month]
       ,[Cal Date].[Month].[All]
      )
    } 
  MEMBER [Measures].[0-30] AS 
    Sum
    (
      [x].Item(0).Lag(1) : [x].Item(0).Lag(0)
     ,[Measures].[Master Count] 
    ) 
  MEMBER [Measures].[31-60] AS 
    Sum
    (
      [x].Item(0).Lag(2) : [x].Item(0).Lag(1)
     ,[Measures].[Master Count] 
    ) 
  MEMBER [Measures].[>60] AS 
    Sum
    (
      NULL : [x].Item(0).Lag(4)
     ,[Measures].[Master Count] 
    ) 
SELECT 
  {
    [Measures].[0-30]
   ,[Measures].[31-60]
   ,[Measures].[>60]
  } ON 0
 ,{[Customer].[Name].[Name].ALLMEMBERS} ON 1
FROM [My Cube];