I guess we’ve all seen it before, VS/SSDT/BIDS automatically generates datasets for input parameters (which is fine to start with).
But for Date/Time parameters it always (by default) selects the ‘Text’-datatype (drop-down list), instead of the ‘Date/Time’-datatype (nice calendar).
What we want to achieve
So this is what we want to achieve (in red):
Start a clean slate
First step is to drop the generated date/time parameter(s) we’re not going to use and create a new one:
Make sure to select the ‘Date/Time’ as the datatype, which enables the nice calender-type input. Leave the ‘Available Values’-section unchanged/empty.
Hussle the Date-value in the correct format (YYYY-MM-DD)
A difference between both datatypes is that the ‘Date/Time’-datetype will return values formatted based on your local/regional settings (e.g. YYYY-M-D or in my case D-M-YYYY).
Where I need it to be YYYY-MM-DD (including 0’s), so something like:
2015-12-01
To achieve this, we need to cast/convert a little bit and include a ‘0’ when we need it:
=IIF(LEN(CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))) = 2,
CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))
)
Turn it into a MDX value
The final step is to get it into the desired value for MDX:
[Dim Creation Date].[Date].&[2015-12-05T00:00:00]
Here’s the full code expression which you can use as a parameter value (expression):
="[Dim Creation Date].[Date].&["
+CSTR(DATEPART(DateInterval.Year,CDate(Parameters!DateFrom.Value)))+"-"+
IIF(LEN(CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))) = 2,
CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))
)+"-"+
IIF(LEN(CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value)))) = 2,
CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value)))
)
+"T00:00:00]"
Use the expression as an input value for your dataset
The final step is to use the expression:
Done!
That’s it! Bye bye ugly drop-down list 🙂