There are times when you need to populate a drop-down list with a list of years, for example when asking a user to select the relevant financial year where a full date picker is not appropriate. You have a number of options, such as hard coding the list of years, or using a data source such as CSV or XML file. When you need to build a list of years that can change automatically, one of the easiest ways to do this is by creating a stored procedure in a SQL Server database. This article provides an example stored procedure you can use to implement this.
The below Stored Procedure returns a range of years, with the ability to configure how many years before the current year to include, and how many years into the future to include.
You will need access as a DBA to the target SQL Server database to create the stored procedure, and administrator access to the Infiniti environment to set up a new data source.
CREATE PROCEDURE [dbo].[GetRangeOfYearsFromToday]
@FutureYears int = 0
SET NOCOUNT ON;
WITH yearlist AS
SELECT YEAR(GETDATE()) - @PreviousYears AS Year
SELECT yl.Year + 1 AS Year
FROM yearlist yl
WHEREe yl.Year + 1 <= YEAR(GetDate()) + @FutureYears
SELECT Year FROM yearlist ORDER BY year DESC;
After creating the above stored procedure in your SQL Server database, you need to add it in Infiniti Manage. Configure a Data Source with a connection string pointing to the SQL Server database, and then add a new data object that references the stored procedure. Once configured you can add a data source question to your project in Design and configure the @PreviousYears and @FutureYears key fields to pass in how many years into the past and future are required to be returned.
Example of data source filters to be used within your project:
Example of data source drop down within Produce:
sql tipp return dynamic range years populate drop-down list dataobject
stored proc list year calendar date