How to create and have a current start date or period on pcMobile?


The solution is to get the correct default value on the slicer, it can be accomplished in 3 ways.

1. The code in the slicer (MDX script using VB script) is set to today's date .

2. The slicer is set to show default value for the dimension and this is defined by code in the cube (MDX script using VB script) .

3. You can use MDX script in the cube defines a named set and then refer to this as default value in the slicer.



In the example below, we use No.3 (not for today's date) but for the last closed month (from the 16th, it's a new month as below which is previous month)

Example of the MDX script code


// Current Month

CREATE SESSION STATIC SET [Current_Month] AS

{

StrToMember("[Time].[Time].[" + Format(now(),"yyyyMM") +"]")

};

// Last 12 Months until Current Month

CREATE SESSION STATIC SET [Last_12_Months] AS

{

LastPeriods(12, [Current_Month].Item(0).Item(0))

};

// Last Closed Month

CREATE SESSION STATIC SET [Last_Closed_Month] AS

{

StrToMember("[Time].[Time].[" + Format(dateadd("d",-45,now()),"yyyyMM") +"]")

};

// Last 12 Months until Last Closed Month

CREATE SESSION STATIC SET [Last_12_Closed_Months] AS

{

LastPeriods(12, [Last_Closed_Month].Item(0).Item(0))

};

Filter([Time].[Month].Members, [Time].CurrentMember.Properties("Closed") = "-1")


NB1: Date formatting: Format(dateadd("d",-45,now()),"yyyyMM") +"]") it has to be defined as the time member is defined (in our example 201502 gives Feb 2015)

And in the slicer (Select Month Slicer) is set to initially show “Last_Closed_Month mha” “Initial Selection” under the tab “Slicer settings”

NB2: Filter([Time].[Month].Members, [Time].CurrentMember.Properties("Closed") = "-1")  It means that it will read a property in the Time dimension called Closed and if it is ticked =-1, then it should show that