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!
