如何在MDX查询中获得汇总和？
如何在MDX查询中获得汇总和？
提问于 2014-04-27 05:18:03
我使用SSAS和Adventure Works DW 2008。
这个MDX是什么？：
测量：Reseller Sales Amount
日期: 2014/03/05和
月份= 2014/03年度(总和(01至05天))
和
2014年(森( 01及02) +森(第01至05天))
With
set Serial_Month as
[Date].[Calendar].Currentmember.parent.FIRSTSIBLING
:
[Date].[Calendar].Currentmember.parent
set Serial_Day as
[Date].[Calendar].Currentmember.FIRSTSIBLING
:
[Date].[Calendar].Currentmember
Select
non empty
{
[Date].[Calendar].[Date],
Serial_Day,
Serial_Month
} on columns ,
non empty {[Measures].[Reseller Sales Amount]} on rows
From [Adventure Works]Stack Overflow用户
回答已采纳
发布于 2014-05-01 08:27:05
下面指定一些特定的日期，然后创建一个calculated member。
我不能百分之百确定你需要什么，但是下面的标题是正确的吗？
注意:在我的冒险作品中，我没有和你一样的日期范围。
WITH
SET [SpecificDate] AS
[Date].[Calendar].[Date].&[20080401]
SET [SpecificMonths] AS
{ [Date].[Calendar].[Month].&[2008]&[3]:
[Date].[Calendar].[Month].&[2008]&[7] }
MEMBER [Date].[Calendar].[AggregatedMonths] AS
(
AGGREGATE([SpecificMonths])
)
SET [SpecificYear] AS
[Date].[Calendar].[Calendar Year].&[2008]
MEMBER [Date].[Calendar].[CalcMember] AS
(
[Date].[Calendar].[Date].&[20080401] +
[Date].[Calendar].[Month].&[2008]&[3] +
[Date].[Calendar].[Calendar Year].&[2008]
)
Select
{
[Measures].[Reseller Sales Amount]
} ON COLUMNS,
{
[SpecificDate],
[SpecificMonths],
[Date].[Calendar].[AggregatedMonths],
[SpecificYear],
[Date].[Calendar].[CalcMember]
} ON ROWS
FROM [Adventure Works]如果希望行上的所有日期(带有数据)以及列上的各种度量(如mtd和ytd )，则可以执行以下操作：
With
MEMBER [Measures].[CurrentDay] AS
AGGREGATE(
[Date].[Calendar].Currentmember,
[Measures].[Reseller Sales Amount]
)
MEMBER [Measures].[CurrentMonth] AS
AGGREGATE(
[Date].[Calendar].Currentmember.parent,
[Measures].[Reseller Sales Amount]
)
MEMBER [Measures].[CurrentMTD] AS
AGGREGATE(
MTD([Date].[Calendar].CURRENTMEMBER),
[Measures].[Reseller Sales Amount]
)
MEMBER [Measures].[CurrentYTD] AS
AGGREGATE(
YTD([Date].[Calendar].CURRENTMEMBER),
[Measures].[Reseller Sales Amount]
)
Select
non empty
{
[Measures].[CurrentDay],
[Measures].[CurrentMonth],
[Measures].[CurrentMTD],
[Measures].[CurrentYTD]
}
on columns,
non empty
{
[Date].[Calendar].[Date]
}
having [Measures].[CurrentDay]<>null
on rows
From [Adventure Works]页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/23319695
复制相关文章
相似问题
腾讯云开发者
