Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

STRTOSET - Constrained Expand / Collapse
Author
Message
Posted Monday, January 25, 2010 3:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 4:02 PM
Points: 65, Visits: 120
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.
Post #853360
Posted Tuesday, February 09, 2010 10:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 4:02 PM
Points: 65, Visits: 120
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.
Post #862604
Posted Friday, May 27, 2011 6:14 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 6:05 AM
Points: 3,974, Visits: 85
I have applied this and re run the cube it works
Post #1116171
Posted Sunday, March 18, 2012 3:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 23, 2012 9:51 AM
Points: 1, 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
Post #1268721
Posted Thursday, January 10, 2013 3:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 04, 2014 8:11 AM
Points: 78, 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: 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
Post #1405325
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse