sql-serverssasmdxcube

MDX Query with parameters with multiples values and null value


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

Solution

  • 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.