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.
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:
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;
This works as expected on the Calendar
hierarchy:
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:
Changing the destination Hierarchy of the Computed Member. Changing CREATE MEMBER CURRENTCUBE.[Completion Date].[Calendar].[All].[Last 30 Days]
to CREATE MEMBER CURRENTCUBE.[Completion Date].[Time Period].[All].[Last 30 Days]
.
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) Add dummy attribute for unfiltered values with the name 'All Time' (key is int with 0 value)
2) Add 3 empty members
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) 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):