Ever wanted to use pretty Calendar Controls of Report Builder 2.0 in reports based on Analysis Services?

How to use SSRS Calendar Controls for Time dimensions in the reports based on SSAS (Analysis Services) in Report Builder 2.0:



1. Start creating the SSRS report in the Report Builder 2.0 with a Wizard.

2. Select a Time dimension and drop it in the filter area, select hierarchy, use Range (Inclusive) and check both parameters checkboxes, add measures and other dimensions to the report. Close the Query Designer.




3. Go to the parameters of the report (they are automatically created) and modify "From" and "To" Time parameters - change their Data type to Date/Time and set Available Values to None





4. Open the DataSet Properties and press fx button to the right of Query





5. Remove "CONSTRAINED" from "STRTOMEMBER" function that is using Date parameters in the MDX query



6. Go to parameters tab of the DataSet Properties window and enter the expression as a parameter value. Expression has to generate a Uniquename of the Time dimension

In my example unique name is [Date].[Date].[Day].&[20111216]

To generate this string you will need following expression:
="[Date].[Date].[Day].&[" + Format(Convert.ToDateTime(Parameters!FromDate.Value), "yyyyMMdd") + "]"



Repeat this for the second parameter.