MDX, dates and Calendar Control in Reporting services 2005

Hi,
I need to develop several reports in RS 2005 where the report
parameters are dates. The reports are based on a SQL Server 2005
Analysis Services Cube and consequently the reports need to be based
on MDX. My dates are formatted according to Swedish date standard. I
would like the users to pick the dates from a Calendar control.
(I have seen several smilar problems with dates, but all referring to
SQ statements, not MDX)
Here's my problem:
When I design my query in the Data tab of the report, I choose the
field that I want to use as a parameter (in this case a date) and tick
the checkbox indicating that I want a Report parameter created.
When I inspect the report in Preview, the date is not displayed in a
calendar, but instead as a tremendously long list. (Not good!) When I
look at the report parameter in Layout it has the datatype String.
- If I change the parameter to DateTime (in order to get a calendar),
I get a runtime error: "an error occurred during local report
processing. The property 'Valid Values' of report paramater
'KalenderDatum' doesn't have the expected type."
My question:
- What can I do to make Reporting Services understand that I want to
display dates?
- Can calendar controls be used with MDX at all'
- Is there someting I need to do in MDX to format my date as a
DateTime field?
- Does RS just not like Swedish' ;-)
Additional info:
- My cube is built on a staging database, and if I design a report
against that database with ordinary SQL statements using the same
DateTime field, I get a calendar control, and RS understands that I
want to display dates. Everything works fine.
- I have set the language property on my report to Swedish (the date
format being yyyy-mm-dd)
Please let me know if there is a workaround for this. Unfortunately
MDX is not exactly my second language either...
CarinaI had the same problem (but with US date formats). What you need to do is on
the "Data" tab in your RS project, change the MDX query to an expression by
putting an = there followed by your whole MDX query in double quotes. Then
concantenate in your parameter date fields. Here is an example of how I did
it for a range on [Start Date] based on 2 datetime parameters called
Begin_Date and End_Date. Note, be sure there are no control characters or
carriage returns in your MDX expression.
="SELECT NON EMPTY { [Measures].[Login Count]} ON COLUMNS, NON EMPTY {
[GroupUsers].[Application - Name].[Login Name].ALLMEMBERS * [Date].[Start
Date].&[" & Format(Parameters!Begin_Date.Value,"MM/dd/yyyy") &
"]:[Date].[Start Date].&[" & Format(Parameters!End_Date.Value,"MM/dd/yyyy") &
"] } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
[MyCubeDataset])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
I just set up regular Datetime report parameters for Begin_Date and End_Date
and added them to the dataset on the parameters tab. I formatted them into
the format I wanted inside the expression above because I needed the leading
zeroes for the date comparisons to work in MDX. Also, here is a link that
helped me:
http://msdn2.microsoft.com/en-us/library/aa902647(sql.80).aspx
Hope this helps.
"carina.jones@.sogeti.se" wrote:
> Hi,
> I need to develop several reports in RS 2005 where the report
> parameters are dates. The reports are based on a SQL Server 2005
> Analysis Services Cube and consequently the reports need to be based
> on MDX. My dates are formatted according to Swedish date standard. I
> would like the users to pick the dates from a Calendar control.
> (I have seen several smilar problems with dates, but all referring to
> SQ statements, not MDX)
> Here's my problem:
> When I design my query in the Data tab of the report, I choose the
> field that I want to use as a parameter (in this case a date) and tick
> the checkbox indicating that I want a Report parameter created.
> When I inspect the report in Preview, the date is not displayed in a
> calendar, but instead as a tremendously long list. (Not good!) When I
> look at the report parameter in Layout it has the datatype String.
> - If I change the parameter to DateTime (in order to get a calendar),
> I get a runtime error: "an error occurred during local report
> processing. The property 'Valid Values' of report paramater
> 'KalenderDatum' doesn't have the expected type."
> My question:
> - What can I do to make Reporting Services understand that I want to
> display dates?
> - Can calendar controls be used with MDX at all'
> - Is there someting I need to do in MDX to format my date as a
> DateTime field?
> - Does RS just not like Swedish' ;-)
> Additional info:
> - My cube is built on a staging database, and if I design a report
> against that database with ordinary SQL statements using the same
> DateTime field, I get a calendar control, and RS understands that I
> want to display dates. Everything works fine.
> - I have set the language property on my report to Swedish (the date
> format being yyyy-mm-dd)
>
> Please let me know if there is a workaround for this. Unfortunately
> MDX is not exactly my second language either...
> Carina
>|||Thank you so much for your input! With your help and the explanation
below I did figure it out :-). I'm a happy camper!
/Carina
In short (found this explanation on another site):
1. Develop the report.
2. 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.