Search code examples
sqlssasmdx

MDX YTD calculated measure

I'm still new to MDX and I'm trying to get some basic functions to work in my SSAS cube. Can you guys point out what I'm doing wrong here? I've added a calculated measure to my cube with the following code:

CREATE MEMBER CURRENTCUBE.[Measures].[Amount YTD]
AS
  AGGREGATE(
    YTD([OrderDate].[Calendar].CurrentMember)
   ,[Measures].[Amount]),
VISIBLE = 1, ASSOCIATED_MEASURE_GROUP = 'MyMeasureGroup';

After that I'm trying to get some data going...

SELECT 
NON EMPTY
{
  [Measures].[Amount]
, [Measures].[Amount YTD]
} ON COLUMNS,
NON EMPTY
{
  [OrderDate].[Month].ALLMEMBERS *
  [Product].[Product Group].ALLMEMBERS 
} ON ROWS
FROM (SELECT ([OrderDate].[Year].&[2014-01-01T00:00:00]:
              [OrderDate].[Year].&[2015-01-01T00:00:00]) ON COLUMNS
FROM [SalesOrderIntake])

This is the output I'm getting:

Query result

I'm not seeing any errors in my Output messages, which makes it difficult for me to figure out what is acting up. Hoping you guys can help me out on this one.

FYI: the actual select is just for testing purposes. I just want to get that YTD running. I've tried several things and it always comes out empty, so I was hoping to get some actual errors if I would query it directly in SSMS instead of using a BI tool. Also, the OrderDate dimension is a generated Time dimension which was provided to me by VS.

Solution

  • In your query you're using what looks like an attribute hierarchy:

    [OrderDate].[Month].ALLMEMBERS

    Whereas the measure uses the user hierarchy:

    [OrderDate].[Calendar]

    If you use Calendar in your script does it work ok?