MDX Date Range in Excel

Posted on 2009-04-28
Last Modified: 2016-02-13
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!
Question by:WizzoP

    Author Comment

    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.
    LVL 15

    Accepted Solution


    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.

    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now