如何编写按日期范围和维度成员值切片的mdx查询 [英] How can I write an mdx query that slices by both a date range and dimension member value

问题描述

我需要编写一个mdx查询，该查询的结果受维度值限制，但受日期范围限制.我知道如何做一个或另一个，但是我不知道怎么一次做.

I need to write an mdx query that limits its results by the value of a dimension but also by a date range. I know how to do one or the other but I can't figure out how to do both at once.

这适用于日期范围:

SELECT {[Measures].[Hours]} ON COLUMNS, [Time Type].[Type].Members ON ROWS 
FROM [cube]
WHERE {[Date].[Date ISO].[2013-01-26]:[Date].[Date ISO].[2013-06-25]}

这适用于成员切片器:

SELECT {[Measures].[Hours]} ON COLUMNS, [Time Type].[Type].Members ON ROWS 
FROM [cube]
WHERE [Time Type].[Allocation Type].[Direct]

如何同时通过这两个WHERE子句值约束结果?我试过将它们放在同一位置，就像这样:

How do I constrain the results by both of these WHERE clause values at the same time? I've tried putting them both in the same WHERE like so:

SELECT {[Measures].[Hours]} ON COLUMNS, [Time Type].[Type].Members ON ROWS 
FROM [cube]
WHERE ([Time Type].[Allocation Type].[Direct],
    {[Date].[Date ISO].[2013-01-26]:[Date].[Date ISO].[2013-06-25]})

但是Mondrian答复:没有函数匹配签名(，).

but Mondrian replies with: No function matches signature (, ).

请注意，Mondrian不支持子查询，否则我会这样做.

Note that Mondrian does not support subqueries or I would do it that way.

我认为我可能需要对成员约束在行上使用过滤器功能，但我需要对不想显示的内容进行过滤，我不确定该怎么做.

I think I might need to use a filter function on my rows for my member constraint but I need to filter on something that I don't want to display, which I am not sure how to do.

推荐答案

我认为交叉连接就是这样的答案:

I think crossjoin is the answer like so:

SELECT {[Measures].[Hours]} ON COLUMNS, [Time Type].[Type].Members ON ROWS 
FROM [cube]
WHERE CROSSJOIN([Time Type].[Allocation Type].[Direct],
    {[Date].[Date ISO].[2013-01-26]:[Date].[Date ISO].[2013-06-25]})

CROSSJOIN创建直接"成员和我范围内的日期的所有组合作为WHERE切片器的元组.我认为这是正确的答案.

CROSSJOIN creates all the combinations of the 'Direct" member and the dates in my range as tuples for my WHERE slicer. I think this is the right answer.

这篇关于如何编写按日期范围和维度成员值切片的mdx查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！