需要你的帮助来解决客户报告中的一个问题。
请检查下面2个mdx查询的结果。
注意:我使用了Union运算符,这样我就可以将2个成员的结果与1合并(法国和德国加在一起)。在Union中使用Exist函数,因为如果第一个集合与第二个集合的维度成员相关,则需要检查函数中的第一个集合。
我使用Union with Exist来实现OR条件。
在第一个MDX查询中,您可以在结果集中找到虚拟集(法国和德国),但在第二个MDX中,您无法找到虚拟集(法国和德国),因为Product Category hierarchy与country和Exist函数相关,检查虚拟集不是国家层次结构的实际成员,因此它将从结果集中删除虚拟集。
有没有什么方法或替代方法可以让我强制exists函数在最终结果(mDX 2)中显示虚拟集?
有没有什么方法或替代方法可以让我强制exists函数在最终结果(mDX 2)中显示虚拟集?
MDX 1)
WITH
SET [Combined] AS {
[Customer].[Customer Geography].[Country].&[France],
[Customer].[Customer Geography].[Country].&[Germany]
}
MEMBER [Customer].[Customer Geography].[France & Germany] AS Aggregate([Combined])
SELECT
[Measures].[Internet Sales Amount] ON 0,
Union(
Except([Customer].[Customer Geography].[Country], [Combined]),
[Customer].[Customer Geography].[France & Germany]
) ON 1
FROM [Adventure Works]结果MDX 1
Internet Sales Amount
Australia $9,061,000.58
Canada $1,977,844.86
United Kingdom $3,391,712.21
United States $9,389,789.51
France & Germany $5,538,330.05MDX查询2)
WITH
SET [Combined] AS {
[Customer].[Customer Geography].[Country].&[France],
[Customer].[Customer Geography].[Country].&[Germany]
}
MEMBER [Customer].[Customer Geography].[France & Germany] AS Aggregate([Combined])
SELECT
[Measures].[Internet Sales Amount] ON 0,
Union( Exists(Except([Customer].[Customer Geography].[Country], [Combined]),
{[Product].[Product Categories].[Category].&[3]}
, "Internet Sales") ,
Exists([Customer].[Customer Geography].[France & Germany],{[Product].[Product Categories].[Category].&[3]}
, "Internet Sales"))
ON 1
FROM [Adventure Works]结果MDX查询2)
Internet Sales Amount
Australia $9,061,000.58
Canada $1,977,844.86
United Kingdom $3,391,712.21
United States $9,389,789.51发布于 2017-08-21 07:06:17
为什么在第二个查询中需要使用exists两次?
WITH
SET [Combined] AS {
[Customer].[Customer Geography].[Country].&[France],
[Customer].[Customer Geography].[Country].&[Germany]
}
MEMBER [Customer].[Customer Geography].[France & Germany] AS Aggregate([Combined])
SELECT
[Measures].[Internet Sales Amount] ON 0,
Union(
Exists(
Except([Customer].[Customer Geography].[Country], [Combined]),
{[Product].[Product Categories].[Category].&[3]}
,"Internet Sales"
)
//,Exists(
// [Customer].[Customer Geography].[France & Germany]
// ,{[Product].[Product Categories].[Category].&[3]}
// ,"Internet Sales"
// )
,[Customer].[Customer Geography].[France & Germany]
)
ON 1
FROM [Adventure Works];https://stackoverflow.com/questions/45770349
复制相似问题