This timeline shows activities that took place, grouped per month and indicating the type of activity with a color and a specific icon.
Disclaimer: This is not a finished report and should not be considered as such. This is just a proof of concept to show what the reporting engine of CRM On Demand is capable of.
HTML5 Canvas and Javascript are not a required skill for normal CRM On Demand report generation at all! But using these techniques enable a wide range of unusual data visualizations.
Here is all you need to make a timeline report in CRM On Demand. But before you try this out, you might want to learn more about how narrative views actually work by checking out this video recording
Result Example
Concepts
- Each box represents an activity on the timeline.
- Much of the power of the report is hidden in the calculated ‘month’ field formula where I compare every activities month with the previous one in order to determine whether or not I should add a light blue month indicator in the time line.
- Colors and icons match a type of activity and can be set in the narrative view prefix
- The icons rely on the ‘awesome font’ library and the library file should be uploaded into CRM On Demand instead of being referenced on the internet like in the example below or performance reasons
Criteria Fields
From the ‘Reporting’ subject areas, I used: ‘Activities‘
| Field Name | Calculated Field | Formula |
|---|---|---|
| Activity ID | Activity.”Activity ID” | |
| Start Time | “- Date Planned Start”.”Planned Start Time” | |
| Month | Yes | CASE WHEN CAST(REPLACE(“- Date Planned Start”.”Fiscal Month/Yr”, ‘ / ‘, ”) AS INTEGER) <> MSUM(CAST(REPLACE(“- Date Planned Start”.”Fiscal Month/Yr”, ‘ / ‘, ”) AS INTEGER),2) – CAST(REPLACE(“- Date Planned Start”.”Fiscal Month/Yr”, ‘ / ‘, ”) AS INTEGER) THEN CASE CAST(RIGHT(“- Date Planned Start”.”Fiscal Month/Yr”, 2) AS CHAR) WHEN ’01’ THEN ‘January’ WHEN ’02’ THEN ‘February’ WHEN ’03’ THEN ‘March’ WHEN ’04’ THEN ‘April’ WHEN ’05’ THEN ‘May’ WHEN ’06’ THEN ‘June’ WHEN ’07’ THEN ‘July’ WHEN ’08’ THEN ‘August’ WHEN ’09’ THEN ‘September’ WHEN ’10’ THEN ‘October’ WHEN ’11’ THEN ‘November’ WHEN ’12’ THEN ‘December’ ELSE ‘Unknown’ END||’ ‘||LEFT(“- Date Planned Start”.”Fiscal Month/Yr”, 4) ELSE CAST(null AS CHAR) END |
| Type Code | Activity.”Type Code” | |
| Date | “- Date Planned Start”.Date | |
| Description | Yes | Activity.Type||’: ‘||Activity.Description |
| Account Name | Account.”Account Name” | |
| Owner | Yes | “- Owned By User”.”First Name”||’ ‘||”- Owned By User”.”Last Name” |
Criteria Sorting
Sorting is very important in this report as it will trigger the appearance of the light blue month indicator on the timeline. This report is sorted:
- Start Time: Descending
Criteria Filters
- Type Code is not null
- Any filter to reduce the number of activities within a certain scope
- a set of accounts
- a certain timeframe
- group of salesreps
- types of activities
- …
Data Formatting
Format the date till the table view looks something like the example below.
Hide the table once the narrative view is ready.
Narrative View Definition
Prefix
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.5.0/css/font-awesome.min.css">
<span style="font-family:FontAwesome;visibility:hidden;"></span>
<canvas id="myCanvas" width="800" height="1"></canvas>
<script>
var canvas = document.getElementById('myCanvas');
var context = canvas.getContext('2d');
// get mouse position
function getMousePos(canvas, evt) {
var rect = canvas.getBoundingClientRect();
return {
x: evt.clientX - rect.left,
y: evt.clientY - rect.top
};
}
// text wrap
function wrapText(context, text, x, y, maxWidth, lineHeight) {
var words = text.split(' ');
var line = '';
for(var n = 0; n < words.length; n++) {
var testLine = line + words[n] + ' ';
var metrics = context.measureText(testLine);
var testWidth = metrics.width;
if (testWidth > maxWidth && n > 0) {
context.fillText(line, x, y);
line = words[n] + ' ';
y += lineHeight;
}
else {
line = testLine;
}
}
context.fillText(line, x, y);
}
var oscDomain = window.location.host;
oscDomain = oscDomain.replace('-bi.', '-crm.');
canvas.addEventListener('mousedown', function(evt) {
var mousePos = getMousePos(canvas, evt);
var message = 'Mouse position: ' + mousePos.x + ',' + mousePos.y;
// alert(message);
for(var n = 0; n < drilldowns.length; n++) {
if ((mousePos.x > drilldowns[n]['x'])
&& (mousePos.x < drilldowns[n]['x'] + params['actBlockWidth'])
&& (mousePos.y > drilldowns[n]['y'])
&& (mousePos.y < drilldowns[n]['y'] + params['actBlockHeight']) ) {
var drilldownID = drilldowns[n]['ID'];
drilldownID = drilldownID.replace('.00', '');
var URL = 'https://' + oscDomain + '/sales/faces/CrmFusionHome?cardToOpen=ZMM_ACTIVITIES_CRM_CARD&tabToOpen=ZMM_ACTIVITIES_ACTIVITIES_CRM&TF_ActivityId=' + drilldownID;
// alert(URL);
window.open(URL, '_OSCActivityDetails');
}
}
}, false);
var drilldowns = [];
var params = [];
var activities = [];
params['maxWidth'] = canvas.width;
params['middleWidth'] = Math.round(canvas.width / 2);
params['font'] = 'Calibri';
params['spacerHor'] = 10;
params['lineWidth'] = 10;
params['lineColor'] = 'lightblue';
params['lineContrastColor'] = 'lightblue';
params['lineFontColor'] = 'white';
params['monthBlockLineWidth'] = 1;
params['monthBlockHeight'] = 24;
params['monthBlockWidth'] = 150;
params['monthBlockX'] = params['middleWidth'] - Math.round(params['monthBlockWidth']/2);
params['monthNameSize'] = 12;
params['monthActCounter'] = 0;
params['pointerSpacer'] = 60;
params['pointerRadius'] = 15;
params['pointerLineWidth'] = 1;
params['activityBgColor'] = 'lightblue';
params['activityContrastColor'] = 'blue';
params['actLeft'] = -1; // draw activity on the left of the timeline or not, 1=left, 2=right
params['actBlockLineWidth'] = 1;
params['actBlockHeight'] = params['pointerSpacer']-10;
params['actBlockWidth'] = 350;
params['actCount'] = 0;
params['actNameSize'] = 10;
params['actRounding'] = 15;
var actTypes = {
"DEFAULT" : {
"icon" : "\uf128",
"iconWidth" : 24,
"bgColor" : "gray",
"contrastColor" : "black",
"fontColor" : "black"
},
"EMAIL" : {
"icon" : "\uf0e0",
"iconWidth" : 24,
"bgColor" : "pink",
"contrastColor" : "black",
"fontColor" : "black"
},
"EVENT" : {
"icon" : "\uf000",
"iconWidth" : 24,
"bgColor" : "#FFD7D7",
"contrastColor" : "black",
"fontColor" : "black"
},
"CALL" : {
"icon" : "\uf095",
"iconWidth" : 24,
"bgColor" : "#FFDAB8",
"contrastColor" : "black",
"fontColor" : "black"
},
"INTERNAL" : {
"icon" : "\uf0c0",
"iconWidth" : 24,
"bgColor" : "#CCC0DA",
"contrastColor" : "black",
"fontColor" : "black"
},
"MEETING" : {
"icon" : "\uf007",
"iconWidth" : 24,
"bgColor" : "#ADE1F6",
"contrastColor" : "black",
"fontColor" : "black"
},
"DEMO" : {
"icon" : "\uf108",
"iconWidth" : 24,
"bgColor" : "#C8EBCF",
"contrastColor" : "black",
"fontColor" : "black"
}
};
//=========================================================================================
var prevMonth = 'xXxXx';
var noMonths = 0;
var noActivities = 0;
// left = -1 as we need to substract on the X axis from the middle line when drawing
// right = +1 as we need to add on the X axis from the middle line when drawing
var orientation = 2;
function processActivity(i) {
// count months and activities
noActivities++;
if (prevMonth != activities[i][1]) {
prevMonth = activities[i][1];
noMonths++;
if (orientation == 2) {
activities[i][7] = canvas.height + params['pointerSpacer']/2 + 50;
canvas.height += params['pointerSpacer'] + 50;
} else if (orientation == 1) {
activities[i][7] = canvas.height + params['pointerSpacer']/2 + 50 + Math.round(params['actBlockHeight']*5/3);
canvas.height += params['pointerSpacer'] + 50 + Math.round(params['actBlockHeight']*5/3);
} else {
activities[i][7] = canvas.height + params['pointerSpacer']/2 + 50 + Math.round(params['actBlockHeight']*4/3);
canvas.height += params['pointerSpacer'] + 50 + Math.round(params['actBlockHeight']*4/3);
}
orientation = -1;
} else {
activities[i][1] = '';
activities[i][7] = canvas.height + params['pointerSpacer']/2;
canvas.height += params['pointerSpacer'];
}
activities[i][8] = orientation;
if (orientation == -1) {
orientation = 1;
} else {
orientation = -1;
}
}
//=========================================================================================
function drawTimeline() {
// clear canvas to erase loading message
context.clearRect(0,0,canvas.width,canvas.height);
// extend canvas a little more
canvas.height += params['pointerSpacer'];
// draw central line
context.beginPath();
context.lineWidth = params['lineWidth'];
context.moveTo(params['middleWidth'], 0);
context.lineTo(params['middleWidth'], canvas.height);
context.strokeStyle = params['lineColor'];
context.stroke();
for (var i=0; i< activities.length; i++) {
drawActivity(i, activities[i][0], activities[i][2], activities[i][3], activities[i][4], activities[i][5], activities[i][6], activities[i][1]);
}
}
//=========================================================================================
function drawActivity(i, actID, actType, actDate, actDescription, actCustomer, actOwner, monthName) {
// get activity type details
var activity = [];
if (actTypes[actType] == undefined) {
activity = actTypes['DEFAULT'];
} else {
activity = actTypes[actType];
}
context.lineWidth = 1;
// pointer circle
context.beginPath();
context.arc(params['middleWidth'], activities[i][7], params['pointerRadius'], 0, 2 * Math.PI, false);
context.fillStyle = activity['bgColor'];
context.fill();
context.strokeStyle = activity['fontColor'];
context.stroke();
// pointer icon
context.font = '15px FontAwesome';
context.textAlign = 'center';
context.textBaseline = 'middle';
context.fillStyle = activity['fontColor'];
context.fillText(activity['icon'], params['middleWidth'], activities[i][7]);
context.font = '18px ' + params['font'];
// draw pointer arrow
x0 = params['middleWidth'] + Math.round(params['pointerRadius'])*activities[i][8];
y0 = activities[i][7];
x1 = x0;
y1 = y0;
x2 = x1 + params['spacerHor'] * activities[i][8];
y2 = y1 + params['spacerHor'];
x3 = x1 + params['spacerHor'] * activities[i][8];
y3 = y1 - params['spacerHor'];
// draw month block if month available
if (monthName != '') {
// draw month block
context.beginPath();
context.rect(params['middleWidth'] - Math.round(params['monthBlockWidth']/2),activities[i][7] - Math.round(params['monthBlockHeight']/2) - params['actBlockHeight'], params['monthBlockWidth'], params['monthBlockHeight']);
context.fillStyle = params['lineColor'];
context.fill();
// write month name
context.font = params['monthNameSize'] + 'pt ' + params['font'];
context.textAlign = 'center';
context.textBaseline = 'middle';
context.fillStyle = params['lineFontColor'];
context.fillText(monthName, params['middleWidth'],activities[i][7] - params['actBlockHeight']);
}
// draw activity block
var borderRound = 9;
context.beginPath();
context.moveTo(x1,y1);
context.lineTo(x3,y3);
context.lineTo(x3, y3 - Math.round(params['actBlockHeight']/3) + borderRound);
if (activities[i][8] == -1) {
context.arc(x3 - borderRound, y3 - Math.round(params['actBlockHeight']/3) + borderRound, borderRound, 0, Math.PI*3/2, true);
} else {
context.arc(x3 + borderRound, y3 - Math.round(params['actBlockHeight']/3) + borderRound, borderRound, Math.PI, Math.PI*3/2, false);
}
context.lineTo(x3 + params['actBlockWidth'] * activities[i][8], y3 - Math.round(params['actBlockHeight']/3));
context.lineTo(x3 + params['actBlockWidth'] * activities[i][8], y3 + Math.round(params['actBlockHeight']*5/3) - borderRound);
if (activities[i][8] == -1) {
context.arc(x3 + params['actBlockWidth'] * activities[i][8] + borderRound, y3 + Math.round(params['actBlockHeight']*5/3) - borderRound, borderRound, Math.PI, Math.PI/2, true);
} else {
context.arc(x3 + params['actBlockWidth'] * activities[i][8] - borderRound, y3 + Math.round(params['actBlockHeight']*5/3) - borderRound, borderRound, 0, Math.PI/2, false);
}
context.lineTo(x3, y3 + Math.round(params['actBlockHeight']*5/3));
context.lineTo(x2,y2);
context.closePath();
context.strokeStyle = activity['fontColor'];;
context.stroke();
context.fillStyle = activity['bgColor'];
context.fill();
// neutralize left/right for drilldown and text
if (activities[i][8] == 1) {
activities[i][8] = 0;
}
var actBlockX = x3 + params['actBlockWidth'] * activities[i][8];
var actBlockY = y3 - Math.round(params['actBlockHeight']/3);
// add drilldown to activity block, not for arrow
var nextDrillID = drilldowns.length;
drilldowns[nextDrillID] = [];
drilldowns[nextDrillID]['ID'] = actID;
drilldowns[nextDrillID]['x'] = actBlockX;
drilldowns[nextDrillID]['y'] = actBlockY;
// write activity details
context.textAlign = 'left';
context.textBaseline = 'top';
context.font = params['actNameSize'] + 'pt ' + params['font'];
context.fillStyle = activity['contrastColor'];
context.fillText('Date', actBlockX + params['actNameSize'] + 2, actBlockY + (params['actNameSize'] + 2)*1);
context.fillStyle = activity['fontColor'];
context.fillText(actDate, 50 + actBlockX + params['actNameSize'] + 2, actBlockY + (params['actNameSize'] + 2)*1);
context.fillStyle = activity['contrastColor'];
context.fillText('Account', actBlockX + params['actNameSize'] + 2, actBlockY + (params['actNameSize'] + 2)*2);
context.fillStyle = activity['fontColor'];
context.fillText(actCustomer, 50 + actBlockX + params['actNameSize'] + 2, actBlockY + (params['actNameSize'] + 2)*2);
context.fillStyle = activity['contrastColor'];
context.fillText('Salesrep', actBlockX + params['actNameSize'] + 2, actBlockY + (params['actNameSize'] + 2)*3);
context.fillStyle = activity['fontColor'];
if (actOwner != ' ') {
context.fillText(actOwner, 50 + actBlockX + params['actNameSize'] + 2, actBlockY + (params['actNameSize'] + 2)*3);
}
if (actDescription != ' ') {
context.fillStyle = activity['fontColor'];
wrapText(context, actDescription, actBlockX + params['actNameSize'] + 2, actBlockY + (params['actNameSize'] + 2)*5);
}
}
Narrative
var i = activities.length; activities[i] = []; activities[i][0] = '@1'; // activity ID activities[i][1] = '@3'; // month name activities[i][2] = '@4'; // activity tyoe activities[i][3] = '@5'; // activity date activities[i][4] = '@6'; // activity description activities[i][5] = '@7'; // activity customer activities[i][6] = '@8'; // activity owner processActivity(i);
Row separator: empty
Postfix
// buy time for Awesome Font to load
context.fillText('... loading timeline ...', params['middleWidth'], 50);
setTimeout(drawTimeline,1000);
</script>
Rows to Display
As many as you think is usefull. The length of the report will adapt to whatever value use here. A reasonable value to start with is 50
