Skip to content →

The UI Grail – jQuery UI Sliders on a Crosstab

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 domain.com/cognos8/common. 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"
src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript"
src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/jquery-ui.min.js"></script>
<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.
jQuery("#slider-range").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
</script>[/sourcecode]

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!

Published in jQuery/Cognos

5 Comments

  1. Ross Hartshorn Ross Hartshorn

    Just wanted to comment for anyone else who reads this that the jQuery.noConflict(); is absolutely necessary. It tripped me up a bit because I got away with not having it in Cognos 8.2, but after we converted to 8.4 it bit me on a few reports.

    Great post!

  2. Love the idea here. A screenshot or two would really be great to see the exact result.

  3. Tom Tom

    This was super helpful, thank you!

  4. Hello Nic,
    Can you please share the XML. I am not able to achieve the same.

  5. Sal Conti Jr Sal Conti Jr

    Yea..I have also been unsuccessful with getting the slider to operate as suggested here. I am using Cognos 10.1 and I am not getting any errors, just not seeing the slider for one, and in other attempts to affect a list or crosstab object, i have not been able to get it to work.

    I wonder if it has to do with the jquery library or version of Cognos…any ideas at all would be greatly appreciated!

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *