Dynamically construct the MUN of the 'Current Month'

[tjohnson3050]

Usually dimensional models will include relative time.  Recently I faced a requirement to efficiently calculate the mun for the prior month using a dimensional model with no relative time.  The model was a Cognos DMR model.

The following function uses a prompt macro to query the current date from the report server, then constructs a member unique name (MUN) for the current month.  This assumes a calendar hierarchy with levels for Year, Quarter and Month.  It also assumes the quarter and month business keys are constructed a certain way (YYYYQ) and (YYYYMM).  The macro function timestampMask can return the year and the month from the current timestamp, and a case statement derives the quarter number. Then the dimensional function for previous member (prevMember) returns the prior month.  The good thing about this method is that the function (including the case statement) current month is resolved quickly before the query is generated.

In the function below, the [Namespace].[CalendarDimension].[CalendarHierarchy].[Month]-> portion would obviously need to be replaced by the appropriate names in your model.

prevMember ([Namespace].[CalendarDimension].[CalendarHierarchy].[Month]->[all].
#sb(timestampMask($current_timestamp,'yyyy'))#.
#sb(timestampMask($current_timestamp,'yyyy')+
case timestampMask($current_timestamp,'mm')
when '01' then '1'
when '02' then '1'
when '03' then '1'
when '04' then '2'
when '05' then '2'
when '06' then '2'
when '07' then '3'
when '08' then '3'
when '09' then '3'
when '10' then '4'
when '11' then '4'
when '12' then '4'
end)#.
#sb(timestampMask($current_timestamp,'yyyy') + timestampMask($current_timestamp,'mm'))#
)

[charon]

Interesting  :D

Ty for sharing tj