To have a ‘date picker’ report parameter on your SSRS reports the process works the same on all versions of SSRS from 2005 through 2012. When you add the Date dimension as a filter in Query Designer it creates a report parameter for you. But the report parameter has a data type of text. Since we normally want to have a calendar object where users can choose the date instead of typing the date in, we have to make the following four changes:
1. In the report parameter we have to change the data type to Date/Time. That gets us the date picker.
2. Also in the report parameter under Available Values we have to set this to None. The default setting will be set “Get values from a query” and it will want to pull all the values from our date dimension. That has a text format and would cause a ‘valid values’ format error.
3. Still in the report parameter under Default Values, select “Specify Values” and in the expression use date functions to get whatever date you need (e.g. =Today).
4. Finally, in our Dataset properties under Parameters we have to set the Parameter value to an expression like this
="[Calendar].[Date].&[" + Format(CDate(Parameters!CalendarDate.Value),"yyyy-MM-dd") + "T00:00:00]"
where [Calendar].[Date] is my dimension and CalendarDate is the ID of my report parameter. The reason we’re doing this is to convert a date data type to the required text format to compare with our date dimension.