There have been a few previous discussions regarding a) the lack of customizing the General portlet on project dashboards (with name, id, description, etc), unable to add new fields and such, and b) creating HTML portlets that display project (or other) data. On a whim, I decided to take a crack at putting them together and creating a new, customizable, HTML portlet to replace the General system portlet. This is what I have so far, still some clean up to do but it is functional. I've made it look very much like a normal portlet (reusing stock stylesheet rules). There are a couple custom attributes that would need to be removed as well, but I wanted to get this out there to get feedback and what others think. I created a new account to access the xog queries, is there a way to restrict this account only to certain queries rather than all?
Query to return data:
SELECT @SELECT:DIM:USER_DEF:IMPLIED:ORH_GEN:odf_pk:odf_pk@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:description:description@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:schedule_finish:schedule_finish@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:schedule_start:schedule_start@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:name:name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:ppm_url:ppm_url@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:FULL_NAME:FULL_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:oh_rank:oh_rank@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:unique_code:unique_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:ORH_GEN:status_indicator:status_indicator@
FROM(
SELECT
odf_pk,
ppm_url,
description,
schedule_finish,
schedule_start,
name,
res.FULL_NAME,
oh_rank,
obj_request_category,
unique_code,
status_indicator,
CASE status_indicator
WHEN '1' THEN 'On Track'
WHEN '2' THEN 'Minor Variance'
WHEN '3' THEN 'Significant Variance'
ELSE 'Unknown'
END As status_indicator_text
FROM niku.ODF_PROJECT_V2 prj
LEFT OUTER JOIN niku.SRM_RESOURCES res ON res.USER_ID = prj.manager
WHERE prj.odf_pk=@WHERE:PARAM:USER_DEF:INTEGER:pInvestment@) data
WHERE @FILTER@
HTML for HTML portlet:
Edit xmlhttp.open to use the correct XOG URL
xmlhttp.setRequestHeader("SOAPAction to use the correct query URL
<html>
<head>
<title>General project data via SOAP</title>
<script type="text/javascript">
function getQueryVariable(variable) {
var query = window.location.href.substring(1);
var vars = query.split("&");
for (var i = 0; i < vars.length; i++) {
var pair = vars[i].split("=");
if (pair[0] == variable) {
return unescape(pair[1]);
}
}
alert('Query Variable ' + variable + ' not found');
}
function getDate(date) {
var date = new Date(date);
return ("0" + (date.getMonth() + 1).toString()).substr(-2) + "/" + ("0" + date.getDate().toString()).substr(-2) + "/" + (date.getFullYear().toString()).substr(2);
}
jQuery( document ).ready(function( $ ) {
var xmlhttp = new XMLHttpRequest();
xmlhttp.open('POST', 'http://xogurl/niku/xog', true);
xmlhttp.onreadystatechange = function () {
if (xmlhttp.readyState == 4) {
if (xmlhttp.status == 200) {
jQuery('#prj_desc').text(jQuery(xmlhttp.responseText).find('description').text());
jQuery('#prj_code').text(jQuery(xmlhttp.responseText).find('unique_code').text());
jQuery('#prj_pmgr').text(jQuery(xmlhttp.responseText).find('full_name').text());
jQuery('#col_rank').text('# '+jQuery(xmlhttp.responseText).find('oh_rank').text());
jQuery('#prj_date').text(getDate(jQuery(xmlhttp.responseText).find('schedule_start').text()) + ' - ' + getDate(jQuery(xmlhttp.responseText).find('schedule_finish').text()));
var dom_par = jQuery('#prj_data').parents('.ppm_portlet');
var sharepoint = jQuery(xmlhttp.responseText).find('ppm_url').text();
var txtTitle = jQuery(xmlhttp.responseText).find('name').text();
if (sharepoint) {
sharepoint = (window.location.href.indexOf("claritytest") > -1) ? 'http://svmswiftspdev01' + sharepoint: 'http://pmo.is.orhs.org' + sharepoint
txtTitle = '<a href="' + sharepoint + '" target="_blank">' + txtTitle + '</a>';
}
txtTitle += ' <span id="rank">(Rank # ' + jQuery(xmlhttp.responseText).find('oh_rank').text() + ')</span>';
dom_par.find('.ppm_portlet_title_bar h2').html(txtTitle);
switch(jQuery(xmlhttp.responseText).find('status_indicator').text()) {
case '1':
dom_par.find('.ppm_portlet_title_bar').addClass('status_gre');
dom_par.find('.ppm_portlet_header_actions').addClass('status_gre');
jQuery('#prj_icon').addClass('icon_gre');
break;
case '2':
dom_par.find('.ppm_portlet_title_bar').addClass('status_yel');
dom_par.find('.ppm_portlet_header_actions').addClass('status_yel');
jQuery('#prj_icon').addClass('icon_yel');
break;
case '3':
dom_par.find('.ppm_portlet_title_bar').addClass('status_red');
dom_par.find('.ppm_portlet_header_actions').addClass('status_red');
jQuery('#prj_icon').addClass('icon_red');
break;
default:
}
}
}
}
var prid = getQueryVariable("id");
var data = jQuery('#data').val().replace('<quer:param_pinvestment></quer:param_pinvestment>', '<quer:param_pinvestment>' + prid + '</quer:param_pinvestment>');
xmlhttp.setRequestHeader("SOAPAction", "http://www.niku.com/xog/Query/new_query");
xmlhttp.setRequestHeader("Content-Type", "text/xml");
xmlhttp.send(data);
});
</script>
<style>
body {background-color:lightgrey;}
h1 {color:blue;}
p {color:green;}
#input {
display: none;
}
#prj_name {
text-align: center;
font-weight: bold;
}
#rank {
font-style: italic;
font-size: 90%;
margin-left: 15px;
}
.status_red {
background-color: #ffcccc;
background: linear-gradient( #ffcccc, #e60000 );
background: -moz-linear-gradient(#ffcccc, #e60000);
background: -webkit-gradient(linear, 0% 0%, 0% 100%, from(#ffcccc), to(#e60000));
-pie-background: linear-gradient(#ffcccc, #e60000);
}
.status_gre {
background-color: #80ff80;
background: linear-gradient( #80ff80, #00cc00 );
background: -moz-linear-gradient(#80ff80, #00cc00);
background: -webkit-gradient(linear, 0% 0%, 0% 100%, from(#80ff80), to(#00cc00));
-pie-background: linear-gradient(#80ff80, #00cc00);
}
.status_yel {
background-color: #ffff80;
background: linear-gradient( #ffff80, #e6e600 );
background: -moz-linear-gradient(#ffff80, #e6e600);
background: -webkit-gradient(linear, 0% 0%, 0% 100%, from(#ffff80), to(#e6e600));
-pie-background: linear-gradient(#ffff80, #e6e600);
}
.icon_red img {
background: url("ui/uitk/images/odf.png") no-repeat -112px 0px; width: 16px; height: 16px;
}
.icon_gre img {
background: url("ui/uitk/images/odf.png") no-repeat -48px 0px; width: 16px; height: 16px;
}
.icon_yel img {
background: url("ui/uitk/images/odf.png") no-repeat -144px 0px; width: 16px; height: 16px;
}
</style>
</head>
<body>
<div id="input">
<textarea id="data" rows='10' cols='50'>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:quer="http://www.niku.com/xog/Query">
<soapenv:Header>
<quer:Auth>
<quer:Username>username</quer:Username>
<quer:Password>password</quer:Password>
</quer:Auth>
</soapenv:Header>
<soapenv:Body>
<quer:Query>
<quer:Filter>
<quer:param_pinvestment></quer:param_pinvestment>
</quer:Filter>
</quer:Query>
</soapenv:Body>
</soapenv:Envelope>
</textarea>
</div>
<table class="ppm_grid" id="prj_data" width="100%">
<thead>
<tr>
<th id="col_rank"></th>
<th class="colHeadSort">Project ID</th>
<th class="colHeadSort">Project Manager</th>
<th class="colHeadSort">Schedule Dates</th>
</tr>
</thead>
<tr>
<td id="prj_icon" align="center"><img src="ui/uitk/images/s.gif"></td>
<td id="prj_code">Loading...</td>
<td id="prj_pmgr">Loading...</td>
<td id="prj_date">Loading...</td>
</tr>
<tr>
<td id="prj_desc" colspan="4"></td>
</tr>
</table>
</body>
<html>