Clarity

So, here's my dilemma.  I've been asked to come up with a way to graphically represent project-to-project dependencies at Programme and Portfolio level. I'd love to be able to do something like Mike Bostock's 'Labeled Force Layout' (https://bl.ocks.org/mbostock/950642 - without the animation), preferably in a Jaspersoft report or alternatively in a Portlet.  Trouble is, my Jaspersoft (for a report) and HTML (for a portlet) skills are nowhere near good enough to accomplish it.

Can anyone help out with pointers, a general guide, code snippets or a complete solution (or an alternative suggestion)?

Hi Alistair,

Although its not a report, however refer to the following link if that gives you further ideas. This is a discussion which were present in the communities

OBS Data for Microsoft Visio

Regards,

Samik

Thanks, Samik. I'll play around with it and see if I can get it to give me what I need.

This will show you how to build an html portlet using the D3.js library in PPM

OBS Portlet - D3js 

V/r,

Gene

Your version without the animation is at.

https://communities.ca.com/docs/DOC-231151106#comment-233901155 

The additional files used would have to be placed accessible from the server as the Knowledge store trick does not work in 15.2 and after. See

Is URL redirect working in 15.2 and later 

Hi Martti

Thanks for the heads-up - I use Gene's HTML OBS portlet, but hadn't see the d3 version before. I don't suppose you know if anyone has solved the knowledge store download issue, or if you would have an idea where I could put the js where PPM could find it?  I've tried calling it directly from d3js.org but I think it's blocked. I tried putting it in /fs0/share/clarity but the HTML can't resolve the link.

According to Prashank.Singh the knowledgestore solution does not work.

If I recall correctly Nick Darlington or Suman P. posted something about storing other files on the server.

Neither do I recall if they said that is still possible, or whether that is also blocked.

I'll start a thread on that.

Wow, I'd seen your original HTML portlet magic, but didn't follow the bouncing ball to the D3 version. I can't get it working yet, we're on 15.2 and bumping into the Knowledge Store download issue Martti mentions below.  I've tried writing the HTML directly into the Portlet (rather than calling as separate HTML file) as that's what I've done with my other HTML portlet, and then adding 

<script src="https://d3js.org/d3.v4.min.js"></script>

to the code, but it's not working. I get a d3.js is not defined error, I'm guessing because there's something within PPM blocking the call to the js.

So close, yet so far...

Did you add the <script> tag to the top of your HTML?

The other option is to just paste the contents of https://d3js.org/d3.v4.min.js

inside a set of script tags.

V/r,

Gene

Still trying ... looks like it will be a bit more of a longer-term learning curve.  I'll post the results when I crack the solution. Thanks for the pointers, gcubed and urmas

Just checking your statement: "PPM blocking the call to the js".  It works for me.

So I am able to load the d3.js on a PPM 15.3 system.

Here is my portlet.

 <style>

.link {
  stroke: #ccc;
}

.node text {
  pointer-events: none;
  font: 10px sans-serif;
}

</style>
 <div class='ppm_filter_section'>
     <div>
          <div id='requestDivYes' style="display: none">
               <br />
               <br />
               <table id="requestTable" class='ppm_grid'>
                    <thead>
                         <tr>
                              <td style="text-align: center">Forced Graph </td>
                         </tr>
                    </thead>
                    <tbody class='ppm_grid_content'>
                         <tr>
                              <td>
                                   <div id='theGraph' />
                              </td>
                         </tr>
                    </tbody>
               </table>
          </div>
          <div id='requestDivNo' style='display: none; width: 680px; margin-left: 80px'>
               <br />
               <br />Sorry, no force graph for you! <br />
               <br />
          </div>
     </div>
</div>
<!--
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
-->
<script src="https://d3js.org/d3.v3.min.js"></script>

<script>

function processRequest() {

     var queryRequest = getQueryRequest(currentSession);
     var queryOptions = {
          url: ppmUrl,
          type: 'POST',
          data: queryRequest,
          error: processError,
          success: processSuccess,
          contentType: 'text/xml;charset=UTF-8'
     };
     jQuery.ajax(queryOptions);
}

function processSuccess(queryResults, textStatus, jqXHR) {
     try {
          var img1  = '/niku/ui/uitk/images/selection.gif'


          var records = jQuery(queryResults).find('Record');
          if (records == null || records.length == 0) {

               jQuery('#requestDivNo').show();
               return;
          }

          var nodes = [];
          nodes.push({
               'id': '-1',
               'invId': '-1',
               'nodeName': 'Root'
          });
          var links = [];

          //Build our node and link arrays
          for (var i = 0; i < records.length; i++) {
               var invId = jQuery(records[i]).find('inv_id').text();
               var issueStatus = jQuery(records[i]).find('issue_status').text();
               nodes.push({
                    'id': i,
                    'invId': invId,
                    'nodeName': issueStatus
               });
               
               var source = i;
               var currentNode = i +1;
               if (nodes[i].invId != nodes[currentNode].invId) source = 0;
               links.push({
                    "source": source,
                    "target": currentNode
               });
               if (i > 30) break; //To much data
          }

          jQuery('#requestDivYes').show();
          var width = 960
          var height = 800

               var svg = d3.select("#theGraph").append("svg")
               .attr("width", width)
               .attr("height", height);

          var force = d3.layout.force()
               .gravity(0.05)
               .distance(100)
               .charge(-100)
               .size([width, height]);
               
          force.nodes(nodes).links(links).start();
          var link = svg.selectAll(".link").data(links).enter().append("line").attr("class", "link");

          var node = svg.selectAll(".node").data(nodes).enter().append("g").attr("class", "node").call(force.drag);
          node.append("image").attr("xlink:href", img1).attr("x", -8).attr("y", -8).attr("width", 16).attr("height", 16);
          node.append("text").attr("dx", 12).attr("dy", ".35em").text(function (d) {return d.nodeName});
          
          force.on("tick", function () {
                    link.attr("x1", function (d) {
                         return d.source.x;
                    })
                    .attr("y1", function (d) {
                         return d.source.y;
                    })
                    .attr("x2", function (d) {
                         return d.target.x;
                    })
                    .attr("y2", function (d) {
                         return d.target.y;
                    });

                    node.attr("transform", function (d) {
                         return "translate(" + d.x + "," + d.y + ")";
                    });
          });
     } catch (ex) {
          var msg = ex;
     }
}

function processError(jqXHR, textStatus, errorThrown ){
     var error = textStatus;
     var i = 0;
}

function getQueryRequest(sessionId) {
     var queryXml = [];
     queryXml.push('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:quer="http://www.niku.com/xog/Query">');
     queryXml.push('<soapenv:Header>');
     queryXml.push('<quer:Auth>');
     queryXml.push('<quer:SessionID>?</quer:SessionID>'.replace('?', sessionId));
     queryXml.push('</quer:Auth>');
     queryXml.push('</soapenv:Header>');
     queryXml.push('<soapenv:Body>');
     queryXml.push('<quer:Query>');
     queryXml.push('<quer:Code>xid_qry_issues</quer:Code>');
     queryXml.push('</quer:Query>');
     queryXml.push('<quer:Sort>');
     queryXml.push('<quer:Column>');
     queryXml.push('<quer:Name>inv_id</quer:Name>');
     queryXml.push('</quer:Column>');
     queryXml.push('</quer:Sort>');
     queryXml.push('</soapenv:Body>');
     queryXml.push('</soapenv:Envelope>');
     return queryXml.join("");
}

function isEmpty(str) {
     return (!str || 0 === str.length);
}

var currentSession = window.clarity.session.sessionId;
var host = window.location.host;
var ppmUrl = 'https://' + host + '/niku/xog';
var requestType = {};
jQuery(document).ready(processRequest());
</script>

Which gives me this:

V/r,

Gene

Great, thanks for sharing

Thanks so much for sharing this, Eugene.  I can't claim to know for sure, but I think the issue was with the way I was building up the arrays.  I copied and pasted shamelessly from the example you shared, and have found success!

To close the loop, here is my solution - which is absolutely Proof of Concept, not optimised, etc. etc. And I still haven't figured out how to paste code snippets into here.

1. NSQL Query (fon_project_dependencies):

SELECT @SELECT:DIM:USER_DEF:IMPLIED:DPDCY:DP.ID:RSRC@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:DP.CREATED_BY:CREATED_BY@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:DP.CREATED_DATE:CREATED_DATE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:DP.DEPENDENT_ID:DEPENDENT_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:DP.PRINCIPAL_ID:PRINCIPAL_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:DP.LAST_UPDATED_DATE:LAST_UPDATED_DATE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:I1.CODE:PCPL_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:I1.NAME:PCPL_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:I1.SCHEDULE_START:PCPL_START@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:I1.SCHEDULE_FINISH:PCPL_FINISH@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:I1.PROGRESS:PCPL_PROGRESS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:I2.CODE:DPDT_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:I2.NAME:DPDT_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:I2.SCHEDULE_START:DPDT_START@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:I2.SCHEDULE_FINISH:DPDT_FINISH@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DPDCY:I2.PROGRESS:DPDT_PROGRESS@
FROM
PRJ_PROJECT_DEPENDS DP
INNER JOIN INV_INVESTMENTS I1 ON DP.PRINCIPAL_ID = I1.ID
INNER JOIN INV_INVESTMENTS I2 ON DP.DEPENDENT_ID = I2.ID
WHERE @FILTER@

2. Portlet

<style>

.link {
stroke: #ccc;
}

.node text {
pointer-events: none;
font: 10px sans-serif;
}

</style>
<div class='ppm_filter_section'>
<div>
<div id='requestDivYes' style="display: none">
<br />
<br />
<table id="requestTable" class='ppm_grid'>
<thead>
<tr>
<td style="text-align: center">Forced Graph </td>
</tr>
</thead>
<tbody class='ppm_grid_content'>
<tr>
<td>
<div id='theGraph' />
</td>
</tr>
</tbody>
</table>
</div>
<div id='requestDivNo' style='display: none; width: 680px; margin-left: 80px'>
<br />
<br />Sorry, no force graph for you! <br />
<br />
</div>
</div>
</div>
<!--
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
-->
<script src="https://d3js.org/d3.v3.min.js"></script>

<script>

function processRequest() {

var queryRequest = getQueryRequest(currentSession);
var queryOptions = {
url: ppmUrl,
type: 'POST',
data: queryRequest,
error: processError,
success: processSuccess,
contentType: 'text/xml;charset=UTF-8'
};
jQuery.ajax(queryOptions);
}

function processSuccess(queryResults, textStatus, jqXHR) {
try {
var img1 = '/niku/ui/uitk/images/selection.gif'

var records = jQuery(queryResults).find('Record');
if (records == null || records.length == 0) {

jQuery('#requestDivNo').show();
return;
}

var nodes = [];
var links = [];

//Build our node and link arrays
for (var i = 0; i < records.length; i++) {
var invId = 0;
var childInternalId = 0;
var invId = jQuery(records[i]).find('pcpl_id').text();
var uniqueName = jQuery(records[i]).find('pcpl_name').text();
var childInternalId = jQuery(records[i]).find('dpdt_id').text();
var childName = jQuery(records[i]).find('dpdt_name').text();
var newentry = 0

// Add the Parent IDs to the array
//Check if it's a new record
for (var p = 0; p < nodes.length; p++) {
if (nodes[p].pid == invId) {
var newentry = 1;
}
}

if (newentry == 0) {
// It's a new record
nodes.push({
'id': nodes.length + 1,
'pid': invId,
'nodeName': uniqueName
});
}

//Cycle back through and get the dependent project ids
var newentry = 0
//Check if it's a new record
for (var p = 0; p < nodes.length; p++) {
if (nodes[p].pid == childInternalId) {
var newentry = 1;
}
}

if (newentry == 0) {
// It's a new record
nodes.push({
'id': nodes.length + 1,
'pid': childInternalId,
'nodeName': childName
});
}
}

for (var i = 0; i < records.length; i++) {
// Build up the Links
// Circle back through the recordset and get the ID numbers of the dependencies
var principalID = jQuery(records[i]).find('pcpl_id').text();
var childInternalId = jQuery(records[i]).find('dpdt_id').text();
var t = 0;
var s = 0;
console.log("Starting loop");
for (var p = 0; p < nodes.length; p++) {
if (nodes[p].pid == principalID) {
var s = p;
}
}
for (var p = 0; p < nodes.length; p++) {
if (nodes[p].pid == childInternalId) {
var t = p
}
}

links.push({
'source': s,
'target': t
});
}

console.log(nodes);
console.log(links);

jQuery('#requestDivYes').show();
var width = 960
var height = 800

var svg = d3.select("#theGraph").append("svg")
.attr("width", width)
.attr("height", height);

var force = d3.layout.force()
.gravity(0.05)
.distance(100)
.charge(-100)
.size([width, height]);

force.nodes(nodes).links(links).start();
var link = svg.selectAll(".link").data(links).enter().append("line").attr("class", "link");

var node = svg.selectAll(".node").data(nodes).enter().append("g").attr("class", "node").call(force.drag);
node.append("image").attr("xlink:href", img1).attr("x", -8).attr("y", -8).attr("width", 16).attr("height", 16);
node.append("text").attr("dx", 12).attr("dy", ".35em").text(function (d) {return d.nodeName});

force.on("tick", function () {
link.attr("x1", function (d) {
return d.source.x;
})
.attr("y1", function (d) {
return d.source.y;
})
.attr("x2", function (d) {
return d.target.x;
})
.attr("y2", function (d) {
return d.target.y;
});

node.attr("transform", function (d) {
return "translate(" + d.x + "," + d.y + ")";
});
});
} catch (ex) {
var msg = ex;
console.log(msg);
}
}

function processError(jqXHR, textStatus, errorThrown ){
var error = textStatus;
var i = 0;
}

function getQueryRequest(sessionId) {
var queryXml = [];
queryXml.push('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:quer="http://www.niku.com/xog/Query">');
queryXml.push('<soapenv:Header>');
queryXml.push('<quer:Auth>');
queryXml.push('<quer:SessionID>?</quer:SessionID>'.replace('?', sessionId));
queryXml.push('</quer:Auth>');
queryXml.push('</soapenv:Header>');
queryXml.push('<soapenv:Body>');
queryXml.push('<quer:Query>');
queryXml.push('<quer:Code>fon_project_dependencies</quer:Code>');
queryXml.push('</quer:Query>');
queryXml.push('<quer:Sort>');
queryXml.push('<quer:Column>');
queryXml.push('<quer:Name>inv_id</quer:Name>');
queryXml.push('</quer:Column>');
queryXml.push('</quer:Sort>');
queryXml.push('</soapenv:Body>');
queryXml.push('</soapenv:Envelope>');
return queryXml.join("");
}

function isEmpty(str) {
return (!str || 0 === str.length);
}

var currentSession = window.clarity.session.sessionId;
var host = window.location.host;
var ppmUrl = 'https://' + host + '/niku/xog';
var requestType = {};
jQuery(document).ready(processRequest());
</script>

3 Which leads to:

<img src="https://communities.ca.com/servlet/JiveServlet/previewBody/231180334-102-1-71408/Project%20Dependencies.PNG "/>