AnsweredAssumed Answered

Create drop down list from global variable made in ExecuteSQL

Question asked by Stu412 on Aug 26, 2016
Latest reply on Aug 26, 2016 by jfletch

Hi all

 

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:

 

ExecuteSQL (

"

SELECT Year from DataTable_Home

WHERE

CustomerID_FK = ? and

Period < ?

GROUP BY YearEnd

"

;"";"";

2473;

6

)

 

Question is, how do I use the list which I can see in the Data Viewer as a value list?

 

Thanks

Outcomes