You’ll have to stay with me and really be a sport until I can get a virtual machine going at home with a COGNOS training install on it. Until then, I can’t post screenshots of live data or anything like that. This is more.. Conceptual.

So I’ve setup a crosstab with Years as the column, Sales Category as the row, and Sales Amount as the measure. Pretty simple. What I’d like to do, however, is see if I can somehow manipulate the Years being displayed dynamically without rerunning the report from scratch. That’s where jQuery UI comes in.

I dispatched the necessary jQuery UI CSS files over to IT to place in the webserver where COGNOS is located. They dropped it into the c8/common folder, which resolves to Besides that, I added an HTML Item to the header (at the very very top before the report title), and setup the links to jQuery/jQuery UI.

So here we go. This is a little bit of a hack, but it actually works beautifully in practice. On your crosstab report, you’re going to add two Text Item Value Prompts. Immediately, make sure “Required” is set to No. Name them whatever you would like, just give them descriptive names that you can remember. Don’t have them filter anything. I named mine ?Begin? and ?End?.
Create a filter on the crosstab query. It should be something like:

[sourcecode][Year] between ?Begin? and ?End?[/sourcecode]

Set it to optional (this allows us to run the report without being required to put in values). Drag an HTML item before and after each prompt, and give it a DIV with a unique ID so you can easily select it with jQuery. I named mine #begin and #end. Create a DIV under them with an id of “slider-range”.

Ok, now everything should be ready to go. Take a look at this code:

[sourcecode language=”javascript”]<link type="text/css" href="cognos8/common/css/ui-lightness/jquery-ui-1.8.5.custom.css" rel="Stylesheet" />
<script type="text/javascript"
<script type="text/javascript"
<script type="text/javascript">
jQuery.noConflict(); // Necessary
jQuery(document).ready(function() {
jQuery("#begin").hide(); // Hide the Cognos text prompt that you created
jQuery("#end").hide(); // Hide the other prompt as well
var lastHeader = jQuery("td[uid=15]:first").text(); // From the table header, grab the first Year available (expecting 2002 in my case)
var firstHeader = jQuery("td[uid=15]:last").text(); // Now, take the last Year available (expecting 2011 in my case) – this sets the default values in the slider.
range: true,
min: 2002, // You’re going to have to set these by hand for now
max: 2011, // See above
values: [firstHeader, lastHeader], // Sets the default values as specified above
slide: function(event, ui) {
jQuery("#begin input:text").val(ui.values[0]); // When the slider is moved, it will produce a new Year – put that Year in the hidden text box
jQuery("#end input:text").val(ui.values[1]); // This is the same as the above, but for the second value in the range
setTimeout("promptAction(‘finish’)",3000); // Wait a few seconds, and dispatch the promptAction(‘finish’), which refreshes the page

}); // End Ready

I tested this thoroughly and it worked beautifully for my first few reports. The next steps are to create multiple sliders (both horizontal and vertical). But, that’s essentially how you do it!