如何在mdx查询中的列上获取最小或最大日期 [英] how to get min or max date on columns in mdx query

问题描述

在此示例中，我可以实现什么mdx查询逻辑，以便在hrid = 1的结果集中获得两行，其中1/1/16作为第一行的最小日期(开始)，其中某些属性显示在值"A"的列上 和1/15/16作为第二行的最小日期(开始)，其中某属性的值为"B"，并且为小节.无论该维度行所对应的数据是什么，其汇总.

我正想看看2016年1月

我尝试的所有方法似乎都获得了最小日期值1/1/1900，或者两行的值都为1/1/2016，或者由于我无法弄清而出现错误.

这是我的mdx示例:

WITH MEMBER [Measures].[Start] as 
  (
-- min date that the combination of someattribute and hrid have certain 
-- value withing the range of the where clause restriction of january 2016

SELECT { 
[Measures].[Start]
, [Measures].[Whatevers]
} ON COLUMNS
, NON EMPTY { 
[Agent].[HRID].children
* [Agent].[someAtribute].Members
} ON ROWS
FROM [RADM_REPORTING]
WHERE ( 
    [Date].[Date View].[Month].&[201601]
    )

解决方案

这是一个更普遍的潜在方向:

WITH 
  MEMBER [Measures].[Start] AS 
    Min
    (
      (EXISTING 
        [Date].[Date].[Date].MEMBERS)
     ,IIF
      (
        [Measures].[Internet Sales Amount] = 0
       ,NULL
       ,[Date].[Date].CurrentMember.MemberValue
      )
    ) 
SELECT 
  NON EMPTY 
    {
      [Measures].[Start]
     ,[Measures].[Internet Sales Amount]
    } ON COLUMNS
 ,NON EMPTY 
    [Product].[Product Categories].[Product] ON ROWS
FROM [Adventure Works]
WHERE 
  [Date].[Calendar].[Calendar Year].&[2005];

它提供了以下内容:

what mdx query logic could i implement for this example to get two rows in result set for hrid = 1 with 1/1/16 as min date(start) for first row where someattribut shows up on column with value 'A' and 1/15/16 as min date(start) for second row where someattribute has value of 'B' and measure.whatevers has its aggregation for whatever data corresponds to that dimension row.

Im trying to just look at january 2016

everything ive tried i seem to get min date values of 1/1/1900 or both rows have value of 1/1/2016 or i get errors since i cant figure it out.

heres my mdx sample:

WITH MEMBER [Measures].[Start] as 
  (
-- min date that the combination of someattribute and hrid have certain 
-- value withing the range of the where clause restriction of january 2016

SELECT { 
[Measures].[Start]
, [Measures].[Whatevers]
} ON COLUMNS
, NON EMPTY { 
[Agent].[HRID].children
* [Agent].[someAtribute].Members
} ON ROWS
FROM [RADM_REPORTING]
WHERE ( 
    [Date].[Date View].[Month].&[201601]
    )

解决方案

Here is a potential direction that is more general:

WITH 
  MEMBER [Measures].[Start] AS 
    Min
    (
      (EXISTING 
        [Date].[Date].[Date].MEMBERS)
     ,IIF
      (
        [Measures].[Internet Sales Amount] = 0
       ,NULL
       ,[Date].[Date].CurrentMember.MemberValue
      )
    ) 
SELECT 
  NON EMPTY 
    {
      [Measures].[Start]
     ,[Measures].[Internet Sales Amount]
    } ON COLUMNS
 ,NON EMPTY 
    [Product].[Product Categories].[Product] ON ROWS
FROM [Adventure Works]
WHERE 
  [Date].[Calendar].[Calendar Year].&[2005];

It gives the following:

这篇关于如何在mdx查询中的列上获取最小或最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！