如何在查询中将两个成员合并为一个?

问题描述

在行上，我基本上想选择层次结构中的所有成员，但希望将其中的两个组合为一个.例如，成员将包括A，B和C，因此选择[Group].[Group].members会给我All，A，B和C，但我希望获得All, A,和B&C，其中B和C已合并为一个成员.

On rows, I basically want to select all the members of a hierarchy but would like to combine two of them into one. For example, the members would include A, B, and C, so selecting [Group].[Group].members would give me All, A, B, and C but I would like to get All, A,andB&C, where B and C have been merged into one member.

在查询中可能吗?

我正在使用的数据库存储有关订单运输速度，小包裹，少于卡车装载量和白手套的信息.我想合并小包裹，并且合并少于卡车的货物，这样我就可以在两种运输速度下获得汇总数据.

The database I'm using stores information about the shipping speed of orders, being small parcel, less than truck load, and white glove. I would like to merge small parcel and and less than truck load so I can get aggregate data across the two shipping speeds.

我尝试创建一个计算成员:[Measures].[Not White Glove] as AGGREGATE([Order Product].[Ships Via Group].&[Small Parcel], [Order Product].[Ships Via Group].&[Less than Truck Load])，但是由于我现在有[Order Product].[Ships Via Group].members ON ROWS，所以不确定如何使用它.

I tried creating a calculate member: [Measures].[Not White Glove] as AGGREGATE([Order Product].[Ships Via Group].&[Small Parcel], [Order Product].[Ships Via Group].&[Less than Truck Load]) but am unsure how to then use that as I currently have [Order Product].[Ships Via Group].members ON ROWS.

当我放入([Measures].[Not White Glove], [Order Product].[Ships Via Group].&[White Glove], [Order Product].[Ships Via Group].&[All]) ON ROWS时，出现错误Query (14, 11) The Ships Via Group hierarchy is used more than once in the Crossjoin function.

When I put ([Measures].[Not White Glove], [Order Product].[Ships Via Group].&[White Glove], [Order Product].[Ships Via Group].&[All]) ON ROWS I get the error Query (14, 11) The Ships Via Group hierarchy is used more than once in the Crossjoin function.

有没有更好的方法来解决这个错误/这意味着什么?

Is there a better way to go about this/what does that error mean?

推荐答案

您看到的错误是因为带括号的语法:(a, b, c)定义一个元组，其中a，b和c分别是a中的成员不同的维度.如果您尝试将这些成员结合在一起，则应使用速记:{a, b, c}.

The error you're seeing is because of your syntax with the parentheses: (a, b, c) defines a tuple where a, b, and c are each members from a different dimension. If you're trying to union these members together, you should use the shorthand: {a, b, c}.

现在，可以合并成员了，尽管可能不如您所希望的那样干净和容易.这是一种实现方法的示例，方法是创建一个新成员，然后(通过Except)从层次结构中排除原始成员.

Now, to combine the members is possible, although maybe not as clean and easy as you would want. Here's an example of one way to do it, by creating a new member and then excluding (via Except) the original members from the hierarchy.

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]

结果:

                  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.05

希望可以帮助您走上正确的轨道.

Hope that helps set you on the right track.

这篇关于如何在查询中将两个成员合并为一个?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！