我在多维数据集中有两个维,分别称为[Session Length]
和[Date]
,一个度量[Count - Logins]
。该[Session Length]
维度包含一个名为的属性,[Session Length]
其成员是从0
到的整数240
。
我想编写一个MDX查询,该查询将[Count - Logins]
在[Session Length]
维度的自定义子集上聚合(即,我想基于[Session Length]
维度创建一个自定义集,并对该自定义集的各个成员的计数进行聚合)。这是到目前为止我提出的查询,但是不幸的是我不知道如何前进:
WITH SET [Description] AS {
[SessionLength].[Session Length].&[0], //Glimpse
[SessionLength].[Session Length].&[1]:[SessionLength].[Session Length].&[5], //Short
[SessionLength].[Session Length].&[6]:[SessionLength].[Session Length].&[30], //Medium
[SessionLength].[Session Length].&[31]:[SessionLength].[Session Length].&[90], //Long
[SessionLength].[Session Length].&[90]:[SessionLength].[Session Length].&[240]} //Extended
MEMBER [SessionLength].[Session Length].SessionDescription AS
Aggregate([Description])
SELECT
{ [Measures].[Count - Logins] }
ON COLUMNS,
NONEMPTY({[SessionLength].[Session Length].SessionDescription} * {[Date].[Date].[Date]}) ON ROWS
FROM MyCube
带有以下示例结果集:
Session Length | Date | Count - Logins
-------------------------------------------------
SessionDescription | 2014-02-01 | 22
SessionDescription | 2014-02-01 | 17
如您所见,计数是整个集合的总和,而不是每个成员的总和。这是我希望产生的结果:
Session Length | Date | Count - Logins
-------------------------------------------------
Glimpse | 2014-02-01 | 3
Short | 2014-02-01 | 4
Medium | 2014-02-01 | 9
Long | 2014-02-01 | 5
Extended | 2014-02-01 | 1
Glimpse | 2014-02-02 | 2
Short | 2014-02-02 | 5
Medium | 2014-02-02 | 7
Long | 2014-02-02 | 2
Extended | 2014-02-02 | 1
任何帮助,将不胜感激。我知道可以通过修改DSV来实现,但我不想更改多维数据集的结构。
SessionDescription
如果要在行上查看单独的条目,则必须创建单独的成员,例如:
WITH
MEMBER [SessionLength].[Session Length].Glimpse AS
Aggregate([SessionLength].[Session Length].&[0])
MEMBER [SessionLength].[Session Length].Short AS
Aggregate([SessionLength].[Session Length].&[1]:[SessionLength].[Session Length].&[5])
MEMBER [SessionLength].[Session Length].Medium AS
Aggregate([SessionLength].[Session Length].&[6]:[SessionLength].[Session Length].&[30])
MEMBER [SessionLength].[Session Length].Long AS
Aggregate([SessionLength].[Session Length].&[31]:[SessionLength].[Session Length].&[90])
MEMBER [SessionLength].[Session Length].Extended AS
Aggregate([SessionLength].[Session Length].&[90]:[SessionLength].[Session Length].&[240])
SELECT
{ [Measures].[Count - Logins] }
ON COLUMNS,
NONEMPTY({
[SessionLength].[Session Length].Glimpse,
[SessionLength].[Session Length].Short,
[SessionLength].[Session Length].Medium,
[SessionLength].[Session Length].Long,
[SessionLength].[Session Length].Extended
}
* {[Date].[Date].[Date]})
ON ROWS
FROM MyCube
顺便说一句,我离开了90
在这两个成员,Long
并且Extended
因为它是在你的原始查询。如果不想重复计算,则应将它们从中删除。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句