Wednesday, October 6, 2010

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:

15 comments:

  1. Great tutorial !.
    Really great to see people pushing the boundaries of Cognos 8 report development with jquery components.

    ReplyDelete
  2. Thanks for the tutorial and thanks for the tip about not using $.

    one question: have you gotten jquery work in a report using a tree-prompt on a prompt-page. The problem comes for me when the user re-runs the report - then the tree-prompt disappears for me.

    ReplyDelete
  3. This is a great tutorial, thanks for posting this.

    One quick question: I am attempting to use this on some of my own data, and everything is working fine except that I have some values that are greater than 1 million, and the slider doesn't seem to recognize these values. It sets the maxVal to whatever the highest value is in my list that is also less than 1 million. Is this just a limitation on the slider, or do I need to set some other option for it to handle these larger values?

    ReplyDelete
  4. zschutz,
    This is not a limitation of slider.
    This is the way i wrote the script.
    You can override it by putting hard-coded value.

    original:
    Query("#slider").slider({
    value: 0,
    min: 0,
    max: maxVal,
    step: 50,

    Modified:
    Query("#slider").slider({
    value: 0,
    min: 0,
    max: 1000000,
    step: 50,

    ReplyDelete
  5. Great post. The advantages of using JQuery in Cognos are many (Including compatibility between upgrades, for example), and this is really well-written, throughly explained and the code is very understandable and straight-fowrard.
    I would like to share some inputs that may upgrade this method for some; mainly these 4 remarks:
    1. The reason this code doesn't work for values over 1 million (Actually, over 999,999) is because the replace function here:
    jQuery(this).text().replace(",",""))
    will only replace the first occurence of the comma. Numbers over 999,999 have 2 commas, so the function attempts to numerify 1000,000 - and fails.
    To overcome this, the replace command should use RE, like this:
    jQuery(this).text().replace(/\,/g,"")
    This should be implemented for all three replace commands.
    2. This code will only work for non-aggregated lists, as also mentioned in the post. for lists where query is set to aggregate to the lowest level (Auto group&summarize), this code:
    jQuery('table[LID=lstJQuery] td:nth-child(' + column + ')').each(function(index)
    Should be replaced with this:
    jQuery('table[LID=lstJQuery] td:nth-child('+column+'n)').each(function(index)
    Of course, if the list is grouped further, another technique would be required.
    3. The code begins with the minimum value of 0. Some lists begin with a number higher than 0, making some leaps at the beginning of the slider redundant. I recommend changing 0 to a variable, say minVal, and after this part:
    jQuery('table[LID=lstJQuery] td:nth-child(' + column + ')').each(function(index) {
    if (Number(jQuery(this).text().replace(",",""))>= maxVal) {
    maxVal=Number(jQuery(this).text().replace(",",""));}
    });

    which sets the max value, add this:
    var minVal=maxVal;
    jQuery('table[LID=lstJQuery] td:nth-child('+column+'n)').each(function(index) {
    if (Number(jQuery(this).text().replace(/\,/g,""))<= minVal) {
    minVal=Number(jQuery(this).text().replace(/\,/g,""));}
    });
    minVal=minVal-1

    4. Also, hard-coded leaps of 50 may not be suitable for the data.
    For instance, if there are too many leaps, the silder might not be long enough.
    Here, there are a few approaches, but probably the most comfortable one for the programmer would be to set the number of leaps we want to have (say, 10) hard-coded (Thus making controling the length of the slider that much easier) and then to find the step value by dividing maxVal-minVal by 10 (Or any other number), like this:
    var step=Math.round((maxVal-minVal)/10)
    and setting step to equal the step variable.
    Note that you will have n+1 leaps, as the zero leap (This one from minimal value to the first step) is unaccounted for.

    Again, thank you for posting this - this is very very useful.

    ReplyDelete
  6. Hi,
    This is GREAT tutorial!!
    I am trying to make it on Cognos 10 BI, and everything seems to be OK except that it is NOT running. The slider occasionally moves, and the highlights are NOT shown at all

    ReplyDelete
  7. anybody tried this code sucessfully in Cognos 10. I have the same problem as Zvi mentioned. The slider doesn't slide.

    ReplyDelete
  8. Hello All,
    I have same issue as Zvi and Gicy. Any luck with Cognos 10?

    ReplyDelete
  9. I everyone, I have updated the post, so you can use it Cognos 10. As written, just add RS to LID=lstJQuery (So, it should be LID=lstJQueryRS) and it should work with Cognos 10.X.
    Thanks for Nimrod for his constructive comments. I have modified the JavaScript code according to his suggestions.

    ReplyDelete
  10. I am using Jquery in my reports in report studio 10.1
    Problem is whenever I refresh page prompts disappear.
    Please help.....

    ReplyDelete
  11. I am attempting to follow you tutorial exactly, but use a custom 1.9.2 JQuery UI and the 1.8.2 JQuery release and the code is not working. Please help.

    ReplyDelete
  12. I have just had my eyes opened to these impressive possibilities, and thanks to you Ran for assisting in this!!!

    I do have a question though...if I wanted to adjust the code so instead of highlighting the records that fall within the range of the slider choice, I actually filtered the list column?? So for each measure identified as a column in the list I would build a slider to allow the user to filter each column.

    I was thinking of utilizing parameter filters within the code, but have hit a wall with manipulating that within the code you presented...any ideas? I am working within Cognos 10.1.

    Thanks in advance!!

    ReplyDelete
  13. This comment has been removed by a blog administrator.

    ReplyDelete
  14. it's a great tutorial...
    but can somebody tell me how the logic, when i change the value of the slider, the minimum Quantity(who in a red box) can add 10 or 15 ???

    i haved try it and i still not found the way...
    please help...

    ReplyDelete