Jigsaw Blog

20

Jul
2011
0 comments

Cube Calculations of Current Date, Month, Quarter and Year

If you've struggled like me to define BI reports that contain default values for the Year, Month, Date then the following cube calculations might help. Just use these calculations as the selection when the report is defined. Then each time you open the report you'll be referencing the current Year and Month and not the ones you selected when the report was defined.

CREATE MEMBER CURRENTCUBE.[Posting Period].[Date].[All].CurrentDate
AS STRTOMEMBER("[Posting Period].[Date].&[" + Format(now(), "yyyy-MM-dd")+ "T00:00:00]"),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Posting Period].[Week].[All].CurrentWeek
AS STRTOMEMBER("[Posting Period].[Week].&[" + CSTR(DATEPART("ww",now())) + "]"),
VISIBLE = 1  ;

 

CREATE MEMBER CURRENTCUBE.[Posting Period].[Period].[All].CurrentMonth
AS STRTOMEMBER("[Posting Period].[Period].&[" + Format(now(), "MM")+ "]"),
VISIBLE = 1  ;

CREATE MEMBER CURRENTCUBE.[Posting Period].[Quarter Name].[All].CurrentQuarter
AS STRTOMEMBER("[Posting Period].[Quarter Name].&[Q" + CStr((int(month(now())-1)/3)+1) + "]"),
VISIBLE = 1  ;

CREATE MEMBER CURRENTCUBE.[Posting Period].[Year].[All].CurrentYear
AS STRTOMEMBER("[Posting Period].[Year].&[" + Format(now(), "yyyy")+ "]"),
VISIBLE = 1  ;

Adjust the Hierarhcy names like [Posting Period].[Period] [to suit your installation.

The above calculations assume your using a January - December time frame. If you aren't you'll need to replace

now()

With

DateAdd("m",x,now())

Where x is the month offset you need.

For example if you run a July - June year use

DateAdd("m",6,now())