创建计算的时间段层次结构 [英] Creating a Calculated Time Periods Hierarchy
问题描述
为了给最终用户更好的多维数据集浏览体验,我试图创建一个由计算所得成员组成的时间段层次结构.
In order to give a nicer Cube browsing experience to end users, I am trying to create a Time Periods hierarchy consisting of Calculated Members.
当前,我已在DSV中使用计算列"来创建一个列,该列的日期"表中的每一行都具有相同的值(值是All Time
).然后在我的Date and Time
维中,使用该计算列"创建了一个单层,单成员层次结构,如下所示:
Currently I have used a Calculated Column in my DSV to create a column with the same value on every row in my Dates table (value is All Time
). Then within my Date and Time
dimension I have created a single level, single member hierarchy using that Calculated Column, which looks like this:
现在我已经成功完成的工作是通过以下计算将时间段添加到我的Calendar
层次结构中:
Now what I have already successfully done is add Time Periods to my Calendar
hierarchy with the following calculation:
CREATE MEMBER CURRENTCUBE.[Completion Date].[Calendar].[All].[Last 30 Days]
AS SUM(LastPeriods(30,StrToMember(
"[Completion Date].[Calendar].[Day]."+
"&["+CStr(Year(Now()))+"]&["+CStr(Month(Now()))+"]&["+CStr(Day(Now()))+"]"
))),VISIBLE = 1;
这在Calendar
层次结构上按预期工作:
This works as expected on the Calendar
hierarchy:
但是我想将它们移到新的Time Periods
层次结构中,以使它们很好地分开.
But I want to move these into the new Time Periods
hierarchy to keep them nicely separated.
到目前为止,我已经尝试通过两种不同的方式来做到这一点:
So far I have tried to do this in two different ways:
-
更改计算成员的目标层次结构.将
CREATE MEMBER CURRENTCUBE.[Completion Date].[Calendar].[All].[Last 30 Days]
更改为CREATE MEMBER CURRENTCUBE.[Completion Date].[Time Period].[All].[Last 30 Days]
.
将Calendar
层次结构上的计算所得成员的可见性更改为VISIBLE = 0
,并在Time Periods
层次结构上创建引用它的第二个计算所得成员:(我尝试过使用和不使用
Changing the Visibility of the Calculated Member on the Calendar
hierarchy to VISIBLE = 0
and creating a second Caculated Member on the Time Periods
hierarchy which references it: (I have tried with and without using the SUM()
function)
CREATE MEMBER CURRENTCUBE.[Completion Date].[Time Period].[All].[Last 30 Days]
AS SUM([Completion Date].[Calendar].[All].[Last 30 Days]),
VISIBLE = 1;
但是这些都不对我有用.所以我的问题是,我该如何完成我想要达到的目标?
But neither of these have worked for me. So my question is, How can I complete what I am trying to achieve?
我的最终目标是创建一个层次结构,用户可以将其拖到数据透视表中并查看以下内容(但要实际计算出时间段):
My end goal is to have a Hierarchy which the user can drag into a pivot table and see the following (but with the Time Periods actually calculated):
推荐答案
我已经用这种方式创建了相同的内容:
I've just created the same this way:
1)为名称为"All Time"的未过滤值添加虚拟属性 (键为整数,值为0)
1) Add dummy attribute for unfiltered values with the name 'All Time' (key is int with 0 value)
2)添加3个空成员
CREATE MEMBER CURRENTCUBE.[Report Date].[Time Period].[All].[Last 30 Days]
AS
null,
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[Report Date].[Time Period].[All].[Last 60 Days]
AS
null,
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[Report Date].[Time Period].[All].[Last 90 Days]
AS
null,
VISIBLE = 1;
3)比添加范围(我还有另一种键格式):
3) Than add scopes (I have another key format):
/* SCOPES */
SCOPE ([Report Date].[Time Period].[All].[Last 30 Days]);
THIS = Sum(LastPeriods(30,StrToMember("[Report Date].[Report Date].[Day].&["+CStr(Format(Now(),"yyyyMMdd"))+"]")));
END SCOPE;
SCOPE ([Report Date].[Time Period].[All].[Last 60 Days]);
THIS = Sum(LastPeriods(60,StrToMember("[Report Date].[Report Date].[Day].&["+CStr(Format(Now(),"yyyyMMdd"))+"]")));
END SCOPE;
SCOPE ([Report Date].[Time Period].[All].[Last 90 Days]);
THIS = Sum(LastPeriods(90,StrToMember("[Report Date].[Report Date].[Day].&["+CStr(Format(Now(),"yyyyMMdd"))+"]")));
END SCOPE;
它有效(也添加了一项措施来对要验证的级别成员进行计数):
It works (also add a measure to count members of a level to validate):
这篇关于创建计算的时间段层次结构的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!