在计算成员中使用EXCEPT [英] Using EXCEPT in Calculated member
问题描述
我在MDX查询中有以下计算得出的成员:
I have within an MDX query the following calculated member:
MEMBER [Asset].[Class].[Fixed Income Derivatives (Inflation Linked)]
AS
(
[Asset].[Class].&[Fixed Income],
[Asset].[Sub Class].&[Derivatives],
[Asset].[Sub Class Type].&[Inflation]
)
这在查询中使用如下:
SELECT
{
[Measures].[Market Value]
} ON 0,
NON EMPTY(
{
[Asset].[Class].[Fixed Income Derivatives (Inflation Linked)]
} ON 1
FROM [Asset]
这很好用,当然可以给我所有通货膨胀挂钩的固定收益衍生品的市场价值.
This works fine, and of course gives me the market value of all Inflation linked Fixed income derivatives.
我现在尝试添加第二个计算所得成员,这一次是给我所有与通货膨胀相关的固定收益衍生品.我虽然像第二个成员中的EXCEPT
一样简单:
I now try to add a second calculated member, this time to give me all fixed income derviatives which are NOT inflation linked. I though this would be as easy as an EXCEPT
in a second member like so:
MEMBER [Asset].[Class].[Fixed Income Derivatives (Non Inflation Linked)]
AS
(
[Asset].[Class].&[Fixed Income],
[Asset].[Sub Class].&[Derivatives],
EXCEPT(
[Asset].[Sub Class Type].[Sub Class Type],
[Asset].[Sub Class Type].&[Inflation]
)
)
A,不走运-它会产生错误
Alas, no luck - it yields the error
该函数需要一个字符串或数字表达式作为 争论.元组集表达式为 使用.
The function expects a string or numeric expression for the argument. A tuple set expression was used.
这是一个令人困惑的消息,但与SSAS中的课程相当.我肯定自己走在正确的轨道上,只是EXCEPT稍微有错,但是我无法终生发现问题.
Which is a confusing message, but par for the course in SSAS. Im sure im on the right track, and just have the EXCEPT slightly wrong, but I cant for the life of me spot the problem.
推荐答案
您只需要使用Aggregate函数即可实现目标,而不会出错.
You just need to use Aggregate function to achieve your goal without getting an error.
如下更改您的计算成员定义;
Change your calculated member definition as below;
MEMBER [Asset].[Class].[Fixed Income Derivatives (Non Inflation Linked)]
AS
(
Aggregate
(
{[Asset].[Class].&[Fixed Income]} *
{[Asset].[Sub Class].&[Derivatives]} *
EXCEPT
(
[Asset].[Sub Class Type].[Sub Class Type],
[Asset].[Sub Class Type].&[Inflation]
)
,Measures.CurrentMember
)
)
这篇关于在计算成员中使用EXCEPT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!