Using date parameters in Reporting Services reports built against data cubes

I've came across a newsgroup question today, that I thought that I would blog about. When you create a report in Reporting Services against a SQL AS data cube and create a parameter against a date dimension the VS Report Designer creates a string type parameter. Most of the time when you are dealing with dates it's much easier for the end user to use the calendar to select a date. This is only available if you set the parameter to a DateTime type, however this then breaks the query.

The query builder tool, when dealing with parameters creates a query which looks like this.

SELECT NON EMPTY { KPIGoal("Billable Hrs KPI"), KPIValue("Billable Hrs KPI"), KPIStatus("Billable Hrs KPI"), [Measures].[Billable Hrs Booked %], [Measures].[Billable Hrs %], KPIGoal("Hrs Booked KPI"), KPIValue("Hrs Booked KPI"), [Measures].[Hrs Booked %], KPIStatus("Hrs Booked KPI") } ON COLUMNS, NON EMPTY { ([Users].[Users].[Users].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( -STRTOSET(@UsersUserName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@UserGroupsUserGroups, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromActivityDateDate, CONSTRAINED) : STRTOMEMBER(@ToActivityDateDate, CONSTRAINED) ) ON COLUMNS FROM [Exactim]))) WHERE ( IIF( STRTOSET(@UserGroupsUserGroups, CONSTRAINED).Count = 1, STRTOSET(@UserGroupsUserGroups, CONSTRAINED), [User Groups].[User Groups].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The Date parameters are expected to be a string in the format of the full name of the date member (e.g. [Activity Date].[Date].[2007-09-27T14:52:00]) this gets passed into the STRTOMEMBER which then get the real member object.

What we need to do is to format the DateTime report parameter to a string.

Select your main data set in the report designer and select the eclipse button next to the drop down to open the data set properties.

On the Parameter tab of the dialog, select the value of the parameter and you should get a drop down appear, select <Expression...>

You can now create an expression which transforms the datetime parameter in to a string

e.g.

="[Activity Date].[Date].&[" & Year(Parameters!FromActivityDateDate.Value) & "-" & Format(Month(Parameters!FromActivityDateDate.Value), "00") & "-" & Format(Day(Parameters!FromActivityDateDate.Value),"00") & "T00:00:00]"

The thing to take away is to remember that there are two sets of parameters the Report Parameters that the user interacts with and the Query Parameters that are passed database.