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?
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 lastPeriods(#'[Cube].[Dim].[Hier].[Level]->['+timestampMask($current_timestamp)+']'#,3) will resolve to 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.
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 (http://www.cognoise.com/community/index.php/topic,18355.msg53835.html#msg53835). 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.
Ah! He posted while I was replying! Top bloke that Paul!! :)