CODEWITHSUNDEEP
sql-serversql-server-2008ssasmdxcube
Junior Vasquez
Junior Vasquez

Reputation: 63

MDX Query to include date range

I'm using this query (MDX). It's returning data, but it's returning data for multiple years, where i'm trying to just get the dates in the date range: for example if I want 10/1/2013 the query returns 10/1/2013,10/1/2014,10/1/2015.
I'm looking for the specific date range, 10/13/2013 and 01/13/2014.
Why am I getting multiple years, where I specifically ask for that date range? 

SELECT              {[Measures].[Network Calls Received]} 
                    ON COLUMNS,
                    non empty               

                   [Dim Time].[Half Hour of Day].members  ON Rows

FROM 

                  [OTS Analysis Services]
      where [Dim Date].[Date].&[10/13/2013]:[Dim Date].[Date].&[01/13/2014]

Upvotes: 1

Views: 2764

Answers (2)

BI Dude
BI Dude

Reputation: 2016

I believe the issue is with your reference. Usually dimension member key is an integer. so your range should look like 

 where ([Dim Date].[Date].&[20131013]:[Dim Date].[Date].&[20140113])

You could check you member reference by:

  1. Connecting to SSMS Analysis Services
  2. Create New MDX Query.
  3. Drag Date Member from The Hierarchy to the Editor.

Upvotes: 0

Alex Peshik
Alex Peshik

Reputation: 1515

Looks like your filter has ONE wrong key. Once wrong key is used, server uses NULL instead of it.

Here is an example:

Unfiltered (to show data sample): SSAS_WrongKeys_Unfiltered

Filtered correctly from March 2014 to June 2014: SSAS_WrongKeys_FilteredGood

Than we add wrong key to the right member (and the same as using NULL!): SSAS_WrongKeys_FilteredBad_Right

Once we use both wrong keys, no chance to figure out the dimension used (the same as both NULLs): SSAS_WrongKeys_FilteredBad_Both

Please check both members, but especially [Dim Date].[Date].&[01/13/2014]. Looks like there is no such member in this dimension.

And keys addressing format needs checking too, as BI Dude wrote some time ago (many thanks to him!).

Upvotes: 1

Related Questions