The function expects a tuple set expression for the 1 argument. A string or numeric expression was used

2
votes
SELECT 
  {
    Measures.[Deviation]
   ,[Measures].[Product ID]
  } ON COLUMNS
 ,Filter
  (
    CrossJoin
    (
      {[Dim Customer].[Customer Name].[Customer Name].MEMBERS}
     ,{[Dim Customer].[Gender].[Gender].MEMBERS}
     ,{[Dim Customer].[Customer ID].[Customer ID].MEMBERS}
    )
   ,
      [Measures].[Product ID] > 1 AND [Measures].[Deviation] > 5
    AND 
      [Measures].[Quantity] > 1
  ) ON ROWS
FROM 
(
  SELECT 
    [Dim Customer].[Customer ID].&[1] : [Dim Customer].[Customer ID].&[5] ON COLUMNS
  FROM 
  (
    SELECT 
      {
          Instr
          (
            [Dim Customer].[Customer Name].CurrentMember.Properties('Member_Caption')
           ,'H'
          )
        = 1
      } ON COLUMNS
    FROM [OLAP Cubes]
  )
);

When i run above query i get the error:

The function expects a tuple set expression for the 1 argument. A string or numeric expression was used.

How to solve this error?

1
sql-serverssasmdx

1 Answers

1
votes

This section is the problem ....

...
SELECT 
      {
          Instr
          (
            [Dim Customer].[Customer Name].CurrentMember.Properties('Member_Caption')
           ,'H'
          )
        = 1
      } ON COLUMNS
...

You can only use recognized data-types on COLUMNS or ROWS ... by that I mean a member, or a set of members, or set of tuples.

Are you actually trying to do a FILTER ?

...
SELECT 
     Filter(
          [Dim Customer].[Customer Name].[Customer Name].MEMBERS,
          Instr
          (
            [Dim Customer].[Customer Name].CurrentMember.Properties('Member_Caption')
           ,'H'
          )
        = 1
     ) ON COLUMNS
...