If you’ve done a couple cube based reports, you’ve run into the issue of the dates being in string format. Because the dates are strings inside your cube, you can’t use the neat little date picker control reporting services gives you for datetime parameters. While you as a developer may understand this issue, your "tech savvy" end users may not. Fret no more. Here is a quick and easy way to use the built in date picker control for parameters in your MDX reports.

1) In the Report Data pane of BIDS, open the parameter properties for the parameter(s) you are using to specify a date or begin and end dates and change the data type to datetime. Change available values to None.

2) Go ahead and delete any hidden datasets that populate the drop down lists for your date parameters. You won’t need those with the date picker control.

3) In your data set that populates your report, open the properties, and in the parameters view, you want to use an expression on the parameter value field. So instead of the parameter value containing something like "=Parameters!DateDate.Value", you want to change it to something like:

="[Date].[Date].&[" + Format((Parameters!DateDate.Value), "MM-dd-yyyy") + "]"

Keep in mind that you’ll need to perform step 3 on any data sets that use your date parameters.

The key is to format the expression in such a way that it looks exactly like the members of your date dimension. Once you do that, click preview and you have the beautiful and end-user friendly date pickers in your cube based reports!

You can also go back to your parameters to specify default values using today and/or dateadd (ex. "=dateadd("M",-12,today)").

One of the downsides to using the date picker control with reports using your cube is a data source is that if the end user selects a date in the date picker that is not in your cube, your report is going to blow up, so just be aware of that.

If you have any questions, feel free to leave a comment!