Create a Report with Collapsible Hierarchy

Keywords: Collapsible Hierarchies OR Expandable List Report OR Tree Structured Report

Business Case:
Collapsible hierarchies are useful when you have multiple levels of grouping and subtotals in a report and you don't want to display all the data at once.  When the report loads, it should display only the top level data. Users should be able to expand the group value that they are interested in and the sub groups for the expanded groups should display. If there is another level within the subgroup, then the users should be able to expand the subgroups as well.

In Essence, the Report Studio list report should behave in a fashion similar to, but not the same as, an Analysis Studio report.  This is an indirect way of achieving drill down functionality for relational data. This can be achieved using a drill through report with itself as target, but the advantage of this approach is that the uses will see the results instantly without having to rerun the report.

Environment: Cognos 8.4.1, Report Studio, Javascript, HTML

Example:
To understand the business case better, look at the following example. Here's how the tabular data looks like:

We have three levels: Group A, Group B and Group C. The report should be grouped by Groups A and B. A traditional grouped report will look like this:

This is fine if the number of rows is less. As the data set grows, there might be too much data crammed in the report that the users can't analyze the data better.

Once the collapsible hierarchy solution is implemented, here's how the report will look like when it loads:

The user will see only the high level information. Looking at this, it is evident which group is performing well and which one is not. Now the users can drill down into the group they are interested in by just clicking the + icon next to the group.  When the groups are expanded, the report will look like this:

Now the users can see the subgroups in the respective groups. Since this is a two level grouping, the user can drill down one more level deeper to see the detailed data, which will look like this:

Users can collapse the groups by clicking on the - icon next to the parent.

Solution:
The collapsible hierarchies are build using Javascript on traditional list report. Since this is javascript based, it can work only when the user executes the report in HTML format.  See how the report looks when the same report is executed in PDF format:

It looks very similar to the report in HTML format when all the groups are expanded, but the +/- icons are missing.

Note:

You need the image files for the "plus" and "minus" signs. The javascript code used here references expand_plus.gif and expand_plus.gif in the <c8_installation_directory>/webcontent/pat/images folder.  If your environment doesn't have these files you download them from the links below and place it in the correct folder on your server:

• Plus Sign
• Minus Sign

Implementation:
We start by preparing the layout for a collapsible structure. Even though the columns Group A and Group B are grouped in this report, you will notice that the detail section of this report has individual cells instead of a merged cell. This can be achieved by following these steps:

• Create a simple list report with all the required columns. (I am going to refer to the grouped columns as A and B. Substitute accordingly in your case.)
• Group columns A and B.
• From the "Structure" menu, go to "Headers & Footers --> List Headers & Footers". Alternatively, you can use the "Headers & Footers" icon in the toolbar.
• In "List Headers & Footers", check the following items:
• List Header
• List Footer
• Group A Header
• Group B Header
• So far, the list report layout should look like this:

