Passing DateTime Parameter to MDX Query

HI All,
Looking for a little help with this issue: I currently have an MDX
query which is accepting 2 parameters for a date range. The query is
as follows:
SELECT NON EMPTY { [Measures].[Line Item Sales Amount] } ON COLUMNS
FROM ( SELECT ( STRTOMEMBER(@.FromDateDateForReporting, CONSTRAINED) :
STRTOMEMBER(@.ToDateDateForReporting, CONSTRAINED) ) ON COLUMNS FROM
[KIT Retail Sales]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The parameters pull their respective values from a dataset populated
with this query:
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Date For
Reporting].CURRENTMEMBER.MEMBER_CAPTION' MEMBER
[Measures].[ParameterValue] AS '[Date].[Date For
Reporting].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel]
AS '[Date].[Date For Reporting].CURRENTMEMBER.LEVEL.ORDINAL' SELECT
{[Measures].[ParameterCaption], [Measures].[ParameterValue],
[Measures].[ParameterLevel]} ON COLUMNS , [Date].[Date For
Reporting].ALLMEMBERS ON ROWS FROM [KIT Retail Sales]
In the Report Parameters window, I would like these parameters to be
DateTime datatypes so my users would get the handy Calendar control.
The only way I can get them to work now is by setting them to string
datatype. This works, but the dropdown list is populated with
thousands of date strings (in the form 01/01/2005, 01/02/2005, etc...)
which makes it difficult for users.
Whenver I attempt to make them DateTime datatypes, I get the error:
"The Property 'ValidValues' of the report parameter
'FromDateDateForReporting' doesn't have the expected type" when I run
the report.
My question is: How can I do the conversion to get the datetime back
to a string to use in the first MDX query above?Quick followup: In the report parameters window, if I choose to make
the available values "Non-queried", I can see the calendar control on
the preview page. However when choosing dates for the range, and then
clicking "view report", I get the following error:
The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
function have been violated.|||Guys-
This appears to work fine. However, now go add a column to your main query
(the one with the parameter) and now your report parameter is trashed because
SSRS decided it needed to refresh it. Anybody know of a way to tell it to
leave that parameter alone?
"Bret Updegraff" wrote:
> This is the method from my class file. It is in C# so if you are using
> VB.net or if you are putting this in the Code tab under Report-->
> Properties you will need to convert to VB.NET
> public string ParseDateToMDX( DateTime sDate )
> {
> string year = sDate.Year.ToString();
> string month = sDate.Month.ToString();
> string day = sDate.Day.ToString();
> StringBuilder MDXValue = new StringBuilder();
> if (month.Length == 1)
> {
> month = "0" + month; //pad month
> }
> if (day.Length == 1)
> {
> day = "0" + day; //pad day
> }
> MDXValue.Append("[PostingDate].[Date].&[").Append(year.ToString()).Append("-");
> MDXValue.Append(month.ToString()).Append("-");
> MDXValue.Append(day.ToString()).Append("T00:00:00]");
> return MDXValue.ToString();
> }
> Hope this helps
> "dvdastor@.yahoo.com" <dvdastor@.yahoo.com> wrote in message
> news:1137680384.850501.248380@.g49g2000cwa.googlegroups.com:
> > Quick followup: In the report parameters window, if I choose to make
> > the available values "Non-queried", I can see the calendar control on
> > the preview page. However when choosing dates for the range, and then
> > clicking "view report", I get the following error:
> > The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
> > function have been violated.
>
> --
> Bret Updegraff, MCAD,MCSD,MCDBA
> Microsoft MVP - SQL Server
> Crowe Chizek and Company LLC
> President - Indianapolis Professional Association for SQL Server
> Join our SQL Server Community http;//www.IndyPASS.org
> 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
>|||I hear this may be fixed in SP1:
http://prologika.com/CS/forums/thread/1069.aspx
"FurmanGG" wrote:
> Guys-
> This appears to work fine. However, now go add a column to your main query
> (the one with the parameter) and now your report parameter is trashed because
> SSRS decided it needed to refresh it. Anybody know of a way to tell it to
> leave that parameter alone?
> "Bret Updegraff" wrote:
> > This is the method from my class file. It is in C# so if you are using
> > VB.net or if you are putting this in the Code tab under Report-->
> > Properties you will need to convert to VB.NET
> >
> > public string ParseDateToMDX( DateTime sDate )
> > {
> > string year = sDate.Year.ToString();
> > string month = sDate.Month.ToString();
> > string day = sDate.Day.ToString();
> > StringBuilder MDXValue = new StringBuilder();
> >
> > if (month.Length == 1)
> > {
> > month = "0" + month; //pad month
> > }
> > if (day.Length == 1)
> > {
> > day = "0" + day; //pad day
> > }
> >
> > MDXValue.Append("[PostingDate].[Date].&[").Append(year.ToString()).Append("-");
> > MDXValue.Append(month.ToString()).Append("-");
> > MDXValue.Append(day.ToString()).Append("T00:00:00]");
> >
> > return MDXValue.ToString();
> > }
> >
> > Hope this helps
> >
> > "dvdastor@.yahoo.com" <dvdastor@.yahoo.com> wrote in message
> > news:1137680384.850501.248380@.g49g2000cwa.googlegroups.com:
> >
> > > Quick followup: In the report parameters window, if I choose to make
> > > the available values "Non-queried", I can see the calendar control on
> > > the preview page. However when choosing dates for the range, and then
> > > clicking "view report", I get the following error:
> > > The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
> > > function have been violated.
> >
> >
> > --
> > Bret Updegraff, MCAD,MCSD,MCDBA
> > Microsoft MVP - SQL Server
> > Crowe Chizek and Company LLC
> > President - Indianapolis Professional Association for SQL Server
> > Join our SQL Server Community http;//www.IndyPASS.org
> > 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
> >
> >