MDX查询有效，但忽略EXCEPT子句 [英] MDX query works but ignores the EXCEPT clause

问题描述

我一直在研究自定义dll(通过自定义xll/Excel插件调用)来构造MDX并返回2D数据.

I have been working on a custom dll (that is called via a custom xll / Excel Addin) to construct MDX and return 2D data.

它工作得很好，我刚刚研究了如何添加使用EXCEPT发送排除列表的功能.

It's working nicely and I just went to work out how I add the ability to send in an exclusion list using EXCEPT.

我建立了一个带过滤的查询，该查询有效，只是它忽略了EXCEPT.任何比我多的MDX(我在哈哈大约2个月了:)都知道为什么?

I built up a query with filtering and this query works except it ignores the EXCEPT. Anyone with more MDX than me (I'm about 2 months in haha :)) know why?

谢谢

Leigh

WITH  
Member  [Measures].[Book_Label] AS [Book].[Book].CURRENTMEMBER.MEMBER_CAPTION 
Member  [Measures].[Isin_Label] AS [Isin].[Isin].CURRENTMEMBER.MEMBER_CAPTION 
SELECT 
NON EMPTY 
{[Measures].[Book_Label],[Measures].[Isin_Label],[Measures].[Notional.SUM]} 
ON COLUMNS, 
NON EMPTY ORDER
(
EXCEPT(
FILTER(
([Book].CHILDREN,[Isin].CHILDREN), 
([Book].[Book].CURRENTMEMBER.MEMBER_CAPTION = "ALGO1")
), 
[Isin].[Isin].[DE0001104776]),
[Notional.SUM]
,
BASC) 
ON ROWS 
FROM[TraderCube] 
WHERE ([Date].[Date].[2019-11-18])

推荐答案

我返回尝试将当前正在使用的1..n FILTER构建器与EXCEPT结合使用(业务要求).不幸的是，尽管查询通过了语法检查并执行了，但正如原始文章中所报告的那样，多维数据集/服务器忽略了它.

I returned to trying out combining my currently working 1..n FILTER builder in conjunction with an EXCEPT (requested by business). Unfortunately, despite the query passing syntax check and executing, as reported in original post the cube/server ignores it.

我刚尝试在我的FILTER中添加一个<>，它起作用了！ :)

I just tried adding a <> to my FILTER and it worked! :)

这是一个例子.

WITH  
  Member  [Measures].[Book_Label] AS [Book].[Book].CURRENTMEMBER.MEMBER_CAPTION 
  Member  [Measures].[Isin_Label] AS [Isin].[Isin].CURRENTMEMBER.MEMBER_CAPTION 
SELECT 
NON EMPTY {[Measures].[Book_Label],[Measures].[Isin_Label],[Measures].[Notional.SUM]} 
ON COLUMNS, 
NON EMPTY 
ORDER( 
FILTER(
([Book].CHILDREN,[Isin].CHILDREN), 
(([Book].[Book].CURRENTMEMBER.MEMBER_CAPTION = \"ALGO1\") AND 
([Isin].[Isin].CURRENTMEMBER.MEMBER_CAPTION <> \"DE0001102309\"))
),[Notional.SUM],
BASC) 
ON ROWS 
FROM[TraderCube] 
WHERE([Date].[Date].[2019-11-21])

这篇关于MDX查询有效，但忽略EXCEPT子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！