Friday, February 17, 2012

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:

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).


Tuesday, February 7, 2012

Update Report Specification for Multiple Reports without SDK

Business Case:
The business users decided to change the name of a data item in the Cognos package. Unfortunately there were about 20 reports that used this data item, which means the report author has to open each of these reports and repoint the query item to the correct one.

Background:
If you are not familiar about what a Report Specification is and would like to get a better understanding, read this article first: Explaining Report Specification

You may also be interested in learning how to quickly update a single report with multiple queries for data item name changes. Check out this article: Update Report Specification without SDK

Example:
Let's take the first business case I mentioned above.  Suppose that you have 20 reports have the query item, [Billing Address]. On top of this the term "Billing Address" is used in multiple pages and multiple places in the reports (header, column title, foot notes etc). The report user comes to you, the author, and requests you to change all references to Billing Address to Shipping Address, which already exists in the package.

Solution:
We will be using the Report Deployment option in Cognos to do the bulk update. Note that you will need access to Content Administration to be able to use this approach.

Implementation:
In this case, editing the report in report studio to find every instance of [Billing Address] and changing it to [Shipping Address] is a tedious process. It is highly inefficient too. Here is a quick way to change the report.
  • Prepare the Deployment Content:
    • In the "Public Folders", create a folder called "Deployment".
    • Copy all the reports that need to be updated into this folder.
  • Create Export Job:
    • From the "Launch" menu in Cognos Connection, click "IBM Cognos Administration".
    • Select the "Configuration" tab and click on "Content Administration" on the left pane.
    • Create a new "Export" and specify a name of your choice. For our example, let's call it, "Report Spec Update"
    • In the next screen, under "Deployment Method", select the option "Select public folders and directory content" and click "Next".
    • Under "Public folders content" section, click "Add" and add the "Deployment" folder.
    • Select the appropriate options from the "Options" section.
    • Go thorough the rest of the screens like you normally would and enter a name for the archive in the last screen (E.g. report_spec_update).
    • Save and Run the export job once.
  • Update the Specification:
    • Login to the Cognos server (Use remote desktop connection if Windows, SSH if Linux).
    • Navigate to the deployment folder: <cognos_install_dir>/deployment
    • You will find a zip file with the archive name you specified when creating the export (report_spec_update.zip).
    • Copy the zip file to a different folder.
    • Unzip the contents of the zip file. It will produce 3 files: content.xml, exportRecord.xml and package1.xml.
    • The file we are interested is the package1.xml. Open this file in a text editor. 
    • Note: This is the most important step of the entire process. If your cognos server is running on a linux environment, then the editing must be done in the linux server itself, in vi editor. Do not copy the zip file or the xml files to your windows desktop for editing since this screws up the file encoding.
    • Once you have the file open in the text editor,  do a find and replace for the data item name that you are trying to update. For example, replace all occurrences of [Business View].[Customer].[Billing Address] with [Business View].[Customer].[Shipping Address]
    • Save the file package1.xml.
    • Zip the 3 files to a new archive.
    • Copy the zip file back to the Deployment folder in the Cognos server.
  • Create Import Job:
    • Log in to Cognos Connection and navigate to Content Administration.
    • Create a new Import and from the list of archives, select the new zip file that you just created.
    • Run the Import.
    • The reports in your "Deployment" folder will be updated with the changes you made in the XML file.
That's it !!! The same task, if done manually would take hours to complete and you would not be sure if you updated all the places. The steps above might seem like a long process but when I tested this, the entire process took me about 5 minutes.

If you are in a daring mood, you can expand the approach to the entire content store instead of exporting only the Deployment folder. As long as you keep a copy of the zip file you exported, you are fine.

If you have made a mistake during the update, or if you find out that you have changed more reports than necessary, you can always import the original export back again and you will be right where you started.

Remember.. There is more than one way to skin a cat.. Let's do it the easy way.