Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSRS Date Parameters for Cube Based Datasets

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.

Comments

Posted by prakhecha on 12 November 2014

Error: Overload resolution failed because no accessible 'DatePart' can be called without a narrowing conversion

Hi,

I want to add a date filter parameter .But I get an error when I use this expression..

Can you please help?

I am trying to get the last sundays date from the current date:

="[Date].[Date].&[" & Format(CDate(DateAdd("d", - DatePart("w", Now(), 1)-6, Now()), "yyyyMMdd")) + "]"

Leave a Comment

Please register or log in to leave a comment.