CODEWITHSUNDEEP
sql-server-2008-r2ssasmdx
Sparky
Sparky

Reputation: 763

MDX query not accepting date values

I'm a SSAS newbie and i'm trying to query a cube to retrieve data against aome measure groups order by date. The date range i wish to specify in my query. The query I'm using is this:-

SELECT
{
    [Measures].[Measure1],
    [Measures].[Measure2],
    [Measures].[Measure3]
} 
ON COLUMNS,
NON EMPTY{
   [Date].[AllMembers]
         } 
ON ROWS
FROM (SELECT ( STRTOMEMBER('2/23/2013', CONSTRAINED) : 
STRTOMEMBER('3/1/2013', CONSTRAINED) ) ON COLUMNS 
FROM   [MyCube])

However it gives me the following error
Query (10, 16) The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated.

I tried removing the constrained keyword and then even strtomember function. But in each cases i got the following errors respectively
Query (10, 16) The STRTOMEMBER function expects a member expression for the 1 argument. A string or numeric expression was used.
and
*Query (10, 14) The : function expects a member expression for the 1 argument. A string or numeric expression was used. *

I can understand from the last two errors that i need to include the constraint keyword. But can anyone tell me why this query wont execute?

Upvotes: 4

Views: 6789

Answers (3)

sarvam
sarvam

Reputation: 11

FROM ( SELECT (
   STRTOMEMBER(@FromDateCalendarDate, CONSTRAINED) :
   STRTOMEMBER(@ToDateCalendarDate, CONSTRAINED) ) ON COLUMNS

Upvotes: 1

Rakesh Sharma
Rakesh Sharma

Reputation: 11

Please execute below script. Extract your date dimension attribute copy it by right clicking and paste it in STRTOMEMBER value.

It will works fine.

 SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS 
 FROM ( SELECT ( STRTOMEMBER('[Date].[Date].&[20050701]')  : 
 STRTOMEMBER('[Date].[Date].&[20061007]') )  ON COLUMNS 
 FROM [Adventure Works])

Upvotes: 1

findango
findango

Reputation: 1483

The string that you pass as the member expression must be a fully-qualified member name, or resolve to one. Use the same format as you did in the SELECT.

For example:

STRTOMEMBER('[Date].[2/23/2013]', CONSTRAINED)

Edit: I just noticed the syntax of your range select looks wrong -- you need to use {...}, not (...). 

SELECT { 
    STRTOMEMBER('2/23/2013', CONSTRAINED) : 
    STRTOMEMBER('3/1/2013', CONSTRAINED) }

Upvotes: 3

Related Questions