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]; "
Weblog by Paul Turley and SQL Server BI Blog.