Default prompt values in Report Studio

I was playing around with some JavaScript today, getting a default value selected in a time prompt. This was a Report Studio report with TM1 cube as a data source. Now we all know how static the default selection in Cognos is... I would never even consider opening the report definition every month to make sure it is pushed ahead one month... So lets take a look into how a real selection of a custom default value could be achieved.

The data source has always full current year of months in it. So no matter where we are in the year, we will always see at least twelve months.

The normal user of this report will always select previous full month (throughout October they look at September). There was no way to use relative time because... ah... long story which you've all heard before- we want the freedom to choose blablabla... So, I figured that there has to be a way to select the 99% of cases for the user.

A quick search leads to IBMs page with a suggestion on how to select a number x item in the list prompt. OK, better already, but I don't know which one it would be. So lets incorporate the date and month features into their JavaScript and here is what we get:
</span>
<script>

var theSpan = document.getElementById("SpanDate");
var theSelect = theSpan.getElementsByTagName("select");

var dd = new Date();
dd.setMonth(dd.getMonth(), 0);
var nd = dd.getMonth();

var x = -(nd - 11);

theSelect[0].options[x].selected=true;
</script>
 

This is the second part of the HTML injection to the prompt page (the one on the right of our prompt). The one on the left is just the span tag opening and contains only this line of code:
<span id="SpanDate">

This is how your prompt should look like in the Report Studio. The first HTML Item starts the span. Second one, containing the big chunk of code, applies the selection to the correct prompt item.

Here is the translation of the above code to human language:

Close span tag
Start script

Apply these changes to SpanDate item

Get current date;
Subtract one month from the date;
Tell me the value of the month you got; At this point the script returns a value from 0 (Jan) to 11(Dec). My results in the prompt are from Dec to Jan. And the first item in the list has 0 as identifier. So I basically need to change the month value inside-out. That is what -(nd-11) does. Subtract 11 from the current month value and multiply by -1. So in this case while we are in Sept, our formula is (9-11)*(-1) = 3

The next to last row says select option number x (3 in my example) and 4th item from tom (Sept) is selected (because first row id is 0).

And we finally end the script.

If you would like the script to select current month instead, just skip the row with dd.setMonth(dd.getMonth(), 0);

Easy! Wasn't it? These two quick HTML Items arround a prompt in Report Studio will definitely save my users a ton of clicks every time they runt this report!
M.

P.S. It seems that a JavaScript API for prompts has been implemented in the latest version 10.2 of Cognos Report Studio. Long time overdue; sounds promissing; hope it delivers!

4 comments:

Anonymous said...

Very nice info! Saved my ass with my boss who wanted this prompt!

Anonymous said...

I have a similar requirement. Instead of 201209 I have 2012/Sep? How can achieve this now?

M said...

If your months are ordered in the same way as in my example, then the script will just work. It is not really looking into the values of each item in the prompt. It just finds the item number x in the prompt list and selects that item, no matter what the value is.

M.

Anonymous said...

How can I default only CURRENT YEAR?
Thank you!

Post a Comment