Dynamic custom exception in Cognos using jQuery

Update - September 2012

Hi everyone,
I made some changes on the core JavaScript code bellow (on step 5):

• I have added logic according to suggested logic by Nimrod (on comments below).
• Added comments to the JavaScript code.

Also, if you would like to use it on Cognos 10, as written in the comments in the code, replace each appearance of LID=lstJQuery in the JavaScript code with LID=lstJQueryRS. 
Introduction

On this tutorial, I will explain in a step by step way (with screen shots), how to create a simple Cognos report that uses jQuery and jQuery UI to highlight cells in list table using a jQuery UI slider.

Software and version used

I have used Cognos version 8.4.1 with SP2 applied. jQuery v1.4.2 and jQuery UI 1.8.2. The report is done with report studio on the GO Sales package (part of the samples coming with Cognos installation).

Step by step instruction

Step 1 - Installing the jQuery library

The first thing to be done is downloading the jQuery and jQuery UI:

jQuery can be downloaded here:http://code.jquery.com/jquery-1.4.2.min.js

jQuery UI can start the process of download here: http://jqueryui.com/download

After downloading the files I suggest to place them in c8\webcontent\common\jquery folder on Cognos gateway server.

So it will be c8\webcontent\common\jquery\jquery-1.4.2.min.js

and  c8\webcontent\common\jquery\jquery-ui-1.8.2.custom\js\jquery-ui-1.8.2.custom.min.js

Step 2 - Creating simple list report

We will now open the report studio on the GO Sales (query) package, which is located at the: Public Folders > Samples > Models > GO Sales (query) path.
We will create new List report. The first thing to do is give the list a name, so we can find it via jQuery search methods (selectors).
In order to give list name, we will select the List object in the properties pane, and then change the name property to lstJQuery:

  

Now we need to put data items in the list. So we will drag the following items and order (The order of items is important for later usage with jQuery) to the list:

1. Sales (query) namespace -> Order query subject -> Order number data item

2. Sales (query) namespace -> Product query subject -> Product line data item

3. Sales (query) namespace -> Product query subject -> Product type data item

4. Sales (query) namespace -> Product query subject -> Product name data item

5. Sales (query) namespace -> Sales query subject -> Quantity data item:

 

Also, we will change the title of the report to:Test jQuery and jQuery UI.

So the report layout will look like this:

One last thing is to set the query auto group & summarize property to No:

Step 3 - Adding reference to the jQuery files
We now drag HTML item to the report from the insertable objects toolbox and add reference to the jQuery files. So drag the HTML item to be the first object in the page (above the title):

The content of the HTML is:

<link type="text/css" href="/cognos8/common/jQuery/jquery-ui-1.8.2.custom/css/smoothness/jquery-ui-1.8.2.custom.css" rel="Stylesheet" />
<script src="/cognos8/common/jQuery/jquery-1.4.2.js" type="text/javascript"></script>
<script src="/cognos8/common/jQuery/jquery-ui-1.8.2.custom/js/jquery-ui-1.8.2.custom.min.js" type="text/javascript"></script>

I assume that you placed the files in folders as described in Step 1. If you placed the in other folder then change the src property to the correct path. Also if you have different version of jQuery / jQuery UI then change the name of the files to the proper version.


Step 4 - Placing the slider in the report
Now we will create the UI slider vi HTML item. We will put it inside nested block.
So the first step is to drag block from the insertable objects toolbox and put it above the List. Also adjust its horizontal alignment property to Center. 
The second step is to add additional block inside the first block and set its width size to 75% (via size & overflow property in the properties pane).
The last step is to add HTML item from the insertable toolbox objects to the second block.

The content of the HTML is:

<p>
<label for="quantity">Highlight quantities less than (50 increments):</label>
<input type="text" id="quantity" style="border:0; font-weight:bold;" />
</p>
<div id="slider"></div>
<p>
</p>

Step 5 - Adding the core JavaScript to the report
We now create the core HTML item of the report. This will create the slider UI, handle the slide action and highlight cells in the table that match the criteria of the slider value.
So we add HTML item to the footer of the report. We put the core HTML in the bottom, because we would like to manipulate the page after it is fully loaded.
The content of the HTML is:

 <script type="text/javascript">
    jQuery(document).ready(function(){
        var column=5;  //column where quantity value located
        var maxVal=0;  //maximum value in the list (table)
        var minVal=0; //minumum value in the list (table)
        var leap=20; //number of leaps toggles in the slider

        //Getting quantity maximum value from entire exposed (this page) list (table)
        //For Cognos 10.X replace LID=lstJQuery with LID=lstJQueryRS
        jQuery('table[LID=lstJQuery] td:nth-child(' + column + ')').each(function(index) {
          if (Number(jQuery(this).text().replace(/\,/g,""))>= maxVal) {
               maxVal=Number(jQuery(this).text().replace(/\,/g,""));}
        });

        minVal=maxVal;
        //Getting the minimum quantity value from entire exposed (this page) list (table)
        //For Cognos 10.X replace LID=lstJQuery with LID=lstJQueryRS
        jQuery('table[LID=lstJQuery] td:nth-child('+column+')').each(function(index) {
          if (Number(jQuery(this).text().replace(/\,/g,""))<= minVal) {
               minVal=Number(jQuery(this).text().replace(/\,/g,""));}
        });
        minVal=minVal-1;
        //Setting the step size for each slide scroll using the min. and max. numbers
        //and the leap variable defined in the beginning of the function.
        var step=Math.round((maxVal-minVal)/leap);
        //Setting the label according to the value step variable calculated value
        jQuery('label[for=quantity]').text('Highlight quantities less than (leap by ' + step + ' each slide)'); 

 

        //Adding the slider object (jQueryUI)         
        jQuery("#slider").slider({
            value: minVal,
            min: minVal,
            max: maxVal,
            step: step,
            slide: function(event, ui) {
                jQuery("#quantity").val('' + ui.value);

                //Setting the border of quantity value if it is less than the
                //value of the slider to bold red
                //For Cognos 10.X replace LID=lstJQuery with LID=lstJQueryRS
                jQuery('table[LID=lstJQuery] td:nth-child(' + column + ')').each(function(index) {
                if (Number(jQuery(this).text().replace(/\,/g,""))<= ui.value)

                  {jQuery(this).css("border", "5px solid red");}
                else {
                    if (jQuery(this).attr('style')) {
                      //If value of the quantity is greater than the value in the slider
                      //remove style attribute from object
                      jQuery(this).removeAttr('style');}
                        }
                 });
            }
        });
        jQuery("#quantity").val('' + jQuery("#slider").slider("value"));
    });
</script>

I am not going into details about how the HTML / JavaScript works, since this is not jQuery tutorial blog. However, important point that worth mentioning is not to use the $ shortcut of jQuery, because if you run the report inside a portlet within portal, then you will have never ending spinning sand clock. This why I used jQuery() function instead of $() function.


Step 6 - Run the Report
If you did everything as explained, you can run the report. You should see a list of the data, and above the list, there will be a slider that will highlight all the quantity cells that their value is less than the slider value: