如何从MDX查询中提取日期中的月份年份 [英] How to extract month-year from date from MDX Query

问题描述

我需要使用Date来过滤来自多维数据集的数据，而无需包括日期. 目前，我正在使用以下MDX查询，即获取从一年的第一天开始到最后一天结束的度量数据.

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.

但是，我只希望日期行中的月份为月，而不是整天的DateTime. (这里我的日期字段是:[Period].[TransportDate])

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] )

以下是MDX查询.

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]
   )

上述查询会抓取从2015年第一天到最后一天开始的所有记录.请参阅所附图片(allRecords)

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

(例如日期(07-2015)，金额，CBM，LDM，数量，.寄售)

( 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.

修改

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.

我想以某种方式在同一列中包含年份和月份. 所以01会变成01-2014. 你能帮我吗

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

没有连接的结果

推荐答案

也许尝试将计算所得的成员托管在不同的层次结构中.我猜到了这个[Forwarder].[Forwarder].[All]，您将需要调整为多维数据集中存在的层次结构:

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]
  )

这篇关于如何从MDX查询中提取日期中的月份年份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！