STRTOSET - Constrained

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » STRTOSET - Constrained

STRTOSET - Constrained

Rate Topic

Display Mode

Topic Options

Author

Message

StrivingforGeekdom

Posted Monday, January 25, 2010 3:42 PM

Valued Member

Group: General Forum Members
Last Login: Saturday, January 26, 2013 3:15 PM
Points: 64, Visits: 107

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

StrivingforGeekdom

Posted Tuesday, February 09, 2010 10:38 AM

Valued Member

Group: General Forum Members
Last Login: Saturday, January 26, 2013 3:15 PM
Points: 64, Visits: 107

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

asimyousaf

Posted Friday, May 27, 2011 6:14 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, January 31, 2013 6:01 AM
Points: 3,150, Visits: 82

I have applied this and re run the cube it works

Post #1116171

itksw

Posted Sunday, March 18, 2012 3:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 23, 2012 9:51 AM
Points: 1, Visits: 2

StrivingforGeekdom (2/9/2010)

Thanks a lot for your solution Smile

Post #1268721

niha.736

Posted Thursday, January 10, 2013 3:56 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:15 AM
Points: 68, Visits: 205

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

Post #1405325

« Prev Topic | Next Topic »

Permissions