MDX Month To Date Year To Date Query

Question

I'm trying to build a query which will retrieve values from a cube and build a month to date and year to date SSRS report based on the current date. I'm basically trying to build a report that should look something like this...

Type Customer Product Group Quantity Shipped MTD Quantity Shipped YTD
=====================================================================

OE   CompanyX Bikes                    5                 396 
              Helmets                  10                254

     CompanyY Repair Kits              93                653
              T-shirts                 38                564
              Shoes                    10                120

I'm really struggling with this and so far, this is what I've come up with...

 WITH 
 MEMBER [Measures].[Quantity Shipped YTD] As Sum(YTD([Date].[Dates].CurrentMember),[Measures].[Quantity Shipped])
 Member [Measures].[Quantity Shipped MTD] As Sum(MTD([Date].[Dates].CurrentMember),[Measures].[Quantity Shipped])
 SELECT NON EMPTY { [Measures].[Quantity Shipped MTD], [Measures].[Quantity Shipped YTD] } ON COLUMNS,
 NON EMPTY { ([Customer].[Business Type].[Business Type].ALLMEMBERS * [Customer].[Customer].[Customer].ALLMEMBERS * [Customer Sales].[Summary Prod Group 1].[Summary Prod Group 1].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
 FROM ( SELECT ( -{ [Customer Sales].[Summary Prod Group 1].&[Missing Product Group] } ) ON COLUMNS 
 FROM ( SELECT ( { [Customer].[Customer Code].&[2.254E3], [Customer].[Customer Code].&[2.063E3], [Customer].[Customer Code].&[2.183E3], [Customer].[Customer Code].&[2.261E3], [Customer].[Customer Code].&[5.3753E4], [Customer].[Customer Code].&[1.3084E5], [Customer].[Customer Code].&[1.37058E5] } ) ON COLUMNS 
 FROM ( SELECT ( { [Customer].[Business Type].&[OE] } ) ON COLUMNS 
 FROM ( SELECT ( [Date].[Day].&[Now()]) ON COLUMNS
 FROM [Sales])))) WHERE ( [Customer].[Customer Code].CurrentMember )  
 CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The query above doesn't retrieve anything though. All I get is a blank resultset. There are alot more fields that I need to pull through but I'm assuming if I can get quantity shipped to work, I can get everything else. Please help. Tx

Answer 1

Currently you don't have any dates in context which mean currentmember won't be finding much - to have a date in context you need to add an actual date member to your WHERE or SELECT clause - and not a SELECT clause of a subselect.

Now() is an unusual name for a member as it is a vba function, but if this really is the name, then you could move it to the where clause:

WITH 
  MEMBER [Measures].[Quantity Shipped YTD] AS 
    Sum
    (
      YTD([Date].[Dates].CurrentMember)
     ,[Measures].[Quantity Shipped]
    ) 
  MEMBER [Measures].[Quantity Shipped MTD] AS 
    Sum
    (
      MTD([Date].[Dates].CurrentMember)
     ,[Measures].[Quantity Shipped]
    ) 
SELECT 
  NON EMPTY 
    {
      [Measures].[Quantity Shipped MTD]
     ,[Measures].[Quantity Shipped YTD]
    } ON COLUMNS
 ,NON EMPTY 
    {
        [Customer].[Business Type].[Business Type].ALLMEMBERS*
        [Customer].[Customer].[Customer].ALLMEMBERS*
        [Customer Sales].[Summary Prod Group 1].[Summary Prod Group 1].ALLMEMBERS
    } ON ROWS
FROM 
(
  SELECT 
    {
      [Customer].[Customer Code].&[2.254E3]
     ,[Customer].[Customer Code].&[2.063E3]
     ,[Customer].[Customer Code].&[2.183E3]
     ,[Customer].[Customer Code].&[2.261E3]
     ,[Customer].[Customer Code].&[5.3753E4]
     ,[Customer].[Customer Code].&[1.3084E5]
     ,[Customer].[Customer Code].&[1.37058E5]
    } ON 0
   ,{[Customer].[Business Type].&[OE]} ON 1
   ,-
      {
        [Customer Sales].[Summary Prod Group 1].&[Missing Product Group]
      } ON 2
  FROM [Sales]
)
WHERE 
  [Date].[Day].&[Now()];

I suspect that this [Date].[Day].&[Now()]; is not the name of the member and you're trying to do something dynamic in which case you need to research using strToMember

I've constructed something comparable in the AdvWrks cube:

WITH 
  MEMBER [Measures].[_YTD] AS 
    Aggregate
    (
      YTD([Date].[Calendar].CurrentMember)
     ,[Measures].[Order Quantity]
    ) 
  MEMBER [Measures].[_MTD] AS 
    Aggregate
    (
      MTD([Date].[Calendar].CurrentMember)
     ,[Measures].[Order Quantity]
    ) 
SELECT 
  {
    [Measures].[_YTD]
   ,[Measures].[_MTD]
  } ON COLUMNS
 ,[Product].[Category].Children ON ROWS
FROM [Adventure Works]
WHERE 
  [Date].[Calendar].[Date].&[20050805];

It returns this:

If I want to make the above dynamic by using the vba function now() then this needs to happen:

WITH
  MEMBER [Measures].[Key for Today] AS 
    Format
    (
      Now()
     ,'yyyyMMdd'
    ) 
  MEMBER [Measures].[Today string] AS 
    '[Date].[Calendar].[Date].&[' + [Measures].[Key for Today] + ']'  
  MEMBER [Measures].[_YTD] AS 
    Aggregate
    (
      YTD([Date].[Calendar].CurrentMember)
     ,[Measures].[Order Quantity]
    ) 
  MEMBER [Measures].[_MTD] AS 
    Aggregate
    (
      MTD([Date].[Calendar].CurrentMember)
     ,[Measures].[Order Quantity]
    ) 
SELECT 
  {
    [Measures].[_YTD]
   ,[Measures].[_MTD]
  } ON COLUMNS
 ,[Product].[Category].Children ON ROWS
FROM [Adventure Works]
WHERE 
  StrToMember
    (
      [Measures].[Today string]
     ,constrained
    ) ;