Date Parameters show as Strings when using Analysis Services as a data source

We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.

The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.

I have two quick questions:

Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes? Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.

Code Snippet

SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@.FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@.ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@.LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@.LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.

Thanks very much in advance for your help.

Regards,

Steve

Steve,

i've been there, done that, and it can be done.

For question 1, change your parameter type to a DateTime (no dataset required), and you will get the calendar control - you are getting a dropdown probably because you have got it as a string type and because you are supplying a dataset of values from the date heirarchy of your cube. The real trick to this is converting the output of the calendar control to something that resembles the members of your date heirarchy.

For question 2, you need to change the way your date is represented so that you can include it as a parameter in your MDX. See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1480488&SiteID=1 for some actual examples i posted for someone else.

|||

Thanks for your help. I'm still missing something, because I'm still throwing an error. Could you take a look and see where I went wrong?

1. Opened the parameters created by the Wizard. Changed the Data type to DateTime. There are no boxes checked under Properties. Available values was changed to Non-queried; left the Label/Value matrix blank. Default values: Null.

2. Added Format(CDate(@.parameter),"yyyy-MM-ddT00:00:00".... to the MDX query. See below:

Code Snippet

SELECTNONEMPTY { [Measures].[Lead] } ONCOLUMNS, NONEMPTY { ([Store].[Store Region Type].[Store Region Type].ALLMEMBERS * [Store].[Store Region Name].[Store Region Name].ALLMEMBERS * [Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWSFROM ( SELECT ( STRTOMEMBER(Format(CDate(@.FromLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00"), CONSTRAINED) : STRTOMEMBER(Format(CDate(@.ToLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00"), CONSTRAINED) ) ONCOLUMNSFROM [Referral Leads]) CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

3. For reference, I ran the query under the Dataset that the Wizard created for the parameter FoLeadCreationDateCalendarDate. The results of that query show the ParameterValue field to be populated with values of the format [LeadCreationDate].[Calendar Date].&[2000-01-02T00:00:00]. This is why I selected "yyyy-MM-ddT00:00:00" as my format string.

4. The error I am throwing is:

TITLE: Microsoft Visual Studio

Query preparation failed.

ADDITIONAL INFORMATION:

The following system error occurred: Type mismatch. . (Microsoft SQL Server 2005 Analysis Services)

BUTTONS:

OK

After re-reading your post, I also tried:

Code Snippet

SELECTNONEMPTY { [Measures].[Lead] } ONCOLUMNS, NONEMPTY { ([Store].[Store Region Type].[Store Region Type].ALLMEMBERS * [Store].[Store Region Name].[Store Region Name].ALLMEMBERS * [Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWSFROM ( SELECT ( STRTOMEMBER("[LeadCreationDate].[Calendar Date].&[" + Format(CDate(@.FromLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) : STRTOMEMBER("[LeadCreationDate].[Calendar Date].&[" + Format(CDate(@.ToLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) ) ONCOLUMNSFROM [Referral Leads]) CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Also gave the same error.

Any help on this would be tremendously appreciated.

- Steve

|||

OK - it took me a few days but I figured out how to do it:

1. Develop the report using the Wizard.

2. As sluggy mentioned, change the Data Type of the date parameters to DateTime, and set Available Values to Non-queried.

3. On the Data tab, edit the cube's Dataset (click on the ... button).

4. Click on the Parameters tab of the Dataset dialog box. Replace the Value of the date parameters with ="[Dimension].[Member].&[" + Format(CDate(Parameters!ParameterName.Value), "yyyy-MM-ddT00:00:00") + "]" where [Dimension].[Member] and ParameterName are the dimensions and parameter names of your particular cube. Depending on how your dimension is set up, you may also need to change the format of the date.

5. There is no need to change the MDX of the query.

sql