Share via

Using a Date Picker for an Analysis Data Query Parameter

  • Article

Introduction

This article describes actions to be taken in Report Builder 3.0.

Many of us have grown used to having a date picker (calendar) control to select a date in a web form. SSRS reports offer this functionality as well for selecting date values for DateTime parameters. But this convenient control presents problems when the parameter value will be used to filter analysis data.

Overview

When you create a report using analysis data and you set date filters in the query designer that will be driven from user input, the report will auto-generate the parameter and supporting query for you. Typically, this is very helpful. However, with dates in particular, the parameter interface is not the familiar date picker but a list of date strings. Since all cube data is in string format, SSRS sets the parameter to data type Text. In fact, while the displayed values in the parameter's list may look like 2012-10-10, the actual value will be something like [Date].[Date].&[2012-10-10T00:00:00].

Discussion

Let's consider the following scenario:
While creating a new report based on the Analysis data (data cube), your dataset requires the user to submit a start and end date to filter the data. As described above, the parameter created is a text parameter, with its allowed values driven from an auto-generated analysis dataset. This does not present an easy user interface and most users will prefer a date picker control. Here's how you fix it:

  • Open the parameter(s) in question
  • Change the type from Text to Date/Time (this enables the date picker control)
  • On the Available Values tab, make note of the query name then change the setting to None. Even with a DateTime data type for the parameter, setting the allowed values will cause the parameter to display those values in a drop down list rather than using the date picker.
  • On the Default Values tab, change any default value to the equivalent date (i.e. CDate("10/10/2012")) or you can set it to Today() to use the report run date as the default.
  • Save and close the parameter properties.
  • Look in the datasets node for the query you noted above.
    • If you don't see it, right-click the Datasets node and select Show hidden datasets.
  • Delete the dataset. It is not needed any longer since it was auto generated to provide the available values for your parameter(s) and the parameter(s) no longer have allowed values.
  • Open any datasets that use the parameter.
  • Go to the Parameters tab.
  • Open the expression editor (fx) next to the parameter value(s) by clicking the "fx" button.
  • Change the value in the expression to something like: =" [Date].[Date].&["+Format((Parameters! Date.Value), "yyyy-MM-dd")+"T00:00:00]". This converts the standard date value into a data cube date string.
    It is important to note that the leading part of the string ([Date].[Date]) may be different depending on the dimension that the filter applies to. For Instance, if you are filtering the work item changed date the leading string would be "[Work Item].[System_ChangedDate]".

Summary

This alters the functionality from the original auto-generated parameter. As created by SSRS, the allowed values for the date parameter(s) were restricted to known values from the analysis data. For instance, if there were no records for a given date, that date would not be in the list of allowed values for the auto-generated parameters. Converting the parameter to a DateTime parameter removes these restrictions which could allow a user to select an invalid date. You should ensure that your dataset handles this situation gracefully.