One of the requirements that I have often encountered is a dynamic year prompt. The user would like a list of the past n years, defaulting to a specific one (the previous year for instance). Cognos does not have a built-in way of dynamically setting a default value on a prompt. There is a way around this though.

To start go to the query that needs to be filtered. In this example I’ll be using a relational source.

The filter line should be

[ns].[Time].[Year] = #prompt('Year','integer','year(getDate))-1')#

The prompt macro will automatically insert the default year into the filter, in this case the current year – 1. Note that the year(getDate()) is T-SQL, PL/SQL users should using something like to_char(add_months(sysdate, -12),’YYYY’).

If there is no value returned the parameter on runtime the filter will default to the previous year. This works well, but the user wants the year selected on the value prompt.

Add some fields to the report page to test it.

For that we’ll need the prompt on the page. Create the value prompt, give it the parameter name Year, and click finish. Set the name of the prompt to Year. Set Auto-Submit to Yes. Add static choices 1 – 5.

The next step is to change the numbers into years, and to automatically select the previous year.

Add an HTML item to the right of the prompt:

<script>
var form = getFormWarpRequest();
var YearList = form._oLstChoicesYear;
var i = 0;
d=new Date();
year = d.getYear()
while (i <= 4)
{
YearList.options[i+2].text=year - i;
YearList.options[i+2].value=year - i;
i++;}
YearList.remove(0);
YearList.remove(0);
YearList.options[1].selected=true;
YearList.removeAttribute("hasLabel");
</script>

Note that the while statement loops 5 times. If you want more than 5 options increase the number of loops accordingly.

If it works the report will look like: It works except for one small issue. When the user selects another year, the report is correct but the Javascript forces the year in the prompt back to the default selection!

The best way to solve this is to set the HTML Source Variable with a Boolean variable.

Use the report expression

ParamValue('Year') is not null

Select the new variable from the Condition Explorer and click on the HTML Item. If it is not already, set the HTML Source Variable to the variable that was just created. Set the Source Type to Report Expression and paste in the following code:

'<script>
var form = getFormWarpRequest();
var YearList = form._oLstChoicesYear;
var i = 0;
d=new Date();
year = d.getYear()
while (i <= 4)
{
YearList.options[i+2].text=year - i;
YearList.options[i+2].value=year - i;
i++;}
YearList.remove(0);
YearList.remove(0);
YearList.removeAttribute("hasLabel");
YearList.options[' + number2string(extract("year",date2timestamp  (Today()))-string2int32(ParamValue('Year'))) + '].selected = true;
</script> '

Now when no year is selected it will default to the previous year, and select the second value in the prompt. When a year is selected, it will find the number of years between that year and current year (extract(“year”,date2timestamp (Today()))-string2int32(ParamValue(‘Year’))), and will select the year at that index. Some locales use a semi-colon instead of a comma.