MDX：按度量表值过滤成员集

发布于 2024-07-13 04:05:32 字数 1212 浏览 5 评论 0原文

我在事实表中有一些数字，并生成了一个使用 SUM 聚合器来汇总数字的度量。但问题是我只想对大于 10 的数字求和。我尝试在度量定义中使用通用表达式，这当然有效，但问题是我需要能够动态设置该值，因为它并不总是 10，这意味着用户应该能够自己选择它。

更具体地说，我当前的 MDX 如下所示：

WITH
SET [Email Measures] AS '{[Measures].[Number Of Answered Cases], 
[Measures].[Max Expedition Time First In Case], [Measures].[Avg Expedition Times First In Case], 
[Measures].[Number Of Incoming Email Requests], [Measures].[Avg Number Of Emails In Cases],
[Measures].[Avg Expedition Times Total],[Measures].[Number Of Answered Incoming Emails]}' 

SET [Organizations] AS '{[Organization.Id].[860]}' 
SET [Operators] AS '{[Operator.Id].[3379],[Operator.Id].[3181]}'
SET [Email Accounts] AS '{[Email Account.Id].[6]}'
MEMBER [Time.Date].[Date Period] AS Aggregate ({[Time.Date].[2008].[11].[11] :[Time.Date].[2009].[1].[2] }) 
MEMBER [Email.Type].[Email Types] AS Aggregate ({[Email.Type].[0]}) 
SELECT {[Email Measures]} ON columns,
[Operators] ON rows 
FROM [Email_Fact]
WHERE ( [Time.Date].[Date Period] )

现在，相关成员是计算成员 [Avg Expedition Times Total]。该成员采取了两项措施； [总探险次数] 和 [探险次数] 并将其一分为二以获得平均值，所有这些目前都有效。但是，我希望 [Sum Expedition Times] 仅汇总高于或低于我/用户意愿的参数的值。

如何过滤 [Sum Expedition Times] 迭代的数字，而不是过滤该度量最终给我的总和？

原文

I have some numbers in a fact table, and have generated a measure which use the SUM aggregator to summarize the numbers. But the problem is that I only want to sum the numbers that are higher than, say 10. I tried using a generic expression in the measure definition, and that works of course, but the problem is that I need to be able to dynamically set that value, because it's not always 10, meaning users should be able to select it themselves.

More specifically, my current MDX looks like this:

WITH
SET [Email Measures] AS '{[Measures].[Number Of Answered Cases], 
[Measures].[Max Expedition Time First In Case], [Measures].[Avg Expedition Times First In Case], 
[Measures].[Number Of Incoming Email Requests], [Measures].[Avg Number Of Emails In Cases],
[Measures].[Avg Expedition Times Total],[Measures].[Number Of Answered Incoming Emails]}' 

SET [Organizations] AS '{[Organization.Id].[860]}' 
SET [Operators] AS '{[Operator.Id].[3379],[Operator.Id].[3181]}'
SET [Email Accounts] AS '{[Email Account.Id].[6]}'
MEMBER [Time.Date].[Date Period] AS Aggregate ({[Time.Date].[2008].[11].[11] :[Time.Date].[2009].[1].[2] }) 
MEMBER [Email.Type].[Email Types] AS Aggregate ({[Email.Type].[0]}) 
SELECT {[Email Measures]} ON columns,
[Operators] ON rows 
FROM [Email_Fact]
WHERE ( [Time.Date].[Date Period] )

Now, the member in question is the calculated member [Avg Expedition Times Total]. This member takes in two measures; [Sum Expedition Times] and [Nr of Expedition Times] and splits one on the other to get the average, all this presently works. However, I want [Sum Expedition Times] to only summarize values over or under a parameter of my/the user's wish.

How do I filter the numbers [Sum Expedition Times] iterates through, rather than filtering on the sum that the measure gives me in the end?

分享到QQ

分享到微博

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

发布评论

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

蹲墙角沉默 2024-07-20 04:05:32

您可以将成员移动到 MDX 查询中，而不是将其放入多维数据集中。然后你会得到类似的东西......

WITH
MEMBER [Avg Expedition Times Total] AS
SUM(
    FILTER([Your Dimension],
        [Measure you want to filter] > 10),
    [Measure you want to sum])

我不确定你到底想要过滤和求和哪些维度和度量，但我认为这是朝着正确方向迈出的一步。如果您的用户无法修改 MDX（或不想！），那么创建多个度量也是一个非常可靠的解决方案。

You could move the member into the MDX query, instead of putting it in the cube. Then you get something like....

WITH
MEMBER [Avg Expedition Times Total] AS
SUM(
    FILTER([Your Dimension],
        [Measure you want to filter] > 10),
    [Measure you want to sum])

I'm not sure exactly which dimensions and measure you want to filter and sum by, but I think this is a step in the right direction. If your users can't modify the MDX (or don't want to!) then creating multiple measures is a pretty solid solution too.

回复收藏 0 原文