• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2355
  • Last Modified:

MDX Date Range in Excel

Hi Everyone,
I'm using the CUBE functions in Excel to query my SSAS cube.
I have a CUBEVALUE function and I'm trying to get the value of a measure for a specific date range, like this:-

=CUBEVALUE("jago","({[date].[date time].&[2009-03-12T00:00:00]:[date].[date time].&[2009-03-13T00:00:00]})","[Measures].[Consignment Count]")

but all I get is N/A.

However, if I change the second date to be the same as the first, the query returns the correct answer for 2009-03-12.
Any Ideas? I'm quite new to MDX, so I guess I've made a newbie error!
0
WizzoP
Asked:
WizzoP
  • 2
1 Solution
 
WizzoPAuthor Commented:
I can get now the results I require in Management Studio using this :-
with member [Date].[date time].[wibble] as
 aggregate({[date].[date time].&[2009-03-11T00:00:00]:[date].[date time].&[2009-03-11T00:00:00]})
 select [date].[date time].[wibble] on columns,
 [measures].[consignment count]  on rows
from [MyCube]

but I can't find a way to get an aggregate function into the Cubevalue parameters in Excel.
0
 
CSLARSENCommented:
Hi

To my knowledge you can not aggregate like this with cubevalue functions.

Either you need to include the level that you want to summarize e.g. weeks or months in your cube and then reference the period length in question or if you want complete freedom you could maybe reference the days one by one and then use a standard excel sum function to get the final result.
cheers
cslarsen
0
 
WizzoPAuthor Commented:
Thanks for the response, although it wasn't the one I was hoping for!
I suspect that this requirement wont go away, and that I'll be looking for a solution again soon.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now