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

Question

I am very new to this whole MDX query thing. I have the following query which I am trying to execute but keep getting the following error:

Executing the query ... Query (19, 16) The STRTOSET function expects a string or numeric expression for the 1 argument. A tuple set expression was used. Execution complete

My query looks as follows:

   SELECT 
NON EMPTY { [Measures].[Effective Duration] } ON COLUMNS, 
NON EMPTY { (
                [Dim Cause Code].[Cause].[Cause].ALLMEMBERS * 
                [Dim Classification].[Classification].[Classification].ALLMEMBERS * 
                [Dim Date 1].[Full Date Alternate Key].[Full Date Alternate Key].ALLMEMBERS * 
                [Dim Location 1].[Work Center Name].[Work Center Name].ALLMEMBERS * 
                [Fact Process Downtime].[Start Datetime].[Start Datetime].ALLMEMBERS * 
                [Fact Process Downtime].[End Datetime].[End Datetime].ALLMEMBERS * 
                [Fact Process Downtime].[Id].[Id].ALLMEMBERS ) 
            } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( 
    SELECT ( STRTOSET({[Fact Process Downtime].[Is Virtual].&[False]}, CONSTRAINED) ) ON COLUMNS 
    FROM ( 
            SELECT ( STRTOSET({[Dim Location 1].[Work Center].&[South32 Manganese.Mamatwan.DMS]}, CONSTRAINED) ) ON COLUMNS 
            FROM ( 
                    SELECT ( STRTOSET({[Dim Classification].[Classification].&[Scheduled Process]}, CONSTRAINED) ) ON COLUMNS 
                    FROM ( 
                            SELECT ( STRTOSET({[Dim Date 1].[Month Fiscal Year].&[Jul/17]}, CONSTRAINED) ) ON COLUMNS FROM [FactDownTime])))) WHERE ( 
    IIF( STRTOSET({[Dim Date 1].[Month Fiscal Year].&[Jul/17]}, CONSTRAINED).Count = 1, STRTOSET({[Dim Date 1].[Month Fiscal Year].&[Jul/17]}, CONSTRAINED), [Dim Date 1].[Month Fiscal Year].currentmember ), 
    IIF( STRTOSET(STRTOSET({[Dim Location 1].[Work Center].&[South32 Manganese.Mamatwan.DMS]}, CONSTRAINED).Count = 1, STRTOSET(STRTOSET({[Dim Location 1].[Work Center].&[South32 Manganese.Mamatwan.DMS]}, CONSTRAINED), [Dim Location 1].[Work Center].currentmember ), 
    IIF( STRTOSET({[Fact Process Downtime].[Is Virtual].&[False]}, CONSTRAINED).Count = 1, STRTOSET({[Fact Process Downtime].[Is Virtual].&[False]}, CONSTRAINED), [Fact Process Downtime].[Is Virtual].currentmember ) 
  )))

whytheq whytheq · Accepted Answer · 2016-09-09T10:28:03

You need to supply strings to this function strToSet is short for "String to Set"

https://msdn.microsoft.com/en-us/library/ms144782.aspx

So this is incorrect:

STRTOSET({[Fact Process Downtime].[Is Virtual].&[False]}, CONSTRAINED)

But this is correct

STRTOSET('{[Fact Process Downtime].[Is Virtual].&[False]}', CONSTRAINED))

The main use case for the strToSet function is when passing parameters into an mdx query from say SSRS so syntax like this is seldom used:

STRTOSET('{[Fact Process Downtime].[Is Virtual].&[False]}', CONSTRAINED))

If parameters are not involved then just get rid of the STRTOSET:

{[Fact Process Downtime].[Is Virtual].&[False]}

If you need to use parameters then check out this excellent recent answer from @ alejandrozuleta:

MDX SSRS Parameter category chooses all sub category

If parameters are not important then your script can maybe get simplified to something like this:

SELECT 
NON EMPTY [Measures].[Effective Duration] ON 0, 
NON EMPTY 
    [Dim Cause Code].[Cause].[Cause].ALLMEMBERS * 
    [Dim Classification].[Classification].[Classification].ALLMEMBERS * 
    [Dim Date 1].[Full Date Alternate Key].[Full Date Alternate Key].ALLMEMBERS * 
    [Dim Location 1].[Work Center Name].[Work Center Name].ALLMEMBERS * 
    [Fact Process Downtime].[Start Datetime].[Start Datetime].ALLMEMBERS * 
    [Fact Process Downtime].[End Datetime].[End Datetime].ALLMEMBERS * 
    [Fact Process Downtime].[Id].[Id].ALLMEMBERS 
  ON 1 
FROM 
( 
    SELECT 
        [Fact Process Downtime].[Is Virtual].&[False] ON 0, 
        [Dim Location 1].[Work Center].&[South32 Manganese.Mamatwan.DMS] ON 1, 
        [Dim Classification].[Classification].&[Scheduled Process] ON 2, 
        [Dim Date 1].[Month Fiscal Year].&[Jul/17] ON 3 
    FROM [FactDownTime]
);

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

1 Answers