McSsporran
asked on
Cannot filter query MDX
I have created the following MDX Query to determine a date + 91 Days which I am then going to use this as a dataset for a parameter in a SRSS report:
I now want to filter this on the measure [DayOfWeek] to only show the 7's (Sunday) so I do not get duplicates in my Paramter, which I do by amending the code as follows:
However when I run it I get the following Error:
Executing the query ...
Execution of the managed stored procedure datepart failed with the following error: Exception has been thrown by the target of an invocation.Argument 'DateValue' cannot be converted to type 'Date'..
Execution complete
Any advice anyone could give would be greatly appreciated.
Many Thanks
Jon
WITH
Member [Measures].[StartDateValue] As [Time - Expected Receipt Date].[Date].CURRENTMEMBER.UNIQUENAME
Member [Measures].[StartWeek] As [Time - Expected Receipt Date].[Fiscal Year And Week].CurrentMember.Name
MEMBER [Measures].[DayOfWeek] As datepart("w",[Time - Expected Receipt Date].[Date].CURRENTMEMBER.Member_Name)
Member [Measures].[EndDateValue] As membertostr(StrToMember("[Time - Expected Receipt Date].[Date].&" + Format( CDate(DateAdd('d',91, [Time - Expected Receipt Date].[Date].CurrentMember.Name)), "[yyyy-MM-ddTHH:mm:ss]" ) ))
Select
{
[Measures].[DayOfWeek],
[Measures].[StartDateValue],
[Measures].[StartWeek],
[Measures].[EndDateValue]
} On Columns,
[Time - Expected Receipt Date].[Date].[Date] On Rows
From
[Retail]
I now want to filter this on the measure [DayOfWeek] to only show the 7's (Sunday) so I do not get duplicates in my Paramter, which I do by amending the code as follows:
WITH
Member [Measures].[StartDateValue] As [Time - Expected Receipt Date].[Date].CURRENTMEMBER.UNIQUENAME
Member [Measures].[StartWeek] As [Time - Expected Receipt Date].[Fiscal Year And Week].CurrentMember.Name
MEMBER [Measures].[DayOfWeek] As datepart("w",[Time - Expected Receipt Date].[Date].CURRENTMEMBER.Member_Name)
Member [Measures].[EndDateValue] As membertostr(StrToMember("[Time - Expected Receipt Date].[Date].&" + Format( CDate(DateAdd('d',91, [Time - Expected Receipt Date].[Date].CurrentMember.Name)), "[yyyy-MM-ddTHH:mm:ss]" ) ))
Select
{
[Measures].[DayOfWeek],
[Measures].[StartDateValue],
[Measures].[StartWeek],
[Measures].[EndDateValue]
} On Columns,
filter([Time - Expected Receipt Date].[Date].[Date], [Measures].[DayOfWeek] = 7) On Rows
From
[Retail]
However when I run it I get the following Error:
Executing the query ...
Execution of the managed stored procedure datepart failed with the following error: Exception has been thrown by the target of an invocation.Argument 'DateValue' cannot be converted to type 'Date'..
Execution complete
Any advice anyone could give would be greatly appreciated.
Many Thanks
Jon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.