Calendar Control(Date Picker) in SSRS

[ArvindRavish]

Hi,
I am using SQL Server 2005 Reporting Services.

I have a cube " Request Time " dimension member which is given below

[Request Time].[Only Date].&[01/01/2008]

My requirement is I want to somehow bind this cube member to the calendar
control(datepicker) in SQL Server Reporting Services 2005 so that I will be
able to filter the data based on this parameter.

But when I use datepicker for this parameter, I am getting the following
error message.

An error occured during local processing.
An error has occured during report processiong.
Query execution failed for data set 'TestDataSet'.
Query(47,14) the restrictions imposed by the CONSTRAINED flag in the
STRTOSET function were violated.

The MDX query which I am using is given below:

SELECT
NON EMPTY
{[ExpectedDiffHours],[CompletionDiffHours]} ON COLUMNS,

NONEMPTY
(
CROSSJOIN
(
[Dim Requests].[Dim Requests].[Dim Requests].MEMBERS,
[Request Time].[Date].[Date].MEMBERS,
[Expected Time].[Date].[Date].MEMBERS,
[Action Completed Time].[Date].[Date].MEMBERS
)
,[Measures].[Fact Text Mining Count]
)
ON ROWS
FROM [JTrackDW Cube]
WHERE ( IIF( STRTOSET(@RequestDate,CONSTRAINED).Count = 1,
STRTOSET(@RequestDate,CONSTRAINED), [Request Time].[Only Date].currentmember))

But when I use the same member as string in the report parameters list I am
able to filter the data.

So anybody please help me to get this working...
Any feedback or suggestions is welcome.

--
Regards,
ArvindRavish

[Bruce L-C [MVP]]

I don't know MDX syntax but what seems to me is happening is that to get the
calendar control you are setting the parameter type as date and then trying
to do a string based function on it. Try converting the parameter to a
string:

STRTOSET(convert(varchar(15),@RequestDate,)101),CONSTRAINED), [Request

Time].[Only Date].currentmember))

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"ArvindRavish" <Arvind...@discussions.microsoft.com> wrote in message
news:9F936C08-20A0-49C4...@microsoft.com...

[ArvindRavish]

Thanks for the response.

But the problem is MDX does not understand TSQL functions such as
varchar,convert.
It has its own set of functions defined.
The MDX query builder does not recognise varchar,convert functions.
So there is no way I can use your suggestion in my MDX query...

--
Regards,
ArvindRavish

[Bruce L-C [MVP]]

You could try not using the query builder. Switch to the generic (two pane),
I assume you can do this. It is one of the buttons to the right of the ...

See if it allows you then. There are lots and lots of SQL that the graphical
designer doesn't allow. I pretty much do everything with the generic
designer. Perhaps MDX syntax is similar.

If this does not work for you, you have another option. The query parameters
are mapped to a report parameter. If you click on the ... in the dataset tab
that brings you to where you can see the parameter mapping. The query
parameter can map against an expression. So instead of mapping directly to
the report parameter. Map it to an expression that references the report
parameter but converts it to a string.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"ArvindRavish" <Arvind...@discussions.microsoft.com> wrote in message

news:C2272456-BA24-4DE4...@microsoft.com...

[MattiasT]

Hello Arvind Ravish,

I have just completed the same task, having a date picker working togheter
with mdx code and solved it like this:

'I parsed togheter a string in the query parameter expression box like this:
[TABud Day].[Day].&[" & Format(CDate(Parameters!FromTABudDayDay.Value),
"yyyy-MM-dd") & "]"

'Your code would probably look something like this:
[Request Time].[Only Date].&[" & Format(CDate(Parameters!myDate.Value),
"dd/MM/yyyy") & "]"

'The key is format the datetime value from the date picker to a string in
your date format. Default is the US format.

Hope this helps,

/Mattias