I set up a report using SQL 2005 reporting services
and connect to the cube data. I also have a filter of "From date" and
"TO date" for the reporting. I like to change that so people can see
the calendar and pickup the date from reporting. I am running to a
problem if I use "date" for that parameter. I am not able to make it
works because it only takes something like "[Time].
[Date].&[2005-01-01T00:00:00]" but it won't take "1/2/2007" do you
have any idea how I can make this happen? Thank you!


here is the codes

SELECT NON EMPTY { [Measures].[Rate90days], [Measures].[Count_90
Days], [Measures].[Count90days] } ON COLUMNS,
NON EMPTY { ([Type].[TypeBeta].[TypeBeta].ALLMEMBERS * [Type].
[TypeName].[TypeName].ALLMEMBERS * [Cat].[CatValue].
[CatValue].ALLMEMBERS * [Cat].[CatName].[CatName].ALLMEMBERS) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
( SELECT ( STRTOMEMBER(@FromTimeDate, CONSTRAINED) :
STRTOMEMBER(@ToTimeDate, CONSTRAINED) ) ON COLUMNS FROM [DW]) CELL
PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I've just implemented this

In reporting services build you query as per normal and mark the Date
Dimension / Hierarchy as a Parameter as normal.

Go to the Layout section, open up the report parameters and your right, the
designer will have created a string type parameter.

Change the type from String to DateTime and go back to the Data tab of the
report.
Select you main query data set and click on the ... button next to the drop
down
On the third tab you should see all the Cube parameters and how they are
mapped to the Report Parameters (Value)

Select the parameter in the value column and you should get a drop down,
select this and choose <Expression...>

What you then have to do is convert your report DateTime parameter back into
a string that your Dataset then will use

e.g.

="[Date].&[" & Year(Parameters!MyReportDateParameter.Value) & "-" &
Format(Month(Parameters!MyReportDateParameter.Valu e), "00") & "-" &
Format(Day(Parameters!MyReportDateParameter.Value) ,"00") & "T00:00:00]"

Regards

Simon

"Bob Yang" <bobyang3@gmail.com> wrote in message
news:1187034008.243742.70640@q4g2000prc.googlegrou ps.com...
> I set up a report using SQL 2005 reporting services
> and connect to the cube data. I also have a filter of "From date" and
> "TO date" for the reporting. I like to change that so people can see
> the calendar and pickup the date from reporting. I am running to a
> problem if I use "date" for that parameter. I am not able to make it
> works because it only takes something like "[Time].
> [Date].&[2005-01-01T00:00:00]" but it won't take "1/2/2007" do you
> have any idea how I can make this happen? Thank you!
>
>
> here is the codes
>
> SELECT NON EMPTY { [Measures].[Rate90days], [Measures].[Count_90
> Days], [Measures].[Count90days] } ON COLUMNS,
> NON EMPTY { ([Type].[TypeBeta].[TypeBeta].ALLMEMBERS * [Type].
> [TypeName].[TypeName].ALLMEMBERS * [Cat].[CatValue].
> [CatValue].ALLMEMBERS * [Cat].[CatName].[CatName].ALLMEMBERS) }
> DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
> ( SELECT ( STRTOMEMBER(@FromTimeDate, CONSTRAINED) :
> STRTOMEMBER(@ToTimeDate, CONSTRAINED) ) ON COLUMNS FROM [DW]) CELL
> PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
> FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>

Just to add a bit more to my last post, this is because the MDX query is
using the STRTOMEMBER and you must pass parameter to this in the form of
"[Time].[Date].&[2005-01-01T00:00:00]" (Note the quotes )

Noticed I missed the Time part out so try this expression and assuming that
your Report Parameter is called FromTimeDate

="[Time].[Date].&[" & Year(Parameters!FromTimeDate.Value) & "-" &
Format(Month(Parameters!FromTimeDate.Value), "00") & "-" &
Format(Day(Parameters!FromTimeDate.Value),"00") & "T00:00:00]"

You will have to do this for both your date parameters. The MDX query
shouldn't need changing. You could (though I don't know how) do this in all
the MDX but it's much easier to do this in the Parameter dialog!

Si


"Bob Yang" <bobyang3@gmail.com> wrote in message
news:1187034008.243742.70640@q4g2000prc.googlegrou ps.com...
> I set up a report using SQL 2005 reporting services
> and connect to the cube data. I also have a filter of "From date" and
> "TO date" for the reporting. I like to change that so people can see
> the calendar and pickup the date from reporting. I am running to a
> problem if I use "date" for that parameter. I am not able to make it
> works because it only takes something like "[Time].
> [Date].&[2005-01-01T00:00:00]" but it won't take "1/2/2007" do you
> have any idea how I can make this happen? Thank you!
>
>
> here is the codes
>
> SELECT NON EMPTY { [Measures].[Rate90days], [Measures].[Count_90
> Days], [Measures].[Count90days] } ON COLUMNS,
> NON EMPTY { ([Type].[TypeBeta].[TypeBeta].ALLMEMBERS * [Type].
> [TypeName].[TypeName].ALLMEMBERS * [Cat].[CatValue].
> [CatValue].ALLMEMBERS * [Cat].[CatName].[CatName].ALLMEMBERS) }
> DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
> ( SELECT ( STRTOMEMBER(@FromTimeDate, CONSTRAINED) :
> STRTOMEMBER(@ToTimeDate, CONSTRAINED) ) ON COLUMNS FROM [DW]) CELL
> PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
> FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>

Hi Simon,

thanks for sharing your knowledge, I've also read your blog, but it doesn't
work for nada in my case.

Using the current AdventureWorksDW and Cube from May 2007 (downloaded from
CodePlex), I try to make it work with a dozen of syntax variations.

e.g.
select non empty
{STRTOMEMBER("[Delivery
Date].[Calendar].[Date].&[2001-07-01T00:00:00]",constrained):
//{[Delivery Date].[Calendar].&[1]:
[Delivery Date].[Calendar].[Date].&[26]} on columns,
non empty ([Geography].[Geography].children) on rows
from [Channel Sales]

Commenting out STRTOMEMBER-line and using the following with its
..&[1]-member works fine.
The to be expected error message is: The restrictions imposed by the
CONSTRAINED flag in the STRTOMEMBER function were violated.

Looking inside the DW-Table of time the FullDateAlternateKey states:
2001-07-01 00:00:00.000
so I tried the MDX-Query with
..&[2001-07-01 00:00:00.000]"

And all with none, single, double quotes and so forth.
No luck.

Please give me a hint on what is wrong in the syntax.

Thanks in advance

Yours, Henry