MDX 查询比较不同年份

发布于 2024-11-25 19:03:40 字数 341 浏览 5 评论 0原文

我是 MDX 新手，想知道是否可以创建一个查询，在两个不同的轴上显示每年和每月的销售额，即使销售日期是单个维度。

类似于：

Sales    |   2010    |    2011    |   Diff   
---------+-----------+------------+----------
Jan      |  1234,00  |  2345,10   |  +80%
Feb      |    ...
...

编辑： 将 mondrian 添加到标签中，因为似乎有 mondrian 中不可用的其他 MDX 实现的可能性。

原文

I am new to MDX and wondered if it is possible to create a query that shows a Sales amount per Year and per Month on two different axes, even if the sales date is a single dimension.

Something like:

Sales    |   2010    |    2011    |   Diff   
---------+-----------+------------+----------
Jan      |  1234,00  |  2345,10   |  +80%
Feb      |    ...
...

EDIT: Added mondrian to tags, because there seem to be possibilities with other MDX implementations not available in mondrian.

分享到QQ

分享到微博

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

发布评论

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

七色彩虹 2024-12-02 19:03:40

是的，解决方案是围绕计算成员：

让我们想象一下您的初始 MDX 看起来像：

Select
 { [Calendar].[Year].[2010],[Calendar].[Year].[2011] } on 0,
 { [Calendar].[Months].members } on 1
from [Cube]

您可以在 [Year] 层次结构中添加计算成员：

With
 Member [Calendar].[Year].[Diff] as [Calendar].[Year].[2011] / [Calendar].[Year].[2010]-1, FORMAT_STRING='percent'
Select
 { [Calendar].[Year].[2010],[Calendar].[Year].[2011], [Calendar].[Year].[Diff] } on 0,
 { [Calendar].[Months].members } on 1
from [Cube]

您还可以使用实用或统计维度。这些维度而不是保存数据定义转换/函数，并且输出将不完全是您正在寻找的，但这是一个有趣的概念。

Yes the solution is around calculated members :

Let's imagine your initial MDX looks like :

Select
 { [Calendar].[Year].[2010],[Calendar].[Year].[2011] } on 0,
 { [Calendar].[Months].members } on 1
from [Cube]

You can add a calculated member in the [Year] hierarchy :

With
 Member [Calendar].[Year].[Diff] as [Calendar].[Year].[2011] / [Calendar].[Year].[2010]-1, FORMAT_STRING='percent'
Select
 { [Calendar].[Year].[2010],[Calendar].[Year].[2011], [Calendar].[Year].[Diff] } on 0,
 { [Calendar].[Months].members } on 1
from [Cube]

You can also add a more elegant and flexible solution, by using utility or statistical dimensions. Those dimension instead of holding data define transformations / functions and the output will not be exactly the one you're looking, but it's an interesting concept.

回复收藏 0 原文

枉心 2024-12-02 19:03:40

该问题可以通过 ParallelPeriod 函数解决：

WITH MEMBER [Measures].[Einheiten Vorjahr] 
  AS '(ParallelPeriod([Year],1),
      [Measures].[quantity])'
SELECT {[Measures].[quantity],[Measures].[Einheiten Vorjahr]} ON COLUMNS,
       [date].[2010].children on rows
FROM salesorderitems

The problem can be solved with the ParallelPeriod function:

WITH MEMBER [Measures].[Einheiten Vorjahr] 
  AS '(ParallelPeriod([Year],1),
      [Measures].[quantity])'
SELECT {[Measures].[quantity],[Measures].[Einheiten Vorjahr]} ON COLUMNS,
       [date].[2010].children on rows
FROM salesorderitems

回复收藏 0 原文

~没有更多了~