SQL Server MDX Query with parameters with multiples values and null value

of1yzvn4  于 2023-05-28  发布在  其他
关注(0)|答案(2)|浏览(111)

I work with a SSAS Cube and Datazen (dashboard creator). I've a data view (for the valueCode 'AZE') with 3 parameters:

AgeClassCode: 'AGE01', 'AGE02', 'AGE03', ...

StatutCode: 'A', 'B', 'C', 'D', ...

NiveauCode: 'X', 'Y', 'W', ...

With this query, when I use multiple values or just one value for each, it works. But I would like that the query returns all values for a parameter when the parameter's value is null. I've tested ISEMPTY(@param), ISEMPTY(STRTOSET(@param)), ... but that returns this error:
An mdx expression was expected. An empty expression was specified.

This query works for one or more values:

SELECT 
   NON EMPTY 
   { 
        [Measures].[Value], [Measures].[PreviousValueYear], [Measures].[PreviousValueReportMonth] 
   } ON COLUMNS, 
   NON EMPTY 
   { 
        NONEMPTY
        (
            [EntiteFederal].[ServiceCode].[ServiceCode].ALLMEMBERS *
            [EntiteFederal].[EntiteCode].[EntiteCode].ALLMEMBERS *
            [ReportMonth].[ReportMonth].[ReportMonth].ALLMEMBERS *
            [T].[Year].[Year].ALLMEMBERS 
        ) 
    } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
    FROM
    ( 
            SELECT ( { [ValueType].[ValueCode].&[AZE] } ) ON COLUMNS 
            FROM (
                    SELECT ( STRTOSET('{{ @AgeClassCode }}') ) ON COLUMNS 
                    FROM ( 
                            SELECT ( STRTOSET('{{ @NiveauCode }}') ) ON COLUMNS 
                            FROM 
                            ( 
                                SELECT ( (STRTOSET('{{ @StatutCode }}') ) ON COLUMNS 
                                FROM [MyCube]
                            )
                        )
                )
    )                       
    WHERE 
    ( 

        IIF( STRTOSET('{{ @StatutCode }}').Count = 1, STRTOSET('{{ @StatutCode }}'), [Statut].[StatutCode].currentmember ),
        IIF( STRTOSET('{{ @NiveauCode }}').Count = 1, STRTOSET('{{ @NiveauCode }}'), [Niveau].[NiveauCode].currentmember ),
        IIF( STRTOSET('{{ @AgeClassCode }}').Count = 1, STRTOSET('{{ @AgeClassCode }}'), [AgeClass].[AgeClassCode].currentmember ),
        [ValueType].[ValueCode].&[AZE]
     )

What do I have to change?

EDIT:

To test the strtoset()

, the good solution is the

isError()
a7qyws3x

a7qyws3x1#

When using strToSet or strToMember you need to supply a string that represents valid mdx so for example these are ok:

strToSet("[AgeClassCode].[AgeClassCode].members")

strToMember("[AgeClassCode].[AgeClassCode].[AGE01]")

This isn't valid as NULL isn't a string, or something that represents mdx:

strToSet(NULL)

So if in your client you'd like NULL to represent all members then somehow you need to transform the NULL to a string "[AgeClassCode].[AgeClassCode].members" before it hits strToSet .

vltsax25

vltsax252#

I don't know if we're going through the same problem, but I think it is, and I've found a solution, for mine so let me share it with you, im using pentaho mondrian btw.

IIF( STRTOSET('{{ @StatutCode }}').Count = 1, 
   STRTOSET('{{ @StatutCode }}'), 
   [Statut].[StatutCode].currentmember 
)

this hasn't worked for me, given that the parameter doesn't exist the query won't work.

What I've found was that, given the parameter is empty the query would be like

IIF('@VALUE(:StatutCode)' = '',
    [Statut].[StatutCode].currentmember,
    [Statut].[@VALUE(:StatutCode)]
)

so given your parameter exists if will compare 'statuscode' = '' and if doesnt it will compare '' = ''

hope it helps

相关问题