Solved

Cannot filter query MDX

Posted on 2012-04-12
3
458 Views
Last Modified: 2013-01-24
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:  
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] 

Open in new window


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]

Open in new window



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
0
Comment
Question by:McSsporran
3 Comments
 
LVL 15

Accepted Solution

by:
Tim Humphries earned 500 total points
ID: 37855557
Hi Jon,

Have you tried converting [Time - Expected Receipt Date].[Date].CURRENTMEMBER.Member_Name to a date type explicitly using CDate() first?

Maybe the member_name isn't in the correct format to convert to a date properly (or maybe you have an 'unknown' member which is failing conversion?)

Tim
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS 2013 - Finding Datasets/StoredProcedures 4 38
Using this function 4 42
SQL Server Express or Standard? 5 32
Enabling flash installation using GPO 2 18
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question