Skip to content
August 5, 2013 / Rodrigo Chiolas

SSRS – Calendar Date Picker Parameter with MDX

Problem

Almost all reports i build have a date parameter. When creating a dataset with a date parameter, using Reporting Services with Analysis Services, a list of values is generated automatically.

Although it works and gets the job done, it is not pratical, more than a month and it starts to be annoying scrolling all those values.

Fortunately, it is possible to implement a calendar date picker as a parameter of a cube report.

Solution

Steps to implement a calendar date picker in SSRS 2008.

  1. Create a report
  2. Create the date parameter or edit the parameter generated automatically when you create a main dataset

    SSRS_datepickerpost_create parameter

  3. Go to Report Parameter Properties and change the data type to Date.SSRS_datepickerpost_change datatype
  4. Set Available Values to None

    SSRS_datepickerpost_available values

  5. If you want a default value, set Default Values to Specify Values and use, for example,  the following expression  =CDate(format(Globals!ExecutionTime,”yyyy-MM-dd”))

    SSRS_datepickerpost_default_values

    SSRS_datepickerpost_default_value expression

  6. Now, for each dataset where the date parameter is used, go to Dataset Properties -> Parameters and edit the Parameter Value

    SSRS_datepickerpost_edit dataset parameter

  7. The expression for the Date parameter in this example is  

=”[TP DIM CALENDAR].[Date].&[” + Format(CDate(Parameters!FromParameter.Value),”yyyy-MM-dd”) + “T00:00:00]”

Note: the expression will vary depending on the format in which your cube stores the date field

Note2: If generated automatically, you can delete the dataset associated with the parameter since it is not needed anymore.

References:

Here is the post i used as guide: Original Link

Advertisements

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

%d bloggers like this: