Timeline in CRM On Demand

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

timeline v2.0

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.

timeline table

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;">&#xf0e0;</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 != '&nbsp;') {
            context.fillText(actOwner, 50 + actBlockX + params['actNameSize'] + 2, actBlockY + (params['actNameSize'] + 2)*3);
      }

      if (actDescription != '&nbsp;') {
          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

Leave a Reply

Your email address will not be published. Required fields are marked *