如何始终查找计算所得成员的父代 [英] How to always find the parent of a calculated member

问题描述

这似乎很合逻辑:

WITH 
MEMBER [Date].[Date - Calendar Month].[Calendar Year].&[2013].[LastMth] AS
    IIF(
       Day(Now()) = 1,
       TAIL([Date].[Date - Calendar Month].[Calendar Month],2).Item(1),
       TAIL([Date].[Date - Calendar Month].[Calendar Month],2).Item(0)
       )
SELECT
    NON EMPTY 
    {
        [Date].[Date - Calendar Month].[LastMth],
        [Date].[Date - Calendar Month].[LastMth].PARENT
    }
    ON COLUMNS,
    NON EMPTY 
    [Measures].[BillableIncome]
    ON ROWS 
FROM [ourCube]

我在脚本的第一行中指定了计算成员的父对象-因此我的成员LastMth的父对象为2013，并确保结果集可以返回2013和最后一个月的总数.

如果我在第一行中将其设为父级2012:

 WITH 
    MEMBER [Date].[Date - Calendar Month].[Calendar Year].&[2012].[LastMth] AS
        IIF(
...
...

...不必担心我从(2013)那里得到LastMth，并按照指示将2012设为父级-有点怪异但可以理解的行为.

以下内容为父级返回一个空集-我以为父级将为[Date].[Date - Calendar Month].[(All)]?什么是父母?我该如何彰显其价值?

WITH 
MEMBER [Date].[Date - Calendar Month].[LastMth] AS
    IIF(
       Day(Now()) = 1,
       TAIL([Date].[Date - Calendar Month].[Calendar Month],2).Item(1),
       TAIL([Date].[Date - Calendar Month].[Calendar Month],2).Item(0)
       )
SELECT
    NON EMPTY 
    {
        [Date].[Date - Calendar Month].[LastMth],
        [Date].[Date - Calendar Month].[LastMth].PARENT
    }
    ON COLUMNS,
    NON EMPTY 
        [Measures].[BillableIncome]
    ON ROWS 
FROM [ourCube]

解决方案

如果您未为计算所得成员声明父项，则它将获得All成员的同级，而不是子项.

顺便说一句:我发现大多数客户端工具-包括Excel-都不希望All有兄弟姐妹，因此如果您create在计算脚本中是计算成员(而不是仅在计算脚本中定义它). WITH语句的子句)，即使指示它们显示计算所得的成员，也不要显示它们.

This seems pretty logical:

WITH 
MEMBER [Date].[Date - Calendar Month].[Calendar Year].&[2013].[LastMth] AS
    IIF(
       Day(Now()) = 1,
       TAIL([Date].[Date - Calendar Month].[Calendar Month],2).Item(1),
       TAIL([Date].[Date - Calendar Month].[Calendar Month],2).Item(0)
       )
SELECT
    NON EMPTY 
    {
        [Date].[Date - Calendar Month].[LastMth],
        [Date].[Date - Calendar Month].[LastMth].PARENT
    }
    ON COLUMNS,
    NON EMPTY 
    [Measures].[BillableIncome]
    ON ROWS 
FROM [ourCube]

I specify the parent of the calculated member in the first line of the script - so my member LastMth has 2013 as it's parent and sure enough the result set throws back the total for 2013 as well as the last month.

If I make it's parent 2012 in the first line:

 WITH 
    MEMBER [Date].[Date - Calendar Month].[Calendar Year].&[2012].[LastMth] AS
        IIF(
...
...

...it doesn't worry about where I get LastMth from (2013) and makes 2012 it's parent as instructed - slightly bizarre but understandable behaviour.

The following returns an empty set for the parent - I thought the parent would be [Date].[Date - Calendar Month].[(All)]? What is the parent and how can I expose it's value?

WITH 
MEMBER [Date].[Date - Calendar Month].[LastMth] AS
    IIF(
       Day(Now()) = 1,
       TAIL([Date].[Date - Calendar Month].[Calendar Month],2).Item(1),
       TAIL([Date].[Date - Calendar Month].[Calendar Month],2).Item(0)
       )
SELECT
    NON EMPTY 
    {
        [Date].[Date - Calendar Month].[LastMth],
        [Date].[Date - Calendar Month].[LastMth].PARENT
    }
    ON COLUMNS,
    NON EMPTY 
        [Measures].[BillableIncome]
    ON ROWS 
FROM [ourCube]

解决方案

If you do not state a parent for a calculated member, it gets a sibling of the All member, not a child of it.

As an aside: I found that most client tools - including Excel - do not expect All to have siblings, and thus if you create a calculated member in the calculation script (as opposed to just defining it in the WITH clause of a statement), do not display these, even if they are instructed to show calculated members.

这篇关于如何始终查找计算所得成员的父代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！