• Now, CUT (don't delete) the details section of Group A and Group B (see selected items in the above image).
• Unlock the items (using the icon in the toolbar or from Structure menu) and delete the texts "List Page Header" and "List Page Footer". The layout should now look like this:

•  Next lock the items and from the Insertable Objects Pane --> Data Items tab, add back Group A and Group B to the beginning of the list report.
• Once again Unlock the items and CUT "Group A" and "Group B".
• Then, one after the other, select Group A Header and Group B Header and click "Split Cell". After this, all rows of the list report, detail and summary will be evenly spaced without any merged cells and the report should look like this:

• Next, unlock the cells and drag "Group B" to the second column. 
• Now the first cell of the rows "Group B" and "Group C" are empty. We need to add a blank text item, so that the javascript can recognize it as a child row and collapse it.
• Drag a text item to first cells of Group B and Group C rows. You don't have to enter any text, just put a few white space characters (I put 2 spaces).  See screenshot below:

• Now comes the Javascript code. We will be using 3 scripts.
• The first script, which is also the main script will be placed in the "List Header". In the image above, it's the first blank row below the titles. Let's call this "Script 1".
• Unlock the cells, drag an HTML item to the List Header and place the following script inside:

<!-- Script 1: Master Script -->
<span id="ExpandedTable"> </span>
<script>

var displayStyle = "";
var noDisplayStyle = "none";

var currentRow = 0;

// This function expands/collapses a table where the first columns have +/- icons
function ExpandCollapse(el,loc) {
        // change icon
       el.src = "../pat/images/expand_" + (isPlus(el) ? "minus" : "plus") + ".gif";
       var tr = el.parentNode.parentNode; // the current row

       currentRow = tr.rowIndex;
       showLines(el);
}

function showLines(el) {
       var show = true;  // to show lines or not

       // Grab the ROW that was clicked and the TABLE that contains it
       var tr = el.parentNode.parentNode; // the current row
       var tbl = tr.parentNode.parentNode; // the table
       var cid = el.parentNode.cellIndex; // the index of the column

       if (isPlus(el)) {
              show = false;
       } else {
              show = true;
       }

       while (currentRow < tbl.rows.length-1) {
              currentRow++;
              var trCurrent = tbl.rows[currentRow];
              var index = findIconInRow(trCurrent, 0); // return the index of the first img
              if (index >= 0 && index <= cid) {
       trCurrent.style.display = displayStyle;
currentRow--;
return; // found the next line with icon in the same column; return
              }

              if (!show) {
trCurrent.style.display = noDisplayStyle;
              } else {
trCurrent.style.display = displayStyle;
var iconIndex = findIconInRow(trCurrent, cid + 1);
if (Number(iconIndex) > -1) {
       var icon = trCurrent.cells[iconIndex].firstChild;
       showLines(icon);
}
              }
       }
}

function isPlus(el) {
return el.src.indexOf("minus") == -1;
}

function hasIcon(cell) {
// return true if this cell has an img
if ((Number(cell.childNodes.length)) == 0) {
return false;
}
var c = cell.firstChild;
if (c != null) {
return (c.tagName == "IMG");
}
return false;
}

function findIconInRow(trCurrent, cid) {
for ( var i = cid; i < trCurrent.cells.length; i++) {
if (hasIcon(trCurrent.cells[i])) {
return i;
}
}
return -1;
}

function StartHidden() {
       var q=document.getElementById("ExpandedTable");

       // get the table
       tbl = q.parentNode.parentNode.parentNode.parentNode;
       var isFirstRow=true;

       for ( var i = 0; i < tbl.rows.length; i++) {
              var trCurrent = tbl.rows[i]; // the current row
              var d = trCurrent.cells[0]; // first cell
              var c = d.firstChild;

              if (i < 2 || c.tagName.indexOf("IMG") != -1) {
                     c = c.src; // leave it visible, since it has + icon
for ( var j = 1; j < trCurrent.cells.length; j++) {
       trCurrent.cells[j].width="1";
}
              }
              else {
                     trCurrent.style.display = "none"; // hide the row
              }
       }
}

</script>

• With the first script in place, the report layout will look like this:

•  The second script is a wrapper script that will be placed around "Group A" and "Group B". This script will make the entries clickable. Let's call them Scripts 2a and 2b.

• With the cells unlocked, drag two HTML items, one before the data item and one after. See screenshot below for better understanding:

•  Place the following code in Script 2a:

<!-- Script 2a: Wrapper Script -->
<img onclick='ExpandCollapse(this,"A_img")' src='../pat/images/expand_plus.gif' style='cursor:pointer;vertical-align:middle; margin-right:2px'/>
<span onclick='ExpandCollapse(this.parentNode.firstChild,"GroupA")' style='cursor:pointer'>

•  Place the following code in Script 2b which completes the wrapper script:

<!-- Script 2b: Wrapper Script -->
</span>

• The last script (Script 3) will be placed in the list footer. This script will collapse all the headers when the report loads.  All it does is to call the StartHidden() function defined in the Mater Script.

<!-- Script 3: Start Hidden -->
<script>
StartHidden();
</script>

 Now, if you run the report, it will load with all the groups collapsed (as shown in the 3rd image from top).