How should date type parameters be prompted?  There are two common approaches

depending on how you want the parameter to appear.  Keep in mind that in a cube,

a date attribute is typically not stored or presented as a date type. So you can either

present the parameter as a hierarchy or as a date.  In the prior example, if

you were to parameterize the query as:

select
[Measures].[Internet Sales Amount] on columns
, PERIODSTODATE([Date].[Calendar].[Calendar Year], @ToDate) on rows
from
[Adventure Works];

...the parameter must return the string value in this format:

[Date].[Calendar].[Date].[February 5, 2004]

Unfortunately the MDX query designer is not very good at letting you customize a query

so you have to work with and around it's tendancy to take over the process. 

It often will not allow you to simply reference a parameter using syntax that it didn't

build for you.  I recommend using the graphical builder to create a simple query

that will return all the members in the order you will want to see them in your final,

customized query.  If you want the date parameter to appear as a hierarchy, use

the query builder to add a filter using a user hierarchy and check the Parameter checkbox

in the filter pane.  This will create a separate MDX dataset to drive the parameter

list.

Next, either switch the query editor to text edit mode or replace the query using

an expression.  I find it more flexible to build string expressions to work around

the MDX query builder's quirkiness.  The expression version of the query would

be:

="select " &
"[Measures].[Internet Sales Amount] on columns " &
", PERIODSTODATE(" & Parameters!ToDate.Value & ") on rows " & 
"from " &
"[Adventure Works]; "

The lines are concatonated together for readability.  make sure each line is

separated with a space or carriage return.  This dataset would be good to go

beacuse, the parameter was alraedy built by the graphical designer.

If you want to present the user with a real date type parameter, which will utilize

the date picker control, you will need to pass the resulting parameter value into

a function or expression to build the date member string value.  To do this in

an embedded function, open the Report Properties dialog and type this code into the

Code page:

Function DateToMember(TheDate As Date) As String
    Dim sDateString As String = Format(TheDate, "MMMM d, yyyy")
    Return "[Date].[Calendar].[Date].[" & sDateString & "]"
End Function

...and then use the function in the dataset expression:

="select " &
"[Measures].[Internet Sales Amount] on columns " &
", PERIODSTODATE(" & Code.DateToMember(Parameters!ToDate.Value) & ") on rows " & 
"from " &
"[Adventure Works]; "

Happy reporting!

Weblog by Paul Turley and SQL Server BI Blog.