Hi, I don't know what happened to my blog. The BDN site has problems with my account and blog. 🙁 Oh well here you go, this should help you resolve your problems (my blog post included images).
The following summary details the steps to follow in order to implement a calendar date picker as a parameter of a cube report utilizing Visual Studio 2005 and SQL Server 2005 Service Pack 2 (with Analysis Services and Reporting Services).
It is very important to keep in mind that there are several ways in which cubes store datetime fields. You need to mouse over the member to see the exact syntax of the member.
One of the most common types is the following (string data type) format:
Example: [DATE].[Date].&[2009-12-01T00:00:00]
In order to be able to select the date parameter by calendar date picker instead of by a list of dates:
1) Create Report
Create project, datasource, report (.rdl file), dataset that will have the query that calls the cube and necessary Layout. In the Data tab drag levels or measures to add to the query.
2) Create the Parameter
In the Data tab create Parameters called Date by selecting the checkbox, then press the ! Execute Query button to verify the total.
3) Configure Report Parameters
Go to Layout tab, go to Report Parameters and verify that the Parameter Date is created, change Properties: Data type dropdownlist from String to DateTime, verify that checkboxes for Hidden, Internal, Multi-value, Allow null value and Allow blank value are NOT selected, verify that Available values is Non-queried, change Default values selected radiobutton to Non-queried and with a value of =Today, press OK button.
4) Assign Expression to the Parameter
In the Data tab select the … Edit Selected Dataset button, go to the Parameters tab, verify that the Parameter Name Date appears and in the value change =Parameters!Date.Value to <Expression…>
The expression for the Date parameter in this example is (Note: the expression will vary depending on the format in which your cube stores the date field):
="[DATE].[Date].&[" & Format(CDate(Parameters!Date.Value),"yyyy-MM-dd") + "T00:00:00]"
Press the OK button, then in the Parameters tab press OK button again. Go to the Preview tab and verify that the calendar date picker works as desired and that the totals are correct.
5) Eliminate Unnecessary Datasets
After finishing the Datasets used in the report you can delete the unnecessary additional datasets that are created to present the lists of dates, these are not used.
Notes and Observations
- In this example it is not necessary to encapsulate the query for the Dataset like an expression (=”expression”).
- There is also no need to edit the MDX query manually in Design Mode which is convenient since manually editing a query usually results in not being able to return to the graphical Design Mode without losing the changes that where made manually.
- During my tests I learned that if you are in a development environment that has SQL Server 2005 without Service Pack 2 and have Microsoft Office 2007 the following error (bug) will occur when trying to execute an MDX query or edit it manually in the Design Mode:
Query preparation failed.
-> Error in the application. (msmgdsrv)
If you are using SQL Server 2005 installing Service Pack 2 is highly recommended.
- Please note that in a different case it is also possible that the Analysis Services cube modeller has decided to use a consecutive integer key for the Date dimension table. In such case since date names are unique, try converting to the name of the member, so you get the following (note that there isn't an ampersand):
[Date].[Date].[December 1, 2009]
It is recommended that YYYYMMDD format is used for the integer key so that you can easily convert from date to integer to resolve the member by key. Thanks to Teo Lachev who submitted most of this observation and Dustin Ryan for your assistance.