Extending the Report Studio Toolbox with a Dynamic Column Filter Control

[wisdomt1]

Has anyone tried adding this filter to a list report in C10.1 and had it work successfully? I have followed the podcast to the T but can not seem to get it to work.

[CognosPaul]

We tried to implement it in one of my clients and found a couple of bugs/annoyances. The main issue was that the LID for the list is different in 10.1 than what was specified in the example script.  The second issue is that the LID changes depending on the namespace (opening from Cognos Viewer or Report Viewer).

I don't have access to the fix that I did at that particular client, but it was a fairly easy fix. Using developer tools, examine the HTML behind the list. It's actually a table and has an LID attribute. Use that LID attribute value instead the list name in the script.

As soon as I get access to that client's Cognos again, I'll post the updated script.

[Deep750]

Thanks Paul,
That did it.
Had to change LID from dataTblRS to List1_NS_ for 10.1.1

Doesn't work if you run it in RS, but via Connection it works great
Tried it in 10.2 too, but doesn't seem to work there, even though the LID is the same in both versions...

New code for 10.1.1:

Code Select Expand

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>

<script>
    //this is a jQuery functionto mitigate any conflict between Cognos and jQuery
    jQuery.noConflict();

    //This function adds a filter prompt to the first column header.
    //This can easily be modified to filter any column
    function addFilter() {

        jQuery('table[LID="List1_NS_"] tr:eq(0) td').each(function(index) {


            //Variable FilterDD holds the html string that defines the dropdown prompt
            var filterDD = '<select id="filter' + index + '" onChange="filterTbl()"><option>All</option>'

            //Get the collection to Table Rows and loop through each one
            var uniqueElems = new Array();

            jQuery(this).parentsUntil("table").find("tr").each(function() {
                //append the text from the column of the current row to the HTML string dropdown filter
                uniqueElems.push(jQuery(this).find('td').eq(index).text());
            });

            uniqueElems = jQuery.unique(uniqueElems);
            jQuery.each(uniqueElems, function() {
                //append the text from the first column of the current row to the HTML string dropdown filter
                filterDD = filterDD + '<option>' + this + '</option>';
            });

            //Add the closing tag to the filter HTML String
            filterDD = filterDD + '</select>';

            //finaly insert the HTML String that defines the dropdown filter into the column header
            jQuery(this).html(filterDD);


        });

    }

    function filterTbl() {
        jQuery('table[LID="List1_NS_"] tr:gt(0)').each(function(i) {
            jQuery(this).show();
        });

        jQuery('table[LID="List1_NS_"] tr:eq(0) td').each(function(index) {

            //Variable mySelect holds the selected option
            var mySelect = jQuery('#filter' + index + ' option:selected').text();
            if (mySelect == "All") return;
           
            // loop through each row
            jQuery('table[LID="List1_NS_"] tr:gt(0)').each(function() {

                //Compare the text from the  column of the current row to the selected option
                if (jQuery(this).find('td').eq(index).text() != mySelect) {
                    //if the text does not = the selected option then hide the row
                    jQuery(this).hide();
                }
            });
        });
    }

    function loadFilter() {
        if (jQuery('table[LID="List1_NS_"]').length == 0) setTimeout("loadFilter()", 100);
        else addFilter();
    }

    setTimeout("loadFilter()", 100);
</script>


[CognosPaul]

That's it.

You can get it to work in RS to by using the following script:

Code Select Expand

var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined) 
{

    fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );

}
var preFix = "";
if (fW.elements["cv.id"])
{

    preFix = fW.elements["cv.id"].value;

}   


then change all instances of table[LID="List1_NS_"]
to table[LID="List1' + preFix + '"]

[Deep750]

Great, thanks.
Do you know if it can work in 10.2?

[navissar]

Great stuff. I had some more annoyances and grievances with this: for instance, I wanted the title to read "<field label>" rather than "All" so that the user is actually able to see the title of the field. I used an array that gets the title of column [index] in position [index]. Also, I wanted an option for when if one field is selected, it filters the rest of the prompts (So if I pick a Product Line, I can't pick a Product Type that's not under that Product Line). This isn't always relevant, but I took the easy way out and fashoined it so that when I need this, I can use it if the fields are in the same order as the cascade. Another annoying thing in this bit of code is that for some reason the unique() funcitonality only works when I use Google API, but not a local jQuery install. I guess Google updated their jQuery to allow the unique() method to work on non-DOM arrays. I just changed that to the norma, day-to-day array sort&make unique javascript method (I admit that between JS and jQuery, JS is my stronger suite).
If anyone is interested in more details or in teh code changes, further than what I explained here, ping me.

[Munki]

Great stuff. I had some more annoyances and grievances with this: for instance, I wanted the title to read "<field label>" rather than "All" so that the user is actually able to see the title of the field. I used an array that gets the title of column [index] in position [index]. Also, I wanted an option for when if one field is selected, it filters the rest of the prompts (So if I pick a Product Line, I can't pick a Product Type that's not under that Product Line). This isn't always relevant, but I took the easy way out and fashoined it so that when I need this, I can use it if the fields are in the same order as the cascade. Another annoying thing in this bit of code is that for some reason the unique() funcitonality only works when I use Google API, but not a local jQuery install. I guess Google updated their jQuery to allow the unique() method to work on non-DOM arrays. I just changed that to the norma, day-to-day array sort&make unique javascript method (I admit that between JS and jQuery, JS is my stronger suite).
If anyone is interested in more details or in teh code changes, further than what I explained here, ping me.

I had same issues with it, nevertheless it's an awesome technique. Would you mind sharing how you got around the column title problem? But that's not major as cascading the filters(when one filter is selected, the other fields are filtered). A report spec with the code changes would be great. Thanks for the help :)

[praveenb77]

Hello All,

I used the above code for my report. But i dont see any filters enabled on my report. Do we have to do some server side modifications for the Dynamic filter to be created in the Toolbox ?

Your help is deeply appreciated.

Thanks!

[praveenb77]

Deep,

I got the script working finally after i replaced my list name to List1.

Nimrod,

Can i know how i can get the title of the column to be displayed instead of 'All' ?. Also if i want Multi-Select will i able to modify the code to accomodate that ?

Thanks!

[peter.almstrom]

Hey gurus!

Would it be possible to extend the code so that when Ive filtered one column the second column only shows whats shown in the list? Since its hiding the rows it might be possible to repopulate the filters to only show whats seen.

Another question is that it gives me a filter on the measure column aswell. Can it be modified so it sort of skips the last column?

Br
/Peter

[bubai2807]

I am trying to use the functionality with Cognos 10.2 .

Changed the list name also. Can anybody suggest  if anybody has done this in report studio 10.2