SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


STRTOSET - Constrained


STRTOSET - Constrained

Author
Message
StrivingforGeekdom
StrivingforGeekdom
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 137
I am running into the following error -

restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated

I receive this error when using select all in a report using a datacube. The MDX is auto-generated as follows:

WITH MEMBER [Measures].[Total] AS '[Measures].[Overhead Cost Amount] + [Measures].[Labor Cost Amount]+[Measures].[Hardware Cost Amount]+[Measures].[Material Cost Amount]' SELECT NON EMPTY { [Measures].[Material Cost Amount], [Measures].[Labor Cost Amount], [Measures].[Overhead Cost Amount], [Measures].[Total], [Measures].[Hardware Cost Amount] } ON COLUMNS, NON EMPTY { ([WIP Stage].[WIP Stage Description].[WIP Stage Description].ALLMEMBERS * [Program].[Program Description].[Program Description].ALLMEMBERS * [Responsible Department].[Department Description].[Department Description].ALLMEMBERS * [WIP Stage].[WIP Stage Code].[WIP Stage Code].ALLMEMBERS * [Analysis Date].[Reporting Range].[Reporting Range].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@ResponsibleDepartmentDepartmentDescription, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ProgramProgramDescription, CONSTRAINED) ) ON COLUMNS FROM [Inventory Analysis Reporting])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I have tried to create as an expression but can not make that work either. I would appreciate any help.
StrivingforGeekdom
StrivingforGeekdom
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 137
I have discovered that I needed to make adjustments to the Dimension definitions to not allow unknown as an option. Once I made the change and rebuilt the cubes, the report no longer threw the Constraint error.
Asim Yousaf
Asim Yousaf
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5089 Visits: 153
I have applied this and re run the cube it works
itksw
itksw
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 2
StrivingforGeekdom (2/9/2010)
I have discovered that I needed to make adjustments to the Dimension definitions to not allow unknown as an option. Once I made the change and rebuilt the cubes, the report no longer threw the Constraint error.


Thanks a lot for your solution :-)
niha.736
niha.736
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 233
Hi All,

currently in my solution one cube structure has changed.
according to the new cube we are modifying the existing reports.
here my problem is
I prepared a dataset with new cube members


SELECT NON EMPTY { [Measures].[Scrapping BU],
[Measures].[Scrapping Local Currency],
[Measures].[Sales margin wo VAT (scrapping included)],
[Measures].[Stock sales wo VAT],
[Measures].[Sales margin wo VAT] } ON COLUMNS, NON EMPTY { (
[Product DIM].[Product Hierarchy FI].[MC0 FI].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (
STRTOMEMBER(@FromBillingDateDIMDateHierarchyFI, CONSTRAINED) :
STRTOMEMBER(@ToBillingDateDIMDateHierarchyFI, CONSTRAINED) ) ON COLUMNS FROM ( SELECT (
STRTOSET(@MaterialSalesDIMAssortmentGrade, CONSTRAINED) ) ON COLUMNS FROM ( SELECT (
STRTOSET(@ProductDIMMCFI2, CONSTRAINED) ) ON COLUMNS FROM ( SELECT (
STRTOSET(@ProductDIMMCFI, CONSTRAINED) ) ON COLUMNS FROM ( SELECT (
STRTOSET(@StoreDIMStore, CONSTRAINED) ) ON COLUMNS FROM [Store Sales]))))) WHERE ( IIF(
STRTOSET(@StoreDIMStore, CONSTRAINED).Count = 1,
STRTOSET(@StoreDIMStore, CONSTRAINED), [Store DIM].[Store].currentmember ), IIF(
STRTOSET(@ProductDIMMCFI, CONSTRAINED).Count = 1,
STRTOSET(@ProductDIMMCFI, CONSTRAINED), [Product DIM].[MC4 FI].currentmember ), IIF(
STRTOSET(@ProductDIMMCFI2, CONSTRAINED).Count = 1,
STRTOSET(@ProductDIMMCFI2, CONSTRAINED), [Product DIM].[MC2 FI].currentmember ), IIF(
STRTOSET(@MaterialSalesDIMAssortmentGrade, CONSTRAINED).Count = 1,
STRTOSET(@MaterialSalesDIMAssortmentGrade, CONSTRAINED),
[Material Sales DIM].[Assortment Grade].currentmember ) )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,
FONT_NAME, FONT_SIZE, FONT_FLAGS




this query running well in query designer.
but after previewing am getting the following error.

ERROR:Crying Query (12,69) The Restrictions imposed by the constrained flag in the strtoset function were voilated""""

I already gone through some posts:
I have discovered that I needed to make adjustments to the Dimension definitions to not allow unknown as an option. Once I made the change and rebuilt the cubes, the report no longer threw the Constraint error.



but in my case : am just creating a copy of old report and applying changes as per the TEST cube .
in my previous cube also , unknown member is present.
but am not getting any error there.
can anybody please siggest something on this.
my all new reports are showing same error.




Thanks in advance
Niha
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search