dbTalk Databases Forums  

How to avoid CONSTRAINED flag violation

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap


Discuss How to avoid CONSTRAINED flag violation in the microsoft.public.sqlserver.olap forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Rose
 
Posts: n/a

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


Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.