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()
2条答案
按热度按时间a7qyws3x1#
When using
strToSet
orstrToMember
you need to supply a string that represents validmdx
so for example these are ok:This isn't valid as
NULL
isn't a string, or something that represents mdx: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
.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.
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
so given your parameter exists if will compare 'statuscode' = '' and if doesnt it will compare '' = ''
hope it helps