在计算成员中使用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屋！