Team,
I am looking for the below requirement. Please let me know if anyone had implemented it.
1) Am using Relational model
2) In the Report there is a List of 6 Columns
Col 1 - Company Name
Col 2 - Client Name
Col 3 - Division Name
Col 4 - Customer Name
Col 5 - Net Balance
Col 6 - Division Counts
3) When we run the Report List should show Company Name with Net Balance & Division Counts
4) When we expand Company Name, that Particular Company Name should Expand and Show Client Name with Net Balance & Division Counts
5) Similarly when we expand Client, for that Particular Client - the report should Division with Net Balances & Division Counts
6) Client doesn't want to implement the DMR Model like Drill up & Drill down, they are looking for Expand & Collapse option, is there anyway that we can achieve this using JavaScript. Please suggest!
7) I have used the below code, but in the first run the report is not aggregating to company level.
<!-- 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>
Thanks!
Kiran
Quote from: Kiran Kandavalli on 19 Dec 2017 01:51:04 AM
Team,
I am looking for the below requirement. Please let me know if anyone had implemented it.
1) Am using Relational model
2) In the Report there is a List of 6 Columns
Col 1 - Company Name
Col 2 - Client Name
Col 3 - Division Name
Col 4 - Customer Name
Col 5 - Net Balance
Col 6 - Division Counts
3) When we run the Report List should show Company Name with Net Balance & Division Counts
4) When we expand Company Name, that Particular Company Name should Expand and Show Client Name with Net Balance & Division Counts
5) Similarly when we expand Client, for that Particular Client - the report should Division with Net Balances & Division Counts
6) Client doesn't want to implement the DMR Model like Drill up & Drill down, they are looking for Expand & Collapse option, is there anyway that we can achieve this using JavaScript. Please suggest!
7) I have used the below code, but in the first run the report is not aggregating to company level.
<!-- 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>
Thanks!
Kiran
Hi,
It looks to me like you're trying to reinvent a feature that exists out-of-the-box with dimensional packages. A DMR model will give you the capabilities you need here?
Cheers!
MF.
Yes I agree :( , its out of the box that I am trying to implement.
As I am not doing Framework Model for this Module, so I am trying to convenience the user by showing the example of Drill up & Drill down feature. Which he doesn't like and looking for Expand & Collapse Option.
I have seen an example in the below URL which works fine as per my requirement, but the problem is the JS file is stored in Server, so when I ran the report it shows me the output first with all rows and executing the JS file. So, Basically I am looking for a inline JS - to see if that works.
http://www.pmsquare.com/main/blog/custom-javascript-in-cognos-expand-and-collapse/ (http://www.pmsquare.com/main/blog/custom-javascript-in-cognos-expand-and-collapse/)
Thanks!
Kiran
Glad to hear you're getting some use out of it, even if it doesn't meet your needs 100%.
It sounds like you want to load the page without the rows loaded at all. I do have a solution, but it does require you to model the data in DMR in a single hierarchy.
Take a look at the solution here:
http://cognospaul.com/2014/09/29/expand-collapse-cognos/
When the user clicks on one of the buttons, it will add/remove the member into a hidden input. The expression on the axis loops through those fields, returning the children, unioning them to the main set, then sets them in hierarchical order.
Sorry for the late reply.
Thanks Paul! The solution which you created working fine for me. Thanks a lot!