SSRS: How to use a date/time parameter as an input for an MDX query (instead of a list of text values)

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):

SSRS datetime input parameter instead of text for mdx





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:

SSRS datetime input parameter instead of text for mdx - properties






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:

SSRS datetime input parameter instead of text for mdx - dataset properties









Done!
That’s it! Bye bye ugly drop-down list 🙂

SSRS datetime input parameter instead of text for mdx - final result











Advertisements

2 thoughts on “SSRS: How to use a date/time parameter as an input for an MDX query (instead of a list of text values)

  1. Clint, i think this method of adding a ‘0’ is easier: Right(“0″+CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value))),2)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s