STRTOSET函数期望1参数的字符串或数字表达式.使用了元组集表达式

问题描述

对于整个MDX查询，我还是一个新手.我尝试执行以下查询，但始终收到以下错误:

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:

我的查询如下:

   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 ) 
  )))

推荐答案

您需要为此函数提供字符串strToSet是要设置的字符串"的缩写

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

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

所以这是不正确的:

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

但这是正确的

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

strToSet函数的主要用例是从诸如SSRS的mdx查询中传递参数，因此很少使用这样的语法:

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))

如果不涉及参数，则摆脱STRTOSET:

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

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

---

如果您需要使用参数，请查看@ alejandrozuleta的最新优秀答案:

---

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

"MDX SSRS参数"类别选择所有子类别

如果参数不重要，那么您的脚本可能会简化为以下形式:

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]
);

这篇关于STRTOSET函数期望1参数的字符串或数字表达式.使用了元组集表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！