How to avoid CONSTRAINED flag violation -
01-12-2006
, 03:46 AM
The following MDX shows a non-existing member
'[Product].[Category].&[5]' is used to query.
(valid members are &[1] to &[4]).
Is there a function/flag equivalent to TSQL's ISNULL() or EXISTS that
can be used to deal with the error more elegantly?
I am trying to achieve this objective:
1. receive the parameter and query against AS
2. if it is a valid dimension member, return the value itself and
proceed as normal
3. otherwise, return the default member, e.g.
[Product].[Category].&[0] which is a member setup to handle
unknown/invalid members.
SELECT NON EMPTY { [Measures].[Internet Sales Amount],
[Measures].[Internet Order Quantity] } ON COLUMNS,
NON EMPTY { [Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,
[Sales Reason].[Sales Reason].[Sales Reason].KEY ON ROWS
FROM ( SELECT ( STRTOSET('[Product].[Category].&[5]', CONSTRAINED) ) ON
COLUMNS
FROM [Adventure Works])
WHERE ( IIF( STRTOSET('[Product].[Category].&[5]', CONSTRAINED).Count =
1,
STRTOSET('[Product].[Category].&[5]', CONSTRAINED),
[Product].[Category].currentmember ) )
Query (6, 15) The restrictions imposed by the CONSTRAINED flag in the
STRTOSET function were violated. |