如何在新行标签下调整MDX以分组计算的度量值
如何在新行标签下调整MDX以分组计算的度量值
提问于 2013-09-27 18:59:26
I以下MDX查询日期范围(月份)上的两个度量的一组计算度量：
WITH
MEMBER [Measures].[Prior Visits Office New] as SUM({[Date Post Transaction].[Calendar Month Period].&[201211].Lag(12) : [Date Post Transaction].[Calendar Month Period].&[201306].Lag(12)},[Measures].[Visits Office New]),format_string = '#,##0'
MEMBER [Measures].[Current Visits Office New] as SUM({[Date Post Transaction].[Calendar Month Period].&[201211] : [Date Post Transaction].[Calendar Month Period].&[201306]},[Measures].[Visits Office New]),format_string = '#,##0'
MEMBER [Measures].[Diff Visits Office New] as ([Measures].[Current Visits Office New] - [Measures].[Prior Visits Office New]),format_string = '#,##0'
MEMBER [Measures].[Percent Change Visits Office New] as ([Measures].[Diff Visits Office New] / [Measures].[Prior Visits Office New]),format_string = 'Percent'
MEMBER [Measures].[Prior Visits Office Established] as SUM({[Date Post Transaction].[Calendar Month Period].&[201211].Lag(12) : [Date Post Transaction].[Calendar Month Period].&[201306].Lag(12)},[Measures].[Visits Office Established]),format_string = '#,##0'
MEMBER [Measures].[Current Visits Office Established] as SUM({[Date Post Transaction].[Calendar Month Period].&[201211] : [Date Post Transaction].[Calendar Month Period].&[201306]},[Measures].[Visits Office Established]),format_string = '#,##0'
MEMBER [Measures].[Diff Visits Office Established] as ([Measures].[Current Visits Office Established] - [Measures].[Prior Visits Office Established]),format_string = '#,##0'
MEMBER [Measures].[Percent Change Visits Office Established] as ([Measures].[Diff Visits Office Established] / [Measures].[Prior Visits Office Established]),format_string = 'Percent'
SELECT {[Measures].[Current Visits Office New],[Measures].[Prior Visits Office New],[Measures].[Diff Visits Office New],[Measures].[Percent Change Visits Office New],[Measures].[Current Visits Office Established],[Measures].[Prior Visits Office Established],[Measures].[Diff Visits Office Established],[Measures].[Percent Change Visits Office Established]}
ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Date Post Transaction].[Calendar Month Period].Children})}))
ON ROWS FROM (SELECT ({[Date Post Transaction].[Calendar Month Period].&[201301]})
ON COLUMNS FROM [cube])
WHERE ([Report Group].[Report Group1].&[Group])结果集如下所示：
+-----------------------------+-----------------------------+---------------------------+--------------------------+------------------------------------+-------------------------------------+-----------------------------------+---------------------------------+--------------------------------------------+
| | Current Visits Office New | Prior Visits Office New | Diff Visits Office New | Percent Change Visits Office New | Current Visits Office Established | Prior Visits Office Established | Diff Visits Office Established | Percent Change Visits Office Established |
+-----------------------------+-----------------------------+---------------------------+--------------------------+------------------------------------+-------------------------------------+-----------------------------------+---------------------------------+--------------------------------------------+
| 201301 | 4,793 | 4,307 | 486 | 11.28% | 58,979 | 57,228 | 1,751 | 3.06% |
+-----------------------------+-----------------------------+---------------------------+--------------------------+------------------------------------+-------------------------------------+-----------------------------------+---------------------------------+--------------------------------------------+我只有一个[Date Post Transaction].[Calendar Month Period].&[201301]，它显示行201301，作为填充。它似乎没有返回一个没有行轴标签的结果集。
获取结果集所需的格式如下所示：
+---------------------------------+----------------------+---------------------+-----------------------+----------------------+
| | Current | Prior | Diff | Percent Change |
+---------------------------------+----------------------+---------------------+-----------------------+----------------------+
| Visits Office New | 4,793 | 4,307 | 486 | 11.28% |
+---------------------------------+----------------------+---------------------+-----------------------+----------------------+
| Visits Office Established | 58,979 | 57,228 | 1,751 | 3.06% |
+---------------------------------+----------------------+---------------------+-----------------------+----------------------+甚至更好：
+---------------------------------+----------------------+---------------------+-----------------------+----------------------+
| Visits Office New | 4,793 | 4,307 | 486 | 11.28% |
+---------------------------------+----------------------+---------------------+-----------------------+----------------------+
| Visits Office Established | 58,979 | 57,228 | 1,751 | 3.06% |
+---------------------------------+----------------------+---------------------+-----------------------+----------------------+所以，我在想一些逻辑，可以将计算出来的指标分组如下：
WITH
MEMBER [Measures].[Visits Office New] as (
MEMBER [Measures].[Prior Visits Office New] as SUM({[Date Post Transaction].[Calendar Month Period].&[201211].Lag(12) : [Date Post Transaction].[Calendar Month Period].&[201306].Lag(12)},[Measures].[Visits Office New]),format_string = '#,##0'
MEMBER [Measures].[Current Visits Office New] as SUM({[Date Post Transaction].[Calendar Month Period].&[201211] : [Date Post Transaction].[Calendar Month Period].&[201306]},[Measures].[Visits Office New]),format_string = '#,##0'
MEMBER [Measures].[Diff Visits Office New] as ([Measures].[Current Visits Office New] - [Measures].[Prior Visits Office New]),format_string = '#,##0'
MEMBER [Measures].[Percent Change Visits Office New] as ([Measures].[Diff Visits Office New] / [Measures].[Prior Visits Office New]),format_string = 'Percent'
)
MEMBER [Measures].[Visits Office Established] as (
MEMBER [Measures].[Prior Visits Office Established] as SUM({[Date Post Transaction].[Calendar Month Period].&[201211].Lag(12) : [Date Post Transaction].[Calendar Month Period].&[201306].Lag(12)},[Measures].[Visits Office Established]),format_string = '#,##0'
MEMBER [Measures].[Current Visits Office Established] as SUM({[Date Post Transaction].[Calendar Month Period].&[201211] : [Date Post Transaction].[Calendar Month Period].&[201306]},[Measures].[Visits Office Established]),format_string = '#,##0'
MEMBER [Measures].[Diff Visits Office Established] as ([Measures].[Current Visits Office Established] - [Measures].[Prior Visits Office Established]),format_string = '#,##0'
MEMBER [Measures].[Percent Change Visits Office Established] as ([Measures].[Diff Visits Office Established] / [Measures].[Prior Visits Office Established]),format_string = 'Percent'
)
SELECT {[Measures].[Current Visits Office New],[Measures].[Prior Visits Office New],[Measures].[Diff Visits Office New],[Measures].[Percent Change Visits Office New],[Measures].[Current Visits Office Established],[Measures].[Prior Visits Office Established],[Measures].[Diff Visits Office Established],[Measures].[Percent Change Visits Office Established]}
ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Date Post Transaction].[Calendar Month Period].Children})}))
ON ROWS FROM (SELECT ({[Date Post Transaction].[Calendar Month Period].&[201301]})
ON COLUMNS FROM [cube])
WHERE ([Report Group].[Report Group1].&[Group])但这不管用。有什么想法？
回答 1
Stack Overflow用户
回答已采纳
发布于 2013-10-10 07:24:21
使用查询中迄今尚未使用的任何层次结构(我将在我的示例中使用[Dim1].[Util]，因为我不知道您的多维数据集)，您将在此基础上创建成员--因为您可以在任何层次结构上创建计算成员，而不仅仅是在Measures层次结构上：
WITH member [Dim1].[Util].[Prior] as
SUM({[Date Post Transaction].[Calendar Month Period].&[201211].Lag(12) : [Date Post Transaction].[Calendar Month Period].&[201306].Lag(12)},
[Measures].CurrentMember
),format_string = '#,##0'
member [Dim1].[Util].[Current] as
SUM({[Date Post Transaction].[Calendar Month Period].&[201211] : [Date Post Transaction].[Calendar Month Period].&[201306]},
[Measures].CurrentMember
),format_string = '#,##0'
member [Dim1].[Util].[Diff] as
[Dim1].[Util].[Current] - member [Dim1].[Util].[Prior],format_string = '#,##0'
member [Dim1].[Util].[Percent Change] as
[Dim1].[Util].[Diff] / [Dim1].[Util].[Current],format_string = 'Percent'
SELECT {
[Dim1].[Util].[Prior],
[Dim1].[Util].[Current],
[Dim1].[Util].[Diff],
[Dim1].[Util].[Percent Change]
}
ON COLUMNS ,
{
[Measures].[Visits Office New],
[Measures].[Visits Office Established]
}
ON ROWS
FROM (SELECT ({[Date Post Transaction].[Calendar Month Period].&[201301]})
ON COLUMNS
FROM [cube])
WHERE ([Report Group].[Report Group1].&[Group])对于在列中使用的层次结构没有特定的要求，只是在查询中不应该使用它。一些多维数据集设计器显式地在每个多维数据集中创建一个甚至两个实用程序维度，其中只包含一个虚拟成员，以便能够轻松地编写这样的查询。然后，这些维度仅用于在其上创建计算的成员。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19058163
复制相关文章
相似问题
腾讯云开发者
