Dynamic Year prompt

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')#

Use a prompt macro to get the default year

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.

Advertisements

2 Responses to Dynamic Year prompt

  1. Yiota says:

    Can you please be more specific on the format of:

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

    what is [ns].[Time].[Year] ?

    The above needs to work on a Query and not on a Data Source?

    I created the filter:

    [Year] = #prompt(‘Year’,’integer’,’year(getDate))-1′)#

    and I get the error msg:

    ‘The response from Report Server is not well formed. No response envelop is received.’

    • cognospaul says:

      The [ns].[Time].[Year] is the full year field from the model. “ns” stands for Name Space. It’s expected to be a numeric value, such as 2010. It could be a calculated field, such as year([ns].[Time].[Date]). It’s also worth mentioning that the SQL I gave as an example is purely Microsoft. The Oracle version would be something like EXTRACT({YEAR} FROM sysdate)

      Ahh… I think I got it. It looks like WordPress very helpfully converted the apostrophe to whatever characters ‘ and ′ are. Replace them with single quotes and it should work. I’ll see if there’s anything I can do to prevent WordPress automatically screwing with my posts in the future.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: