constrained flag in the STRTOSET function violated

I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?

The constrained flag is not your problem, it is simply a flag for the STRTOSET function, and you can get rid of it.

The output of the datepicker is a string, the format of that string depends on the location you have your browser set to (eg IE is set to en-US by default). The approach i have used for this in the past is to CDate the output from the datepicker, then use Format to make it into a string that matches your cube's date heirarchy so that you can use STRTOSET on it. So, in your MDX where you have:

STRTOSET(@.yourDateParameter, CONSTRAINED)

change it to:

STRTOSET( Format( CDate(@.yourDateParameter), "<suitable format code>"), CONSTRAINED)

the <suitable format code> bit could be something like "yyyy/MM/dd", what i ended up needing to resemble my date heirarchy was "yyyy-MM-ddT00:00:00".

Hope this helps.

|||

Thank yo so much for your help! I tried your suggested and got this error Query (1, 112) The '[Format]' function does not exist. (Microsoft SQL Server 2005 Analysis Services)

I must have done something wrong... please advise.

|||

I use this is SSRS2005 with no problems, i don't know if it is permissable in 2000. Which version are you using?

||| I am also using SSRS2005....|||

Here are a couple of samples of using the Format() function in real code. The first one is used for filtering dates for a parameter dropdown:

WITH

MEMBER [Measures].[ParameterValue] AS '[Sale Date].[Date Description].CURRENTMEMBER.UNIQUENAME'

SELECT {[Measures].[ParameterValue] } on columns,

{ Filter( [Sale Date].[Date Description].[Date Description], Format(CDate( [Sale Date].[Date Description].CURRENTMEMBER.MEMBER_CAPTION), "dd Mon yyyy") = Format(Now(), "dd Mon yyyy")) } on rows

FROM [MyCube]

The second one is a subset of a much larger query. The first STRTOSET shows me manipulating an actual return string from a calendar control (you can insert @.YourParameterName instead of the actual datetime string) to fit the look of my heirarchy member.

SELECT NON EMPTY { [Measures].[Capacity], [Measures].[Booked] } ON COLUMNS

FROM ( SELECT (

STRTOMEMBER("[Sale Date].[Date].&[" + Format(CDate("2006/05/02 12:00:00 AM"), "yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) :

STRTOMEMBER("[Sale Date].[Date].&[2006-05-06T00:00:00]", CONSTRAINED)

)

ON COLUMNS FROM [MyCube]

)

Hope this helps!