I have quite a specific need at the moment. I need to produce a drop down list of the most recent 5 year's worth of dates (which in turn acts as a filter on a portal). I have older data in the table in question but don't want these dates appearing on the dropdown. I just want the most recent 5.
For example, I'd want to exclude 2010 below (and moving forward, when 2016 comes in, exclude 2010 and 2011 etc):
- 2010 Period 6
- 2011 Period 5
- 2012 Period 4
- 2013 Period 3
- 2014 Period 2
- 2015 Period 1
I've done a little ExecuteSQL to pull this out as below for one unique customer and this produces a lovely list, especially when placed into a $$global variable via a small script:
SELECT Year from DataTable_Home
CustomerID_FK = ? and
Period < ?
GROUP BY YearEnd
Question is, how do I use the list which I can see in the Data Viewer as a value list?