带日期范围过滤器的MDX查询

带日期范围过滤器的MDX查询

本文介绍了带日期范围过滤器的MDX查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我是MDX查询的新手.我正在编写MDX查询以选择跨月的度量值,并且将日期范围作为过滤器放置在这里,以限制返回的月份数.例如,我希望每个月的销售收入在2014年1月1日到2014年6月30日的日期范围内.理想情况下,它应该给我六个月的销售价值,即一月,二月,三月,四月,五月和六月.但是,当我在下面的查询中编写时,出现错误.PFB下面在此处输入代码"ow查询".

I am new to the MDX queries. I am writing a MDX query to select a Measure value across months and I am putting date Range as filter here just to restrict no of Months returned. For eg I want Sales Revenue for each month in Date Range of 01-Jan-2014 to 30-Jun-2014. Ideally, it should give me sales value for six months i.e Jan, Feb, Mar, Apr, May and June. However when i write below query, I get error. PFB the below enter code here`ow query.

Select NON EMPTY {[Measures].[Target Plan Value]} ON COLUMNS,
NON EMPTY {[Realization Date].[Hierarchy].[Month Year].Members} ON ROWS
From [Cube_BCG_OLAP]
( { [Realization Date].[Hierarchy].[Date].&[20140101] :
[Realization Date].[Hierarchy].[Date].&[20141231] })

我得到的错误是 Hierarchy 层次结构已经出现在 Axis1 轴中.在这里,日期和月份年份属于名为实现日期的同一维表.请帮我.预先感谢.

The error I get is The Hierarchy hierarchy already appears in the Axis1 axis. Here Date and Month Year belong to same dimension table named as Realization Date. Please help me. Thanks in advance.

推荐答案

您错过了 WHERE 子句,但我想那是一个错字.如错误消息所示,两个或多个轴上不能具有相同层次结构的成员.在这种情况下,您可以使用下面这样的内容,在MDX术语中,该内容称为 Subselect .

You were missing the WHERE clause but I guess that was a typo. As your error message tells, you can't have members of the same hierarchy on two or more axes. In situations like this, you can use something like below which in MDX terminology is called Subselect.

Select NON EMPTY {[Measures].[Target Plan Value]} ON COLUMNS,
NON EMPTY {[Realization Date].[Hierarchy].[Month Year].Members} ON ROWS
From (
        SELECT
        [Realization Date].[Hierarchy].[Date].&[20140101] :
        [Realization Date].[Hierarchy].[Date].&[20141231] ON COLUMNS
        FROM [Cube_BCG_OLAP]
    )

这篇关于带日期范围过滤器的MDX查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-26 03:02