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