MDX如何根据最新日期检索数据 [英] MDX How to retrieve data based on the latest date

问题描述

如何仅基于该特定记录的最新日期来检索数据.

How to retrieve data based on the latest date for that particular record only.

SELECT 
  [Measures].[Assessment Patients Detail] ON COLUMNS
 ,NON EMPTY 
    (
      [DimAssessment].[Assessment Text].&[Employee Wellness HRA]
     ,[DimAssessment].[Question Text].&[Do you use tobacco products?]
     ,[DimPatient].[Patient Key].[Patient Key]
     ,Generate
      (
        [DimAssessment].[Answer Text].[Answer Text].MEMBERS
       ,
          [DimAssessment].[Answer Text].CurrentMember
        * 
          Tail
          (
            NonEmpty
            (
              [DimDate].[Full Date Alternate Key].[Full Date Alternate Key].MEMBERS
             ,[DimAssessment].[Answer Text].CurrentMember
            )
           ,[DimPatient].[Patient Key]
          )
      )
    ) ON ROWS
FROM [CareManagement];

推荐答案

我认为您在Generate内使用了错误的层次结构?

I think you're using the wrong hierarchy inside the Generate?

SELECT 
  [Measures].[Assessment Patients Detail] ON 0
 ,NON EMPTY 
      {[DimAssessment].[Assessment Text].&[Employee Wellness HRA]}*
      {[DimAssessment].[Question Text].&[Do you use tobacco products?]}*
      {Generate
      (
        [DimPatient].[Patient Key].[Patient Key].MEMBERS
       ,
          [DimPatient].[Patient Key].CurrentMember
        * 
          Tail
          (
            NonEmpty
            (
              [DimDate].[Full Date Alternate Key].[Full Date Alternate Key].MEMBERS
             ,[DimPatient].[Patient Key].CurrentMember
            )
          )
      )}*
      {[DimAssessment].[Answer Text].[Answer Text].MEMBERS} ON 1
FROM [CareManagement];

这与AdvWrks中的情况类似-SO上的多个用户都使用此多维数据集，因此我们都可以玩:

Here is a similar scenario in AdvWrks - several users on SO use this cube so we can all play:

SELECT 
  [Measures].[Internet Sales Amount] ON 0
 ,NON EMPTY 
      {
        [Customer].[Customer].[Alexis Thomas]
       ,[Customer].[Customer].[Carlos Morgan]
       ,[Customer].[Customer].[Carlos Scott]
      }*
      [Customer].[Customer Geography].[Country]*
      [Date].[Calendar].[Month] ON 1
FROM [Adventure Works];

它给出了这一点:

我可以开始尝试添加Generate，并且得到以下结果，但是只要添加Tail，结果就会消失-不确定为什么会这样:

I can start adding in the Generate attempt and I get as far as the following but as soon as I add in Tail the results disappear - unsure why it is behaving this way:

SELECT 
  [Measures].[Internet Sales Amount] ON 0
 ,NON EMPTY 
    Generate
    (
      {
        [Customer].[Customer].[Alexis Thomas]
       ,[Customer].[Customer].[Carlos Morgan]
       ,[Customer].[Customer].[Carlos Scott]
      } AS S
     ,
        s.CurrentMember
      * 
        NonEmpty
        (
            [Date].[Calendar].[Month].MEMBERS
          * 
            [Customer].[Customer Geography].[Country]
         ,s.CurrentMember
        )
    ) ON 1
FROM [Adventure Works];

这篇关于MDX如何根据最新日期检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！