STRTOSET - Constrained

Question

STRTOSET - Constrained

StrivingforGeekdom

Old Hand

Points: 305
More actions
January 25, 2010 at 3:42 pm

#215912

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

StrivingforGeekdom Old Hand Points: 305 More actions · Answer 1

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 SSCertifiable Points: 5704 More actions · Answer 2

Asim Yousaf

SSCertifiable

Points: 5704

May 27, 2011 at 6:14 am

#1330921

I have applied this and re run the cube it works

itksw Valued Member Points: 51 More actions · Answer 3

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 Ten Centuries Points: 1210 More actions · Answer 4

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