事实级别的 MDX 过滤

发布于 2024-08-24 07:48:52 字数 667 浏览 11 评论 0原文

我是 mdx sintaxys 的新手。

我有下一个要求，如果可能的话，我需要能够使用 mdx 解决它。我需要显示金额值大于“X”的销售交易数量、金额值小于“Y”的销售交易数量、金额值大于“Z”的信用交易数量。等等。我的多维数据集有一个名为“amount”的度量，其中包含聚合函数“sum”和带有聚合函数“count”的 transactionNumber 以及时间维度、transactionType 维度等。

问题是，X、Y 和 Z 是动态值并由用户配置，我需要读取这些值、构建查询并通过 xmla 执行它。

我正在等待结果集作为下一个结果集，

                  Greater than > 200 USD       less than < 0.10       total

          SALE            150                         10               300
          CREDIT          200                         30               600
          VODI            10                           2                60

如果您能为我提供任何帮助，我将不胜感激

原文

im pretty newbie at mdx sintaxys.

I have the next requirement that i need to be able to solve it using mdx, if its possible. I need to show the number of SALE transactions which amout value is greater than "X" , the number of SALE transactions which amount value is less than "Y" , the number of CREDIT transactions which amout value is greater than "Z". and so on. my cube has a measure called "amount" with a aggregate function "sum" and transactionNumber with a agregate function "count" and a time dimesion, transactionType dimension and others.

the thing is, that X, Y and Z are dynamics values and configured by users, i need to read those values, build the query and execute it vía xmla.

I'm wating a resultset as the next one

                  Greater than > 200 USD       less than < 0.10       total

          SALE            150                         10               300
          CREDIT          200                         30               600
          VODI            10                           2                60

any help you can provide me, i'll appreciate it

分享到QQ

分享到微博

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

发布评论

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

弃爱 2024-08-31 07:48:52

只有当您具有交易级别的属性时，这才可能实现，否则您的度量将被预先聚合到更高的级别。

如果您确实有 [Transaction ID] 属性之类的内容，则可以编写如下查询。

WITH 
  MEMBER Measures.[Greater than 200 USD] as 
    SUM(Filter([Transaction ID].[Transaction ID].[Transaction ID], Measures.Amount > 200)
       , Measures.Count)
  MEMBER Measures.[Less than 0.10 USD] as 
    SUM(Filter([Transaction ID].[Transaction ID].[Transaction ID], Measures.Amount > 200)
       , Measures.Count)
  MEMBER Measures.Total as Measures.Count
SELECT
  {Measures.[Greater than 200 USD]
    ,Measures.[Less than 0.10 USD]
    ,Measures.[Total]} ON columns
 , [Transaction Type].[Transaction Type].[Transaction Type] ON Rows
FROM <Cube>

This would only be possible if you had an attribute that was at the transaction level, otherwise your measures will be pre-aggregated to a higher level.

If you did have something like a [Transaction ID] attribute, you could write a query like the following.

WITH 
  MEMBER Measures.[Greater than 200 USD] as 
    SUM(Filter([Transaction ID].[Transaction ID].[Transaction ID], Measures.Amount > 200)
       , Measures.Count)
  MEMBER Measures.[Less than 0.10 USD] as 
    SUM(Filter([Transaction ID].[Transaction ID].[Transaction ID], Measures.Amount > 200)
       , Measures.Count)
  MEMBER Measures.Total as Measures.Count
SELECT
  {Measures.[Greater than 200 USD]
    ,Measures.[Less than 0.10 USD]
    ,Measures.[Total]} ON columns
 , [Transaction Type].[Transaction Type].[Transaction Type] ON Rows
FROM <Cube>

回复收藏 0 原文

~没有更多了~