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:
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).
Hi Sriram: This is a very good concept. Thanks for sharing it. Question: Is the JS compatible on C10 environment for Cognos as well?
ReplyDeleteHi Kavitha, Thanks for the comment. The script uses only HTML DOM objects, nothing Cognos specific. So it should work fine with Cognos 10.
ReplyDeleteHi Sriram,
DeleteGreat work!! Keep it up..
Could you post a JavaScript so we can sort our report after it renders?
That would be great....
Thanks
Thanks CognosTech. My next post is about dynamic sorting. I finished that script just a few days ago.
DeleteSriram.
@CognosTech: I have published a post for Dynamic Sorting. Check it out.
DeleteSriram.
Great Post - Unfortunately can't get it to work in 10.1.1
ReplyDeleteJust displays the headers no detail.
Amended Script 2a to pick up my column (equivalent of Group A)
Added the images to the pat\images folder
Any ideas ??
Dean, I haven't tried it on 10.1 yet. I will check it and let you know.
ReplyDeleteSriram.
ok thanks will keep checking back
DeleteI am also trying on 10.1 I think the problem is related to the plus minus images. Either they are not on the server, or they are in a different location. I don't have access to our server to find out. Is it possible to do the same with a textual plus minus icon?
ReplyDeleteI.e. something like (+) and (-)
OK, I have messed around with this for a while, and I am now pretty sure that the reason it isn't working is because the images are missing.
ReplyDeleteMy javascript skilss aren't the best, so I have been doing a bit of trial and error (more error than trial)!!
Basically the way it works is when you open the report, it looks for an image at the start of the row and hides all rows where there isn't an image in the first column.
I am not getting any images (because the address isn't valid on my server), and therefore all rows are being hidden. You can trick it by putting any old image there, but this doesn't really give you the desired outcome!
I have been able to get (+) and (-) to display instead of the image using div tags instead of img, and I can even get it to change from a "(+)" to a "(-)" when clicked, but can't get the hiding working because this bit needs the image.... catch 22!!
Here is my modified code, perhaps someone really smart can help me get it working....
This puts the textual (+) in place of the image at the start of the row
===================
===================
(+)
===================
===================
In Script 1, I have changed this bit to swithc between "(+)" and "(-)"....
===================
===================
// 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";
el.innerHTML = (isPlus(el) ? "(-)" : "(+)");
el.title = (isPlus(el) ? "minus" : "plus");
var tr = el.parentNode.parentNode; // the current row
===================
===================
And this bit does the checking of whether it is plus or minus.....
===================
===================
function isPlus(el) {
return el.title.indexOf("minus") == -1;
}
===================
===================
NOTE: I had to change "div" to "d_iv" and "span" to "s_pan" because these posts are filtered for HTML code. It doesn't look like that in my report!!!
Hi,
ReplyDeleteCan you share the javascript for Multiple Drill-Through Reprots, where we can access parameters.It will be great help
Thanks,
Nasreen
Does this work with Firefox ?
ReplyDeleteI couldn't make it work with FF.
Yes, I was able to make this work in both IE and firefox.
DeleteSriram.
Hi,
ReplyDeleteI don;t know if anyone is having difficulties with this on 10.1.1 but I found a link on the ibm site. This actually has working script for 10.1.1 and it also has code for 8.4 and 10.1 I've not tried these.
They are xml report definitions so you to use the page how to structure the report.
http://www-01.ibm.com/support/docview.wss?uid=swg21377785
Hope this helps D0nmac
Awesome, I struggled with 10.1.1, but the link worked a charm.
DeleteHi Sriram, how are u?
ReplyDeleteFirst, congrats for your great job!
Let me try to explain my scenario, maybe you can tell me if it is reasible or not:
I have a list in a Cognos report, with 5 columns, which is: Owner, Type, Currency, Code, Sub-Code and Revenue ($).
I've tried several ways to implement this, but without success. See, I need to implement the Expand/Collapse feature from de column "Code", to show their "Sub- Codes". I mean, I need to show the Total Revenue for the Code level/line (I believe this is no a big deal) and once a clicked on "plus" button, I expand the Sub-Codes with their Revenues. I don't want the other collumns (Owner, Type and Currency)
be part of that collapsible hierarchy.
Do you believe that is possible to implement this feature for a specific column inside a list with another columns?
Thanks a lot,
Rafael Pereira / rafael_systems@yahoo.com.br
Were you able to do this? I need help on similar scenario.
DeleteThanks
This comment has been removed by the author.
ReplyDeleteHi Sriram,
ReplyDeleteIs this concept possivle in a crosstab report? Thank you for the great article!
Kind regards
Pedro Martins
Hi Pedro, I haven't tried this approach with crosstabs. But it should work for crosstabs too.
DeleteThanks,
Sriram.
Hi Sriram, did you tried this collapsible hierarchies in cross tab report. If so let me know how it worked
ReplyDeleteHi Sriram, I have a collapsible cross tab report in HTML. When user clicks "Export to Excel", all the hidden columns reappear. Can we just export only those rows that have been expanded (unhidden)..
ReplyDeleteThanks! Nayaz Ahmed
Helly Nayaz,
DeleteExpanding/Collapsing happens only at the browser level. So when the report is exported, the all rows will be exported.
When i deliver(email) the report in HTML format, The Plus sign is not working. Do you know any solution for this. (Version -Cognos 8.4.1)
ReplyDeleteRahul,
DeleteJavascript doesn't exactly work the same way in email. It depends on email client. Which one are you using?
Sriram.
Hi,
ReplyDeleteI'm using Cognos 10.1 and the expand/collapse worked fine for me with the link for 10.1 javascripts.
But, I would like the report to start when all the coloumns are expanded rather then closed up like they come now.
I have no clue in javascripts and would very much appreciate the help of modifing this fantastic scripts to ones that start when everything is shown.
Thanks alot!
Coral.
Coral,
DeleteTry this, in the Master Script there is a function called "StartHidden". This function is called during the page load and collapses all the rows.
Inside this function, there is a line:
trCurrent.style.display = "none"; // hide the row
Change this line to
trCurrent.style.display = ""; // show the row
That should do the trick. But for ease of following I would recommend renaming the function to StartExpanded (instead of StartHidden). If you do this, don't forget to change the name in Script 3 as well.
Sriram.
Even if I had run the original script provided, the output was EXPANDED only :(
DeleteAppreciated your help, Sriram
Hi Sriram,
ReplyDeleteThanks for this post.
I have Cognos 10.2 and the above code is not working. I have the image files in place. I have even noted the your modifications for 10.1 but its still not working on 10.2. All I get is the list Header names. That's it. Rest is all blank.
Any suggestions/advice?
I have also tried the steps given at
http://www.ibm.com/developerworks/data/library/cognos/page198.html
From the IBM link (For Cognos 8), I get the images and also get all the groupings on the screen, BUT clicking on the Plus sign is not working.
Thanks n Regards
Dev
Hi Sriram,
ReplyDeleteGreat article and a huge help!
Is there a way for users to expand all or collapse all ? All of my drill up/downs work perfectly however I would like to give my end users an option to expand all or collapse all hidden rows with one click.
Regards,
Mick
Just wondering if anyone has been able to get this to work in a crosstab. I'm sure there are some sublte changes required but I've not been able to discover them yet.
ReplyDeleteThank you in advance.
Brian.
Thanks for the wonderful post.
ReplyDeleteits working fine,but when I run the report in Excel format the "Collapsible Hierarchy " feature does not work.
Can any one please tell me why it does not work ?
Or is there any other way to achieve the above ?
Thanks,
dhendeyogi
It uses HTML tags and works for report output in HTML only.
DeleteIn Excel, you will get expanded view and see all rows.
Hi Sriram,
ReplyDeleteThanks for posting this code. WIth little tweaking of image path to my server having + and - images, it works wonderful. I have used this in multiple reports.
Now, client wants collapsible hierearchy for two lists side by side. Its working fine for list on left but no luck for list on right. I get expanded data in right list instead of collapsed view.
On deleting left list, it works fine for right list. But problem using collpasible hierarchy code given by you for two lists side by side in a report.
Please help urgently.
Thanks
Payal
Got it resolved. If anybody gets stuck on this issue, I will help.
DeleteHello ! This solution is great !!!
DeleteCan you please let me know how you fixed your '' two lists side by side '' issue ?
Thank you !!!
Hello ! This solution is great !!!
DeleteCan you please let me know how you fixed your '' two lists side by side '' issue ?
Thank you !!!
Hello ..
DeleteI need to collapse and expand in crosstab . When I am applying all the rows below it getting collapse instead of group by group. Placed separate members for each row instead of a hierarchy.
Hi Sriram,
ReplyDeleteFirst up thanks for the example. I've been testing this in 10.1.1 and 10.2.1 and the function works great.
My problem is performance between browsers.
Firefox 22, Firefox 23, Chrome work well. IE 8, 9 and 10 don't work so well
Operating systems tested (XP and Win7 64 bit).
There is a delay during rendering and expand and collapse of upto 2 minutes at the worst case.
FF and Chrome take 10's of seconds to open and sub seconds to expand or collapse.
Just wondering if anyone else has experienced this browser issue?
Cheers
Peter
It works very good with my IE 2008.OS Windows 7. Don't know how it works for other browsers.
DeleteIs there a way to show the oveall totals at either the top (list header) or bottom (list footer)? This method only allows you to show the grouped totals, unless you either expand all or export to Excel/PDF. Is there a modification we can do to the script that will always display the list header or footer, so I can show the totals there?
ReplyDeleteThank you.
- Doug
Hi,
ReplyDeleteIam currently working on 10.1.1 version with a report studio with a cube as the source.I have tree prompt which has hierarchies by names and has mutiple levels under each name.The report has 2 sections summary and detail.When i select the first name the report runs fine in both summary and detailed page.If i select name 1 and name 2 the summary page picks up only the first name whereas the detail lists the children as well.I used MDX functions for the summary parent(selected children) and for the selected children i use descendants(set(#promptmany).
Please let me know.
Regards
Hi,
ReplyDeleteI have the code working for one list object on a page. I'd like to add a second list to the same page. When I do so, the 2nd list renders with everything uncollapsed by default. Any ideas on why or how to have the 2nd list initialize collapsed? thanks!
problem solved: modified the StartHidden() to take a parameter and passed the name of the span object.
DeleteThis comment has been removed by the author.
DeleteHello,
ReplyDeleteWhen i run the report the output is not getting generated as collapsed.. Could you please help...
Did you use the StartHidden() script? That's what the script is for
DeleteLast point from the article:
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.
Hello,
ReplyDeleteI have the report with sectioned output on Month. My requirement is to show and hide the corresponding month using + and -. I have used the same logic shown above. When I run the report the output is not generated as Collapsed.
I now the function StartHidden() is not woring in my case. I have 12 elements in the list and grouping done on one column only can you change the StartHidden() function for this requirement. I have placed the function in list footer only.
Could you help me on this..
Thanks,
Ananth
Ananth,
DeleteThis script works with grouped columns, not sections.
Sriram.
Hello Sriram,
ReplyDeleteThank you so much for this great article. Through this java script code, we are either able to show all sections expanded or all sections collapsed. Can you please help me with this scenario -> I want some of the sections expanded and some as collapsed. Thanks again.
Anand,
DeleteAre you talking about how the sections appear when the report loads initially?
Sriram.
Yes Sriram. Initially when report loads for first time, I need some sections expanded and some sections collapsed. Thanks.
DeleteThat is tricky. During the initial load, the function StartHidden() in the main script goes through all the rows and hides the child rows one by one. In order for this to be partial, you should be able to identify the rows before the report is run. How do you know which rows to show and which ones to hide?
DeleteCan I hard code, i.e row 1 expanded and row 2 collapsed ??
DeleteYou cannot do that, since you don't know a) how many rows the report is going to have b) which row is a parent and which row is a child.
DeleteThat's why it's based on grouping, not hard coding.
Thanks Sriram. It means that we can not do like this that some groups are expanded some groups
Deleteare collapsed initially.
Hello Sriram. Initially can I make decision of collapse/expanded on the basis of plus/minus icon ?? when the report loads for the first time ?
DeleteThis comment has been removed by the author.
ReplyDeleteHello Sriram. Initially can I make decision of collapse/expanded on the basis of plus/minus icon ?? when the report loads for the first time ?
DeleteSriram the report does work for my case as of now but the next request for me is to provide headers on top of it which does not work in my case. The HTML items does not allow me to show it in the report?
ReplyDeleteany one tried in the Crosstab report, i have one requirement on Crosstab. please post the code for me. Thanks.
ReplyDeleteSriram, it is not working when there are multiple levels. In this case, only the last node is getting expanded/collapsed.
ReplyDeleteHi Sriram, First great script by the way...made my work a lot easier. I can't believe C10 doesn't have this functionality out of the box. I have 5 list reports. The first always opens up collapsed, however, the remaining 4 open up expanded. I simply copied the HTML objects from the first list report to the rest. Do I need to do something special to have the remaining 4 lists to open up collapsed as well?
ReplyDeleteHow did you implement the 4 lists. Did you copy paste the master script into each one of them? Did you make sure, the startHidden() function is in the footer of all the lists?
ReplyDeleteYou could rename the function to startHidden1(), startHidde2() etc.
Hi Sriram , could you help me how to get collapsed list by default after running the report
ReplyDeleteHi,
ReplyDeleteI tried to implement this solution into a CrossTab but it works only for 3 or less levels hierarchy... I suppose that it sees each row as a sublevel of a parent row instead of see groups of rows as a one row. But i could skip something. Did you try to implement the above code into a CrossTab?
Hi Sriram, this technique is so useful , one can just say number of comments we have.
ReplyDeleteCan this is be applied in Active report?
Hi Arun,
ReplyDeleteI haven't tried this with Active report, I don't think it will work since change in Javascript might break the active report functionality.
Hi Shriram,
ReplyDeleteThanks for your post. Initially when we load the report it shows + sign beside each of the level1 item even if there is no lower level present for that particular level. Is it possible to make it dynamic so that only when lower levels are present then it will show + sign else it will show -.
Hi sriram,
ReplyDeleteThank you very much for your post. It is working good. I have some trouble to build thisexpand and collapse report now everything is fine. It is working good in cognos 10.2. Thanks again.
This comment has been removed by the author.
DeleteHi Sriram,
ReplyDeleteThanks for the blog. It worked for me with 3 issues.
1> The image is not appearing once we expand (while we run the report it appears properly it means the path is right)
2)The sums of the orders and revenues is not appearing one the group header level while the report is collapsed.
Would you able to help ? or anyone who has faced this issue already. ?
I have to save the gifs to ..\samples\images instead of ..pat\images otherwise it doesn't see them at all
DeleteDoes this method for unbalanced Hierarchy ?
ReplyDeleteHi Sriram,
ReplyDeleteThanks for the post.
Ihave a query.
Is there any option for excel output? I mean when we run the report in excel format, it should come with collapse expand feature like you did for html.
Anyone has any idea about this? Please help.
Thanks in advance.
The script works great, thanks for posting it.
ReplyDeleteI have a question though, I have added an overall total for each column and on the initial load of the report the overall total shows but when i expand and collapse the last grouping the overall total collapses into the grouping. How would the code be tweaked so that the final grouping won't include the overall total/list footer in the collapsable rows?
Hello Sriram, Thank you for your post. I am new to IBM Cognos and try new features or ways of reporting data. I came across this and find it interesting. It works, but I run into one issue. When I click on the icon (say one of the SubGroup1 under Grouping1 as stated above) it collapses or expands every group under that. i.e. Expands and collapses SubGroup2 and Grouping2 and it's subgroups. Am I doing something wrong? I am more of a DB person, so no experience with javascript but can get some local help. Also the icon isn't showing up correctly, I guess I can do something about it. Please reply if you get a chance. Thanks for your post again. - Sridhar
ReplyDeleteHi Sriram,
DeleteCan you please update code for Cognos 10 version.
Hello Sriram,
ReplyDeletefor me its worked fine upto single level grouping, but when I have multiple levels groupings in third grouping its coming with all expanding, please help me sriram i have the requirement of this.
This comment has been removed by the author.
ReplyDeleteThank you Siriam, this is a great start. Mine has 2 groups & runs fully expanded with the + icon next to them (it thinks they're collapsed). So there is something missing in the startHidden() function. When I click on the + icon, it does switch to - & remains expanded. When I go to collapse a row, it collapses everything below it, instead of just it's group.
ReplyDeleteI have to save the gifs to ..\samples\images instead of ..pat\images otherwise it doesn't see them at all
Commercial property loans are very important financing tools that aid business growth. mortgage calculator canada Try out several scenarios, modifying the term and the amortization period, to identify the best solution for you personally. mortgage payment calculator canada
ReplyDelete