MDX/分析服务计算本月迄今

发布于 2024-09-27 13:56:52 字数 441 浏览 1 评论 0原文

如果不需要的话，我通常不会参与 MDX，但是......

我有一个多维数据集，其中包含链接到时间维度（带有日期、月份、年份列等）的简单事实表，我想成为能够针对该月的某一天计算该天的本月迄今总计。

在 SQL 中这很容易，但是我不确定如何在 MDX 中实现这一点。

更新我有一个在固定日期工作的示例。现在我想知道如何在任何可能的日期执行此操作

WITH
MEMBER MTD_15_Feb_2010 AS
Aggregate
(
  MTD([Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].[02/15/2010]),
  [Measures].[Value]
)
SELECT
{
 MTD_15_Feb_2010
} ON 0
FROM [Cube]

原文

I don't usually get involved with MDX if I dont have to, but....

I have a cube with simple fact table linked to a time dimension (with date,month,year columns and so on) and I want to be able for a given day of the month calculate the month to date total for that day.

In SQL it would be a doddle, however I'm not sure of how to achieve this in MDX.

Update I have an example working for a fixed date. Now I want to know how to do this for any possible date

WITH
MEMBER MTD_15_Feb_2010 AS
Aggregate
(
  MTD([Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].[02/15/2010]),
  [Measures].[Value]
)
SELECT
{
 MTD_15_Feb_2010
} ON 0
FROM [Cube]

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

短暂陪伴 2024-10-04 13:56:53

您应该能够使用 MTD 函数创建 MDX。它将返回从当月第一天到传递给函数的成员的所有天数。

编辑：有多种方法可以随时更新示例。我建议将日期成员 [02/12/2010] 替换为 CURRENTMEMBER 函数。这还允许您显示多个日期的值，如下例所示。

WITH MEMBER [MTD_Value] AS AGGREGATE (
      MTD([Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].CURRENTMEMBER)
      , [Measures].[Value]
    )
SELECT [MTD_Value] ON 0
  , {
      [Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].[02/15/2010]
      , [Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].[01/15/2010]
      , [Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].[02/15/2009]
    } ON 1
FROM [Cube]

You should be able to create the MDX using the MTD function. It will return the set of all days from the first of the month up to the member passed to the function.

Edit: There are multiple ways to update your example for any day. I would suggest replacing the date member [02/12/2010] with the CURRENTMEMBER function. This would also allow you to display the value for multiple dates as in the example below.

WITH MEMBER [MTD_Value] AS AGGREGATE (
      MTD([Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].CURRENTMEMBER)
      , [Measures].[Value]
    )
SELECT [MTD_Value] ON 0
  , {
      [Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].[02/15/2010]
      , [Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].[01/15/2010]
      , [Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].[02/15/2009]
    } ON 1
FROM [Cube]

回复收藏 0 原文

~没有更多了~