How to extract month-year from date from MDX Query

Question

I need to filter data from Cube using Date without day inclusion. Currently I am using following MDX query, that is fetching measures data starting from 1st day of the year and ending on the last day.

But, I only want to have Month-Year in date time at rows rather than DateTime with all days. ( Here my field for date is : [Period].[TransportDate] )

Following is the MDX Query.

SELECT 
  NON EMPTY 
    { 
      [Measures].[ConsignmentCount]
    , [Measures].[CBM]
    , [Measures].[LDM]
    , [Measures].[Weight]
    , [Measures].[Amount] } ON COLUMNS,
  NON EMPTY 
    { [Period].[TransportDate].Children } ON ROWS
FROM 
  ( 
     SELECT 
      ( 
        { [Period].[YearAndMonth].[ConsignmentYear].&[2014]
        , [Period].[YearAndMonth].[ConsignmentYear].&[2015] } 
      ) ON COLUMNS
      FROM [RebellOlap]
   )

Above query fetching all records starting from 1st day till the last day of 2015. See attached image ( allRecords )

But I want somehow in following manner ( Required Data Set ) I want single column instead of Month and year. So final data set should be

( e.g Date ( 07-2015 ), Amount,CBM, LDM, Num,. Consignments )

I know there's a way to extract only month and year from the whole date. But that works only for single date. What I want to have all dates must be filtered with the inclusion of month and Year and data should also corresponds to those dates accordingly. See above expected filtered data set.

Edit

WITH MEMBER [YearMo] AS 
     [Period].[ConsignmentYear].Currentmember.Name 
     + "-" + 
     [Period].[ConsignmentMonth].Currentmember.Name
SELECT 
  NON EMPTY 
    { 
      [Measures].[ConsignmentCount]
    , [Measures].[CBM]
    , [Measures].[LDM]
    , [Measures].[Weight]
    , [Measures].[Amount] 
   } ON COLUMNS , 
NON EMPTY 
   (
     {  
       [Period].[YearAndMonth].[ConsignmentMonth] 
     } 
   ) ON ROWS 
FROM 
  ( 
     SELECT 
        ( 
          { 
            [Period].[YearAndMonth].[ConsignmentYear].&[2014]
          , [Period].[YearAndMonth].[ConsignmentYear].&[2015] 
          } 
        ) ON COLUMNS
     FROM [RebellOlap]
  )

above produces result but without Year.

I want somehow to have year with month on the same columns. so 01 would becomd 01-2014. Could you help me

Results without concatenation

whytheq whytheq · Accepted Answer · 2016-02-27T12:35:44

Maybe try hosting the calculated member in a different hierarchy. I have guessed this [Forwarder].[Forwarder].[All] and you will need to adjust to a hierarchy that exists in your cube:

WITH MEMBER [Forwarder].[Forwarder].[All].[YearMo] AS 
     [Period].[Year].Currentmember.Name 
     + "-" + 
     [Period].[Month].Currentmember.Name
SELECT 
  NON EMPTY 
    { 
      [Measures].[ConsignmentCount]
    , [Measures].[CBM]
    , [Measures].[LDM]
    , [Measures].[Weight]
    , [Measures].[Amount] 
   } ON COLUMNS , 
NON EMPTY 
      [Period].[Year].[Year]
     *[Period].[Month].[Month]
     *[Forwarder].[Forwarder].[All].[YearMo] 
     ON ROWS 
FROM 
  ( 
     SELECT 
        ( 
          { 
            [Period].[YearAndMonth].[ConsignmentYear].&[2014]
          , [Period].[YearAndMonth].[ConsignmentYear].&[2015] 
          } 
        ) ON COLUMNS
     FROM [RebellOlap]
  )

Or if the above produces an error then you may need to create a measure first:

WITH 
MEMBER [Measures].[YearMoString] AS 
     [Period].[Year].Currentmember.Name 
     + "-" + 
     [Period].[Month].Currentmember.Name
MEMBER [Forwarder].[Forwarder].[All].[YearMo] AS 
     (
       [Forwarder].[Forwarder].[All]
      ,[Measures].[YearMoString]
     )
SELECT 
  NON EMPTY 
    { 
      [Measures].[ConsignmentCount]
    , [Measures].[CBM]
    , [Measures].[LDM]
    , [Measures].[Weight]
    , [Measures].[Amount] 
   } ON COLUMNS , 
NON EMPTY 
      [Period].[Year].[Year]
     *[Period].[Month].[Month]
     *[Forwarder].[Forwarder].[All].[YearMo] 
     ON ROWS 
FROM 
  ( 
     SELECT 
        ( 
          { 
            [Period].[YearAndMonth].[ConsignmentYear].&[2014]
          , [Period].[YearAndMonth].[ConsignmentYear].&[2015] 
          } 
        ) ON COLUMNS
     FROM [RebellOlap]
  )

How to extract month-year from date from MDX Query

2 Answers