如何计算MDX中组的总和？ [英] How do I calculate the sum total of a group in MDX?

问题描述

我需要在SQL Server数据工具中创建一个计算。

I need to create a Calculation in the SQL Server Data Tools.

想象一下，在我的OLAP结构中，我有一个城市和卖方维度，事实上。在城市维度中，我有一个城市，一个州和一个人口，这是一个维度的度量。

Imagine that in my OLAP structure I have a City and a Seller Dimensions, and a fact. And in the City Dimension I have a city, a state, and a Population, and this is a Measure in a Dimension.

我需要总结城市中的人口有销售。但是，我只能说是销售城市，只能显示国家总人口。一个卖家可以在许多城市销售。
示例：

I need to sum the Population in the cities which have sales. But I can't sum only the cities with sales, I can only show the state's total population. One seller can sell in many cities. Example:

我会由卖家John过滤，查询返回：

I'll filter by seller John and the query returns this:

STATE  |   CITY            |     POPULATION 

CA     |   Los Angeles     |     10.000.000
CA     |   San Francisco   |      1.000.000  
CA     |   Sacramento      |      1.000.000   
CA     |   San Diego       |      1.000.000   
CA     |   Bakersfield     |        500.000 

Total                          37.000.000

---

总额应为13.500.000，但对我来说，总和返回37.000.000，这是CA的人口。

---

The sum should return 13.500.000, but for me, the sum returns 37.000.000, which is the population of CA.

我没有高级知识在MDX，我不能创建一个计算的成员与这个上下文，不能提供的例子。

I don't have advanced knowledge in MDX, I can't create a calculated member with this context and can't provide examples.

我只需要返回这个卖家销售的城市人口的总和，但目前正在返还人口的总和，例如，如果我选择显示国家和城市，返回总和如果我选择显示状态和城市，则返回状态总和的总和

In short, I need to return only the sum of the population of the cities in which this seller has sales, but currently is returning the sum total of the population, for example, if I select to show the country and the city, returns the sum total of the country's population, if I select to display the state and the city, returns the sum total of the population of the state

推荐答案

如果您只想显示卖方和城市的总人数，可以使用下面的查询。

If you just want to show the sum of population by seller and cities, you can use something like the query below.

SELECT Measures.Population 
ON 0,
FILTER(Seller.SellerName.CHILDREN * City.City.CHILDREN, Measures.[Sales Amount] > 0)
ON 1
FROM [YourCube]

或

SELECT Measures.Population 
ON 0,
(Seller.SellerName.CHILDREN * City.City.CHILDREN)
HAVING Measures.[Sales Amount] > 0
ON 1
FROM [YourCube]

显然，你必须用多维数据集中的实际尺寸名称替换。

Obviously, you would have to substitute with the actual dimension names from cube.

编辑：

如果你只想要总和在卖家销售的所有城市中，尝试以下代码

//Build a set of cities
with set CitiesForSeller as
exists(City.City.CHILDREN, strtoset('Seller.SellerName.&[SomeName]'), "<<Name of the measure group which has the population measure>>")

//Get the sum of population in all the cities combined
member measures.SumOfPopulation as
sum(CitiesForSeller,Measures.Population)

select measures.SumOfPopulation on 0,
CitiesForSeller 
having measures.SumOfPopulation > 0
on 1
from [YourCube]

这篇关于如何计算MDX中组的总和？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！