Wednesday, January 18, 2012

Show or Hide List Report Columns using Javascript

Business Case:
Report author wants to create a list report in, which, any of the columns can be shown/hidden after the report execution is complete.

Environment: IBM Cognos 8, Internet Explorer/Mozilla Firefox, Javascript, HTML format reports.

Example:
A report is used by many users. This report has many columns, and depending on which user is viewing the report, users might want to temporarily hide one or more columns and later unhide them.

Note:
  • This requirement is different from the case where users know before executing the report, which columns need to be displayed. If the users know beforehand, Render Variable can be used to hide the columns. But in this case, the columns cannot be shown again without re-running the report.
  • Since this solution is implemented using javascript, it goes without saying that it works only when the report is run in HTML format.
  • I have tried this with a list report, but the script should work for crosstabs as well. If you have trouble making this work in crosstabs, please post in the comments section.
Implementation:
  • Implementing this requirement needs some placeholder HTML items on the page in addition to the main script.
  • The list report in cognos is rendered as a table in the cognos viewer, consisting of rows and columns. Unfortunately, the entire page in Cognos viewer consists of rows and columns.
  • In order to identify the list report, we will be unlocking the cells and placing an HTML item in the report title section in one of the list columns (it doesn't matter which column). The HTML item will have the following script:
<span id="list"></span>
  • Now we need a bunch of links outside the list report that can be used to toggle (show/hide) the columns. 
  • You can create this just before the list report, but it won't work if your report spans multiple pages. I placed this in the page header, since the header spans across all the pages.
  • I created a table with one row and 5 columns one for each column in my list report. I then placed, one HTML item in each column. The HTML items will have the following scripts.
Script in 1st HTML item: <a href="javascript:hideColumn(1)">String</a>
Script in 2nd HTML item: <a href="javascript:hideColumn(2)">ID</a>
Script in 3rd HTML item: <a href="javascript:hideColumn(3)">Name</a>
Script in 4th HTML item: <a href="javascript:hideColumn(4)">Type</a>
Script in 5th HTML item: <a href="javascript:hideColumn(5)">Flag</a>
  • If you notice above you have match the column number and name to your list report. Now the report layout should look like this
  •  The last piece is the HTML item for the main script. You can place this just after the list, but to support reports that span multiple pages, I placed it in the page footer. The main script is as follows:
<script>

function hideColumn(colNum)
{
  var listHeader = document.getElementById("market");
  var table = listHeader.parentNode.parentNode.parentNode.parentNode;
  var rows = table.getElementsByTagName("tr");

  for(var i=0;i<rows.length;i++)
  {
    var cells = rows[i].getElementsByTagName("td");

    if(cells[colNum-1].style.display == 'none')
    {
      cells[colNum-1].style.display = '';
    }
    else
    {
      cells[colNum-1].style.display = 'none';
    }
  }
}

</script>
  • When the report is executed, it should look like this:
  •  You can see that, my report spans multiple pages. When I click the link "String" the first column will be hidden and the report will look like:
  • You can see that even though the column is hidden, the link "String" is still visible. You can click on this link again to show the "String" column.
  • I clicked on the "Type" link and now the report looks like this:
  • Even though the approach may seem long, it is pretty simple. 
  • The advantage of this script is that, since we are not manipulating any cognos objects like prompts, this script should work for most versions of Cognos.
  • The script has been tested in both Internet Explorer and Firefox.
This should score you some points with your users.. Have fun...



21 comments:

  1. Thanks,Sriram.It is truely wonderful.Coudn't we do away with java script.Appreciate your time and effort.

    ReplyDelete
  2. Thanks for your comment. Until IBM realizes that these are simple features that they can easily provide and build it as a feature set of cognos, we are stuck with using Javascript.

    Sriram.

    ReplyDelete
    Replies
    1. Thanks Sriram....
      Could you please add a report XML to it.
      It would be great....

      Delete
    2. Hi Sriram:

      Could you please add a report xml for this? I tried it seems like I get an object error at char 3. I do have a question on your script. What is the item market that you are referring to?

      Thanks.

      Delete
  3. i want to hide the list at the runtime.
    after pressing the button the my list will display, please give me the response.

    it is in cognos.

    ReplyDelete
  4. Hi Sriram

    The posts from you are real good.

    I got a requirement, could you please suggest any solution how to implement that.

    I got a hierarchy (eg., Products) and a Measure. Now when I drill down to detail I need values of a new measure to be displayed.

    So when I drill down to other level from root level, my measure should display other value.

    Sai

    ReplyDelete
  5. Hi Sriram,

    Very good features indeed.

    I am facing one issue in my cognos report. I have list data template and I have data field in my list header along with some labels. The data in the list header is not updated and take the same as previous page list header if there are multiple list objects on the same table.

    ReplyDelete
  6. Hi Sriram,

    Does this feature work for when I have to hide two or three group by columns. For example if string and id are two columns having a group by then if i hide string , will the grouping be on both the columns or only the ones which are displayed...well i am not good in js...hence asking this.

    btw...great piece of info

    Kannan

    ReplyDelete
    Replies
    1. Hi Kannan,

      This script should work on grouped columns as well. It really doesn't matter, since we are just manipulating html items. If in case you have trouble making this work, you have to break any merged cells in the grouped column.

      Sriram.

      Delete
  7. Sriram,

    Superb script, works very well and thank you! However is there a way to amend the script to the list is hidden runtime?

    Once I press the button the my list will display?

    Would really appreciate a response?

    Thanks
    B

    ReplyDelete
  8. thanks a lot......

    ReplyDelete
  9. superb brother, learning many things from ur blog. thanks a lot bro

    ReplyDelete
  10. Hi Sriram:

    I have a requirement to show/hide multiple measures in a cognos crosstab. I have one dimension, a column as a row and 4 measures. The show/hide for the measures based on prompt multi-select. I am having a hard time formatting the top label.

    Thanks!

    ReplyDelete
  11. Hi Sriram,

    I was trying to show/hide a crosstab column (node cordinate e6) at crosstab. I followed your instruction and successfully did at list but Some how it donesnot work at cross tab.It just hide some values but didnot hide header and other value.

    Can you please help me out.

    ReplyDelete
    Replies
    1. Hi,

      I have never tried to use this for crosstab. I will look into it.

      Thanks,
      Sriram.

      Delete
    2. Thank you Sriram ,
      Looking forward to hear from you.

      Thanks!

      Delete
    3. This comment has been removed by the author.

      Delete
  12. Hi Sriram,

    I want to create a button for each row.

    like my data: deptno dname
    ===========
    10 ABC
    20 XYZ
    when sectioned the list on the basis of "deptno" then list display data like below;

    10 [SHOW DEPT BUTTON] <------------------- BUTTON REQUIRED
    -----------------------------------.
    ABC

    20 [SHOW DEPT BUTTON] <----------------------BUTTON REQUIRED
    -------------------------------------
    XYZ


    When clicked on respective button it should show the respective row data...Suppose if clicked on the "button"
    which is in dept 20 row, it should show the dept20 data only..like this.

    Kindly help me...i am poor in javascript...

    very urgent..


    Regards,
    KSR

    ReplyDelete
  13. Hii Sriram,

    Kindly look into my post...

    Regards,
    KSR

    ReplyDelete
  14. Hello,

    How to do if i want to hide per one click Columns 1,2,3,4 from a table with 8 columns?

    ReplyDelete
  15. Hi dear, I like your post so much, and it is very good. Hope that we can be friends and communicate with each other. By the way, have you ever used partition resize windows 2003 server ? Unfortunately I lost my partition. I do not know how to do.

    ReplyDelete