DMR Filtering Dynamically

[wykabryan]

I am using a cube to drive my report. One of the requirements is to filter on current year and the last 2 full years using the year dimension. Below is what the data looks like.

The data set (2017,2016,2015,2014,2013,2012,2011,2010,2009, Other)

I have tried using tail. Tail says to return 2017, 2016,2015. But what I am looking for is 2012, 2011, 2010 (current year and the last 2 full years). How can this be done dynamically because the years will change as we move forward?

[CognosPaul]

Tail will always return the last n members of a set. So if you reference a level, tail will just return the last members from it. You could limit the level with the filter function:

filter([Cube].[Dim].[Hier].[Level],[Measures].[Measure]>0) will return a set where each member has [Measure] > 0. You can wrap that with Tail(filter(...),3) to get the last three members from that set.

Alternatively, you could find the current year directly and use the lastPeriods function to build a set of that plus the two previous members. The expression

Code Select Expand

lastPeriods(#'[Cube].[Dim].[Hier].[Level]->['+timestampMask($current_timestamp)+']'#,3) will resolve to

Code Select Expand

lastPeriods([Cube].[Dim].[Hier].[Level]->[2012],3) returning a set of 2012, 2011, 2010.

Just make sure the members are sorted correctly, or you may end up with something like 2012, 2016, 1995.

[MFGF]

This sounds similar to a request Paul replied to recently (when I was on my iPhone with no access to Cognos).

Take a look at what he suggested here. Although he was discussing things at the day level, the same concepts apply at the year level or within a set. The trick is to get the mun of the current year, then work back from this within your set.

Good luck!

MF.

[MFGF]

Ah! He posted while I was replying! Top bloke that Paul!! :)