MDX:平均使用 [英] MDX: avg advanced use
问题描述
对于某个问题,我已经达到了基本的MDX知识极限,如果有人有想法,我们将竭诚为您提供帮助
I am reaching the limit of my basic MDX knowledge on a problem, if anyone has an idea, every help will be welcome
情况
这是我要处理的层次结构.在我的fact_table中,我有一个[措施].[销售]措施.
This is the hierarchy I'd like to deal with. In my fact_table I have a [Measures].[Sales] measure.
[All Management].[TemplateMgt].[CityMgt].[DistricMgt].[StoreMgt]
[All Management].[TMP-00.002].[London].[DistricMgt].[Shoe001]
[All Management].[TMP-00.002].[London].[DistricMgt].[Hat001]
[All Management].[TMP-00.002].[London].[DistricMgt].[Electronic001]
[All Management].[TMP-00.002].[Paris].[DistricMgt].[Shoe001]
[All Management].[TMP-00.002].[Paris].[DistricMgt].[Hat001]
[All Management].[TMP-00.002].[Paris].[DistricMgt].[Electronic001]
[All Management].[TMP-00.002].[Madrid].[DistricMgt].[Shoe001]
[All Management].[TMP-00.002].[Madrid].[DistricMgt].[Hat001]
[All Management].[TMP-00.002].[Madrid].[DistricMgt].[Electronic001]
问题
对于给定的CityMgt,我想要三个值
For a given CityMgt, I would like to have three values
[度量].[Cur] :StoreMgt出售给定CityMgt的销售额(因此,在马德里,取值为[Shoe001],[Hat001],[Electronic001])
[Measures].[Cur]: StoreMgt's sales of the given CityMgt (So for Madrid, get the value [Shoe001], [Hat001], [Electronic001])
[Measures].[Avg] :具有相同TemplateMgt AVG([伦敦].[Shoe001] + [Paris].[Shoe001] + [Madrid]的StoreMgt的StoreMgt组的平均销售额) ].[Shoe001])
[Measures].[Avg]: the average sales of StoreMgt group by StoreMgt having the same TemplateMgt AVG([London].[Shoe001] + [Paris].[Shoe001] + [Madrid].[Shoe001])
[Measures].[Max] :具有相同TemplateMgt MAX([London].[Shoe001],[Paris].[Shoe001],[Madrid]的StroreMgt的最大销售价值. [Shoe001])
[Measures].[Max]: the max sales values of StroreMgt having the same TemplateMgt MAX([London].[Shoe001], [Paris].[Shoe001], [Madrid].[Shoe001])
换句话说,我想要一个具有这种结构的输出
In other word, I'd like to have an output that will have this structure
Shoe001 | Hat001 | Electronic001
----------------------------------------------------
CUR|AVG|MAX | CUR|AVG|MAX | CUR|AVG|MAX
----------------------------------------------------
我到目前为止所得到的
WITH MEMBER [Measures].[Cur] AS (...)
MEMBER [Measures].[Avg] AS (...)
MEMBER [Measures].[Max] AS (...)
SELECT {[Measures].[Cur], [Measures].[Avg], [Measures].[Max]} ON COLUMNS,
{FILTER({DESCENDANTS([All Management].CurrentMember, [StoreMgt])}, [All Management].Parent.Parent = "Madrid" } ON ROWS
from [MyCube]
我的问题是我不知道在Member属性Cur/Avg/Max中放入什么,因此我的数据可以按StoreMgt(一种groupby)进行处理
My problem is that I don't know what to put in the Member attributes Cur/Avg/Max so my datas can be treated per StoreMgt (a kind of groupby)
如果任何人都有资格参加,我将不胜感激.
If anyone can enligthenme, I will appreciate.
顺便说一句,
推荐答案
我不确定以下查询是否可以正常工作,希望它能传达出这一想法,
I am not completely sure that following query will work, hope it conveys the idea,
WITH MEMBER [All Management].[Sales_AVG] AS AVG({[All Management].Members},
[Measures].currentMember)
MEMBER [All Management].[Sales_MAX] AS MAX({[All Management].Members},
[Measure].currentMember)
SELECT {[Measures].[Sales]} ON COLUMNS,
{[All Management].Members, [All Management].[Sales_AVG],
[All Management].[sales_Max]} ON ROWS FROM [MYCUBE] WHERE
{DESCENDANTS([All Management].CurrentMember, [StoreMgt])}
这篇关于MDX:平均使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!